Full Version: Combobox selection based on current customer
UtterAccess Forums > Microsoft® Access > Access Forms
I want to populate the Pet ID, (which brings up the pets name) in a combo box in a sub sub form, with only pets owned by the customer who is the focus of the main form. (See the bottom of the screen to see the structure of the tables.)

The combobox for Pet ID is in a sub form of a sub form of the main form Trips.

The main form is called Trips and has the foreign key CutomerID.

The master/child fields linking the forms are TripID- Visit ID : VisitID-PetsAtVisitID

The pet table (from which I am trying to populate the combo box) in the sub sub form has the foreign key CustomerID but from the main form to the sub sub form I have different foreign keys and doing the linking.

1.I am not sure if I should be using a query or an expression.
2. I am assuming I should be putting an expression in the properties of the combobox of the form “PetsAtVisit”.
2a. If I am to use an expression in the properties of the combobox, under what property heading should I put my expression i.e. “Row Source” etc.

----- Customers ------
| .......................... |
| .........................Trips -Main Form From (Based on table Trips)
| ........................... |
|..........................Visits - SubForm (Based on table Visits)
|............................ |
Pets ........................PetsAtVisit - Sub Sub Form (Based on table PetsAtVisits)

Some of my customers will show animals so not all the pets will be there all the time.

Also as an asside some(or probably all) will die. So I have added a check box "Pets Decieced" But I will have to work that one out.

Edited by: TravelingHT on Sun Aug 31 12:13:07 EDT 2008.
First, you want to base the pet combo box in the PetsAtVisit form on a query. The SQL text of the query will be the row source of the combo box. To show only those pets that are alive you want to include a WHERE clause in your query that looks for those pets that don't have the box checked. You will also want to limit the query results to only the customer listed on the main form.
The query would look something like this:
SELECT tblPets.pkPetID, tblPets.fkCustID, tblPets.txtPetName, tblPets.LogDeceased
FROM tblPets
WHERE (((tblPets.fkCustID)=[forms]![frmCustomers]![pkCustID]) AND ((tblPets.LogDeceased)=False));
To get this to work properly, you will need to requery the combo box when the current record changes in the main form (i.e. a different customer). To do this, you will need the following code in the on current event of your main form
The cboPets is the name of the combo box of the pet names on the PetsAtVisit form.
Thanks I tried this and got nowhere.
I have the query that will bring up the name of all the pets, the requery is in place but I can not see if it is affecting because currently all pets are being brought up.
All I need is to get the criteria of the CustomerID colum to say "= the same as CustomerID value in form frmlTrip (In code of course)
Why can I not have a query on the tblPet to bring up the CustomerID(All customers Must have pets!)and use the results of that query wich will have only the customers pets to populate the list in the subsubquery?
So one query based on tblCustomer and tblPet with the CustomerID in the query from tblPet and then use the result of that query to populate the choices in the combobox list.
How do I reference the results of a query performed for a different control, build a query on a query?
I hope sherlock homes is about somewhere.
You'll have to modify the WHERE clause of the query I gave you to incorporate your main customer form name
SELECT tblPets.pkPetID, tblPets.fkCustID, tblPets.txtPetName, tblPets.LogDeceased
FROM tblPets
WHERE (((tblPets.fkCustID)=[forms]![frmCustomers]![pkCustID]) AND ((tblPets.LogDeceased)=False));
Oused frmCustomers, you'll have to substitute your form name.
If that does not work, can you zip and post your database (with any sensative data removed) and we can take a look at it?
I've attached an example DB that I put together that illustrates the query and the code that I mentioned in my earlier response. The combo box is in the last subform on the form named frmCustomers.
Thanks for the help. I will look at the example you have sent me.
It took some time to do this becuase I have a dedicated offline computer, that is where the database is so I had to create a copy on this computer. I know what I know I dont know about computer security.
There is only 1 query and only one bit of code in the frmTrips.
I have not yet looked at your example. I will enjoy that one. I have been pulling my hair out for over 3 weeks on this one.
I have attached the database file to this post. If I can work out how you do that.
For entering new records, you need to requery the pet combo box in the after update of the customer combo box. I think you ran into problems because of some spelling issues, but other than that you were on the right track.
Ok I got the database, you are very kind for making this.
did get some propblems and I went back and forth through the frmTrips:
Enter paramater Value
I am getting the same thing with mine. Is there something about referencing a primary key in a query?
I am going to spend some time looking over the sturcture of the code you have written so I can learn.
Thanks again for all your help.
Yours truly,
I know the from frmlTrips looks like a spelling mistake but it is not. It is that way and I have just worked arround it, scared to change it for not catching all its populations through the database.
I am still none the wizer though. I still can not get the requery to do anthing and I still get the message asking me to fill in the missing variable.
I think what I should do is create a non visable labled and write code to make it = the result of the combobox choice.
I can then use that as that value in the second query.
This idea acutally came to me from someone in the microsoft list serve.
I know now that I should not post the same prob in two places but I did not then, (when I started this post) and will not do so in the future.
I have 17 replies and the last one asked this question, maybe you can answer it>
Do you in fact have a CONTROL - not a field, but a combo box or textbox - on
frmlTrips named "CustomerID"?
If not, you'll get this error. It's looking for a control which does not
If CustomerID is just a fieldname in the form's recordsource, it's not
available for this query. What you can do is create a textbox on the form
(named CustomerID or, better, txtCustomerID), bound to the field, and use that
control's name in the query criteria. Set the textbox's Visible property to No
since you probably want to conceal the (autonumber??) ID from the user.
So now I am going to watch StarWars and pretend I lived long, long ago, in a univers far, far away.
Thanks again for all the help.
Traveling HT
You will get the prompt for the parameter if you open the frmTrips form by itself (in the database I created). frmTrips is used as a subform in the frmCustomers. If you open the frmCustomers, the customer ID is present for each record and the frmTrips subform can reference it. So the behavior is expected. In other words, don't use the subform as a form.

In your database that I reposted, the spelling issue was with the customer combo box on the main form. You had given the control a name of custmerID (less the "o"). In the query for the pet combo box in the subform you have to reference the control as custmerID. The underlying field in the pets table is still called customerID so that does not change. The query is as follows:

SELECT tblPets.PetslID, tblPets.PetlName, tblPets.CustomerID
FROM tblPets
WHERE logDeceased=FALSE AND customerID=forms!frmTrips!custmerID;

Oalso added the logical field (yes/no datatype) logDeceased to your pets table and used that in the query as well to filter out any pets that have passed away. If you open up your form frmTrips, the pets combo box in the frmPetsAtVisit subform should populate correctly with the pets for the customer listed on the main form.
Edited by: jzwp11 on Wed Sep 3 8:15:00 EDT 2008.
Becasue I basicall transcribed the table from one computer to antoher. I do not have the spelling problem on my original database.
I am going to work on it in on this computer and see what happens correcting the spelling.
I am then going to put in the code you have compiled and work form there I will be back.
Despite no changes on my part, I am starting to get the error message:
LinkMasterFieldPropertieSetting has produced this error "Invalid Outside Procedure"
The hair is thining.
Thanks again.
Travling HT
The error you are getting sounds like one of the fields linking a subform to a main form might be misspelled. When do you get the error?
OK so cues are helpfull.
o I messed up and wrote the code you have, but I put in the Row Source Type : Field List
Guess what this brought up, it brougt up the field lists even thougth some of them are set to 0 so THE CODE WORKS it is referencing the control but the control is bringing back nothing!!! or I may be worng.
I am going to change the customer ID control to bring back only the customer ID not complex stuff that brings back the customer name, or maby do what I said earlier and put the result of the combobox in a hiden txt box and reference that.
Will keep you informed.
Traveling HT
How can I SEE what value is being retruned by:
WHERE customerID=forms!frmTrips!customerID; ?
Create a simple query like this
SELECT tblCustomers.CustomerID
FROM tblCustomers
WHERE customerID=forms!frmTrips!customerID;
Open the form in question (and leave it open), run the query. It should return the customerID of the record currently visible on the form. Then go to another record on the form (with a different customerID) & rerun the query. You should get a different value for the customerID.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.