Problem with Set Value in Subform - The object doesn't contain the Automation...

[Angela_Marie]

I am attempting to create a macro in a subform that will automatically set
the value for a field and I keep getting the error message "The object
doesn't contain the automation object 'Patient Insurance Table'". I have read
several postings on this subject but I still cannot figure out what I am
doing incorrectly. Here are the details:
I have a tabbed form with several subforms. One of the subforms is based on a
Query that includes the tables "Insurance Table" and "Patient Insurance
Table". The Patient Insurance Table has a Primary key named "Patient ID#"
and a foreign key named "Insurance#". I want each insurance for a particular
Patient ID# to be numbered sequentially for that patient only. So, the first
insurance added for a patient should be numbered 1 and the next to be
numbered 2 and so on... The reason that the Insurance Table is included in
the subform query is because I have information from that table populating
the form when a particular insurance is selected.

The macro is written as follows:
SetValue
Item: [PATIENT INSURANCE TABLE]![Insurance#]
Expression: Nz(DMax("[Insurance#]","Patient Insurance Table","[Patient
Insurance Table].[Patient ID#]=" & [Patient Insurance Table].[Patient ID#]),0)
+1
Event: BeforeUpdate

I look forward to being embarrassed by how easy this is to fix!
Thank you!

 

[Steve Schapel]

Angela Marie,

This doesn't quite hold together. You are trying to assign a value to a
field which is the foreign key field, but kind of using it as a type of
serialising number. So that means the primary key in the Insurance
table is being used to serial number the Patient Insurance records. Is
that right?

By the way, it is not a good idea to use a # as part of the name of a field.

And in direct answer to your question (though I think there is a design
problem to be resolved first), the arguments of the SetValue macro
action should be like this...
Item: [Insurance#]
Expression: Nz(DMax("[Insurance#]","Patient Insurance Table","[Patient
ID#]=" & [Patient ID#]),0)+1