Query doesn't work as control source for field on my Access form.
Posted on 2007-08-11
I have a form/subform setup. The main form, fmContacts, has a combobox (ComboPatientLookup) which looks up a patient name in tblPatients and stores the PatientNo value. The subform has tblPatients. Which contains the name/address/etc. information for the selected patient.
I want to use the value from the combo box to populate the PatientNo field in tblContacts to identify the patient for that visit. This PatientNo field is on the main form.
Using the query wizard I built qryPatientLookup. I set the criteria for the query to show the value in the combo box. This works, I get the right value when I run the query. PatientNo control source is set to use the query. However, the field on the form doesn't load this value, even after I refresh the data. The field just shows "#Name?" A text box on the form for testing purposes shows the correct stored numeric value from the combo box, controlsource (for textbox) =[comboPatientLookUp]. When I set the PatientNo field's control source to the textbox value the PatientNo field mirrors the number in the textbox. However when I set the control source for PatientNo to the qryPatientLookUp, it doesn't work.
The control source in the field's property box shows: [qryPatientLookUp]![PatientNo].
In the query, qryPatientLookup, the criteria shows [Forms]![fmContacts]![comboPatientLookUp]. All of these values were selected using the Build... tool.
PatientID -------------->> PatientID
How do I get things to work in the desired way?