Update subform field based on primary form field

[sgagne]

I have a form (frmAddAccount) with a subform (frmAccountSub). In frmAddAccount, I have a field PatientID. When I type in this ID, I would like the subform field PrimaryInsurance to populate with information from the a field in another table - tblPatient. I realize that I could use a query for the report, but I need this information in the tblAccounts to stay the same even if it is changed in tblPatient.

I have been able to do this in the subform for other fields but I can't get it to work pulling from a main form.

Thanks in advance!

 

[vbaInet]

How about using a DLookup() function?

http://access.mvps.org/access/general/gen0018.htmhttp://www.techonthenet.com/access/functions/domain/dlookup.php
http://access.mvps.org/access/general/gen0018.htm

 

[sgagne]

Okay, so I know what to put in the "field" and "domain" but what would I use for criteria?
I want to populate PrimaryInsurance in tblAccount from the same field in tblPatientDatabase. I would guess it is patientID, but I can't seem to get the syntax right.
=DLookup ("[PrimaryInsurance]" , "tblPatientDatabase" , "CRITERIA")

Also, where would you recommend I put this? I was trying it in On Got Focus of the Primary Insurance field.
Thanks!

 

[vbaInet]

Alright, why not show us some of your trials?
It goes in the Control Source of your textbox.

 

[sgagne]

=DLookup ("[PrimaryInsurance]" , "tblPatientDatabase" , "PatientID = PatientID")
returns nothing


=DLookup ("[PrimaryInsurance]" , "tblPatientDatabase" , "tblAccount.PatientID = tblPatientDatabase.PatientID")
returns #Name?

=DLookup ("[PrimaryInsurance]" , "tblPatientDatabase" , "PrimaryInsurance = PrimaryInsurancy")
returns #Name?
I just don't know what I need to use for criteria... I think that is where the error is anyway

 

[sgagne]

If I use the build wizard, it comes out like this:
=DLookUp([tblAccount]![PrimaryInsurance],[tblPatientDatabase]![PrimaryInsurance],[tblAccount]![PrimaryInsurance]=[tblPatientDatabase]![PrimaryInsurance])
Again, I don't know what I need for criteria - still shows as #Name?

 

[vbaInet]

If PatientID exists on the form and PatientID is also a field in your tblPatientDatabase table and they have the same data types, then here:

=DLookup ("[PrimaryInsurance]" , "tblPatientDatabase" , "PatientID = " & PatientID)

where the one in red is the PatientID in tblPatientDatabase and the one in blue is coming from the form's Record Source. The above will work if PatientID is a Number data type (as explained in one of the links I gave you earlier).

 

[sgagne]

Patient ID is alphanumeric. That may be why it doesn't work. Will try with single quotes.

 

[sgagne]

Still no luck. Giving up for now. Thanks anyway!

 

[vbaInet]

"Still no luck"? Why don't you just show us what you wrote.