[Last Call] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2007-08-11
Medium Priority
Last Modified: 2013-11-05
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?
Question by:rreiss60
  • 3
  • 3
LVL 75
ID: 19676274
" 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.


Author Comment

ID: 19676318
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?
LVL 77

Expert Comment

ID: 19676360
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.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 75
ID: 19676373
"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.



Author Comment

ID: 19676378
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?
LVL 75

Accepted Solution

DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 600 total points
ID: 19676389
If tblContacts is the record source for your form, then set the Control Source of the combo box to PatientNo


Author Comment

ID: 19676435
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.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question