Query doesn't work as control source for field on my Access form.

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
PatientName                       Date
DateofBirth                        Provider

How do I get things to work in the desired way?
Who is Participating?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
If tblContacts is the record source for your form, then set the Control Source of the combo box to PatientNo

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
" PatientNo control source is set to use the query."

You cannot set a Control Source = to a query, only to a field in the Record Source or an expression you create.

rreiss60Author Commented:
The query was selected in the Expression builder. I clicked on the "..." next to the control source. The query is available for selection among the other options.  Isn't this creating an expression as you mention?
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

The expression builder doesn't care what you are going to do with the expression.
It will let you build anything whether it is valid or not.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"It will let you build anything whether it is valid or not."

M$ s/b smarter than that, right Pete.

rreiss60 ... when you think about it, a query would 'typically' return more than one row/value/field ... so, the Control Source would get 'confused' - just like M$.

So .... you have to decide on one or the other ... that I mentioned above.  A third one is the control itself can be populated via vba code too.


rreiss60Author Commented:
O.K.  Then back to original intent of my question.  I want to select a value from the comb box.  The first (hidden) column is bound to the field PatientNo from tblPatients.  I want to populate the PatientNo field of tblContacts with this value when a patient is selected from the combobox.  What is the correct way to do this?
rreiss60Author Commented:
Yes, that works, but with the new issue that the PatientNo will change even if the record has been previously populated.  Based on reading in a related thread, I need to disable the combo box if the patient number field is already populated.  I think I will put  a button on the form to allow selections from the combo box only when the button sets combobox.enabled =true or .visible=true which I would want only when entering a new contact.  Or, only if the ContactNo field is null, or something like that.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.