Error when trying to update a field in another form

Topic

New Reply

Hi,

I’m trying to update a field on one form to a field on another form, but am receiving a VB error. I’m using the following code in the ‘After_Update’ section of the field:

Private Sub DeptAIP_AfterUpdate()
Forms![ProgramMovements].LatestAIPDate = Me.DeptAIP
End Sub

I have the same code in a different sub-form which works. This sub-form is part of the ‘ProgramMovements’ form, so I’m not sure if that’s why that code works, but this code in a separate form doesn’t work.

Basically, when I enter data in to the ‘DeptAIP’ field and then click out of the field, I get a VB error:
“Microsoft Office Access can’t find the form ‘ProgramMovements’……”

Any help would be appreciated.

Cheers,
Jason

Reply | Quote

Replies

In order to update a field on a form, that form must be open. The error message suggests it is not. If the subform is on that form, then the code would work, because the form has to be open in order for the subform to be open. If that is not the case, let us know and we’ll try to help resolve the issue.

Reply | Quote

In order to update a field on a form, that form must be open. The error message suggests it is not. If the subform is on that form, then the code would work, because the form has to be open in order for the subform to be open. If that is not the case, let us know and we’ll try to help resolve the issue.

Hi Wendel,

Yes, that is the case – the subform is on that form, which is why that code works. Is there another way around this, or should I just update the underlying field in the table itself?

Cheers,
Jason

Reply | Quote

Can you tell us exactly where the controls DeptAIP and LatestAIPDate are, i.e. on which form/subform?

Reply | Quote

Can you tell us exactly where the controls DeptAIP and LatestAIPDate are, i.e. on which form/subform?

Hi Hans,

DeptAIP is in form “BidDataEnter” and LatestAIPDate is in form “ProgramMovements”

Cheers,
Jason

Reply | Quote

And what is the relationship of BidDataCenter and ProgramMovements? Is BidDataCenter a subform of ProgramMovements or the other way round or neither?

Reply | Quote

And what is the relationship of BidDataCenter and ProgramMovements? Is BidDataCenter a subform of ProgramMovements or the other way round or neither?

There is no relationship between BidDataEnter and ProgramMovements.

Reply | Quote

You have made mention of subforms earlier in this thread.

When you want to refer to a control on a subform, you need to use a different syntax than if you refer to a control on a form that is not a subform.
The Forms collection includes all open forms, but not open subforms.

So is the control you want to update on a form or a subform?

Reply | Quote

You have made mention of subforms earlier in this thread.

When you want to refer to a control on a subform, you need to use a different syntax than if you refer to a control on a form that is not a subform.
The Forms collection includes all open forms, but not open subforms.

So is the control you want to update on a form or a subform?

Hi John,

The control I want to update is on a form – the field is called ‘LatestAIPDate’ and it is on the ‘ProgramMovements’ form.

I mentioned a sub-form earlier because the example code I used works in a sub-form that is on the ‘ProgramMovements’ form. Wendell mentioned that the same code doesn’t work on another form because the ‘ProgramMovements’ form isn’t open when I try to run the code.

Cheers,
Jason

Reply | Quote

So you are trying to update a control on a form that is not open? You cannot do that.
If the form is not open, just update the value in the underlying field.

Reply | Quote

So you are trying to update a control on a form that is not open? You cannot do that.
If the form is not open, just update the value in the underlying field.

Thanks John, I think I asked that question myself in my first reply. Are you able to help with code that would let me update the field in the underlying table, as I’m not sure how to do this. The table name is tblBill and the field is AIP_LatestAIPDate

Cheers,
Jason

Reply | Quote

You did indeed ask that question at the start. But if the form had been open, that would have been easier.

You need to be able to identify which record you want the field updated for.
So is there something on the currently open form that identifies which record? Or is there some other way ?

Usually you would need to specify a value for the key field in the table you want updated. What is the Key? What type of data is it?
And what type of data is AIP_LatestAIPDate – presumably a Date field.

Reply | Quote

You did indeed ask that question at the start. But if the form had been open, that would have been easier.

You need to be able to identify which record you want the field updated for.
So is there something on the currently open form that identifies which record? Or is there some other way ?

Usually you would need to specify a value for the key field in the table you want updated. What is the Key? What type of data is it?
And what type of data is AIP_LatestAIPDate – presumably a Date field.

The ‘BillBidInceptionNo’ identifies the record. This is an AutoNumber.

AIP_LatestAIPDate is a Date/Time field.

Cheers,
Jason

Reply | Quote

The easiest option is probably to create an Update Query, that uses the BillBidInceptionNo from the open form as a parameter.

Do you know how to create update queries?

The After Update event would then just run the query.

Docmd.OpenQuery “qryYourQuery”

Reply | Quote

The easiest option is probably to create an Update Query, that uses the BillBidInceptionNo from the open form as a parameter.

Do you know how to create update queries?

The After Update event would then just run the query.

Docmd.OpenQuery “qryYourQuery”

Hi John,

I’ve worked out how to create a new query and then change it to an ‘update query’, but not sure how to modify this query so that it accepts data from the field on the form.

Reply | Quote

Hi John,

I’ve worked out how to create a new query and then change it to an ‘update query’, but not sure how to modify this query so that it accepts data from the field on the form.

In the query grid when you look at an update query in design view there is a row labelled “Update To” and you select the field you want to update in the top row and then specify the value you want that field updated to as Forms!MyFormName!MyFieldName – you will have to substitute your appropriate names. In your case I think it would be Forms!ProgramMovements!AIP_LatestAIPDate – however your field name be something slightly different, so check that.

Reply | Quote

In addition to saying what you want to update to, you need to specify which record you want updated. You do this by specifying a value on the Criteria line, for the field BillBidInceptionNo.
It is not clear to me where this value is coming from, but if it is also available on the open form, you can reference a control on that form here as you do for theUpdateTo value.
I think the easiest way to build a reference to a form control is to have the form open at the time. In the query click in the place where you want to put the expression, then right click and choose Build. this oepn the Expression Builder.
Double click the + for Forms, then Open Forms, the click the relevant form. A list of all controls is displayed. Double click the right one, and you will see the expression displayed at the top of the expression builder. OK it.

Reply | Quote

In the query grid when you look at an update query in design view there is a row labelled “Update To” and you select the field you want to update in the top row and then specify the value you want that field updated to as Forms!MyFormName!MyFieldName – you will have to substitute your appropriate names. In your case I think it would be Forms!ProgramMovements!AIP_LatestAIPDate – however your field name be something slightly different, so check that.

Hi guys,

Thanks for all your help so far, I just want to make sure I have this right because I think I might have confused everyone with field names.

What I’m trying to achieve is that when a user enters data into the ‘DeptAIP’ field on the ‘BidDataEnter’ form, I want the ‘LatestAIPDate’ field on the ‘ProgramMovements’ form to be updated. The respective fields and tables that these form fields refer to are ‘AIPProposed_DeptsProposedDate’ in the ‘tblBill’ table and ‘AIP_LatestAIPDate’ in the ‘tblBill’ table – so in this respect, I want it so that when the ‘AIPProposed_DeptsProposedDate’ field is updated in the ‘tblBill’ table, for the ‘AIP_LatestAIPDate’ field in the same table to be updated.

So I’ve created an UpdateQuery (called ‘UpdateLatestAIP’), put ‘AIP_LatestAIPDate’ in the top row (ie, the field in the table I want to update), and put ‘Forms!BidDataEnter!DeptAIP’ in the Update To part (ie, the value in this field should be copied into ‘AIP_LatestAIPDate’). I’ve then gone into the ‘DeptAIP’ field on the ‘BidDataEnter’ form, clicked in the ‘AfterUpdate’ section and put in this code:

Private Sub DeptAIP_AfterUpdate()
DoCmd.OpenQuery “UpdateLatestAIP”, , acReadOnly
End Sub

This doesn’t seem to be working however as it says it’s going to update 4 rows and then when I close the form I get a conflict message.

Once again, sorry for not explaining myself properly!

Reply | Quote

This doesn’t seem to be working however as it says it’s going to update 4 rows and then when I close the form I get a conflict message.

So what is the remaining problem? The conflict message? What does this actually say?
Presumably you don’t want to update 4 rows? Why are there 4 rows? Did you follow my instructions in a previous post about specifying which record you want updated?

I think you may need to post a sample db. Here are instructions for doing that.

[*]Make a copy of the database (in Access 2000 or 2002-2003 format) and work with that.
[*]Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
[*]In the remaining table(s), remove most records – leave only the minimum number necessary to demonstrate the problem.
[*]Remove or modify data of a confidential nature.
[*]Perform a compact and repair (Tools/Database Utilities).
[*]Make a zip file containing the database.
[*]If you have difficulties getting the zip file reasonably small, save the database in Access 97 format and then zip it.
[*]Attach the zip file to a reply.

Reply | Quote