Simple query with criteria dependent on current form value

[TravelingHT]

Here is where I currently am

The record source for the combo box Pets in the Form frmPetsAtVisit is a
query based on my tlbPets.

frmPetsAtVisit is a subform of frmVisits, which is a subform of frmlTrips
(the misspell of frmlTrips is intentional)
Both tables tblTrips and tblPets has the same foreign key CustomerID

The query has 3 fields in it PetID PetName and CustomerID:
In the Criteria Row of the CustomerID colum I have:
[Forms]![frmlTrips]![CustomerID] (the misspell of frmlTrips is
intentional)
As built for me by the expression builder by going through and choosing the
form and then the control.

This is not working.

The customerID field in the table we are trying to reference is a drop down
list and actually visually shows the customers name but the bound column is
the customerID value i.e.
This value being a foreign key value for the table Customer but is in the
table Trips

In the QUERY
Do I need to put an “=†before the [Forms] in the Criteria Row? I have tried
this no succcess.

If I add the [Form] after the last form name I get the message essentially
telling me that the computer can not find what I am telling it to find.

Is there something to do with primary keys that is causing a problem?

Do I need to add “on update†or some other action to the drop down list
where the pets name is to be populated.

Should I be messing with the query properties "Unique Values" or "Unique
Records"


Thank you again for all your kind help. I feel as thought I am on the edge
of understanding, but I am rather disappointed with Access2003 inside out. I
have spent a lot of time on that book and something as important as
referencing a value in a field of a control on a form and using the value in
a query is not even glimpsed at there, unless I have missed it. It's not such
a bad book.

 

[Lord Kelvan]

i hope you are ok being in a record source for a combo box ^_^

ok you need to use .requery on the form because the form loads then
loads all the bound objects and the combo box. when you change the
selection in the combo box you need to update the query the form uses
to get the information so you need to use frmlTrips.requery or
something like that to refresh the data regardless you will still get
an error tellign you it cannot find squat because at the tiem of the
form loading you dont have a customer selected.

rather than putting [Forms]![frmlTrips]![CustomerID] inthe query just
have the query as normal and in the form on the after update with the
combo box put some code like

frmlTrips.filter = "customerid = " & combobox.value
frmlTrips.requery

and hopefully that should work this is not tested and so therefore the
code may be wrong. alternativally you could ask on the group .forms
for more info

Regards
Kelvan

 

[TravelingHT]

Dear Lord Kelvan:

I am sorry I not quite understand

"frml.Trips.filter =" : Are we are telling the combobox with the pet names
to requery if the info has changed in the "CustomerID" field of the main
form,
Or are we telling the combobox with the pets names to query the Customer ID
field when it's (the combobox with the pets names) value is changed.
If it is the second then we do I need to do a before update so the correct
info is populated in the list.

I have no idea what this is for:

="CustomerID =" & combobox.value
frmlTrips.requery

I am not sure if you are telling me to add my own values in there and some
of the words are place holders.

Either way, thank you for taking the time to do what you have done already.

Yours truly

 

[Lord Kelvan]

on the form if you have a combo box that has a list of values and you
want to select 1 value from that combo box and then have the rest of
the form display information relevant to that combo box you need to
apply a filter and "refresh" the form the requery command does this.

tell me exatally what you are trying to achieve

 

[TravelingHT]

Dear Lord Kelvan:

In a sub sub form, I want the content of a combobox choices to change,
dependent on the "CustomerID" Value chosen in the main form.

To explain the above with an example:

So I have customers who have pets.

Customer John has 2 pets: Fido and Pipin.
Customer David has 2 pets: Rambo and Killer

I get a call from John and he is going on holiday, so I open my database and
open the form Trips and choose from a combobox of customers, which shows the
customers names but actualy holds the "CustomerID" value.

I move on to the sub form "Visits" and I put in the time and date and
special instructions.

Then I move onto the sub sub form "petsAtVisit" and enter what pets are
going to be at the visit, some pets die and some are taken with the people on
hoiday or are being shown in competition so not all the pets of all the
customers are there all the time.

In this sub sub form "PetsAtVisit" I want to have a combobox that is
populated with only the pets names that are owned by the customer who is the
current focus of the main form
I repeat: ONLY the names of the pets, owned by the customer, who is the
current focus of the Main form, should be visable in the drop down list of
the combobox.

If the customer of the main form is John the only choices should be Fido and
Pipin
NOT: Fido, Pipin, Rambo & Killer

Diagram of my current tables
----- Customers ------
| .......................... |
| .........................Trips -Main Form From (Based on table Trips)
| ........................... |
|..........................Visits - SubForm (Based on table Visits)
|............................ |
Pets ....................PetsAtVisit - Sub Sub Form (Based on table
PetsAtVisits)

I have query searched this database and seen the work you have done with
other people on the same subject I am reading those posts with a highlighter
and ruler and pen, so I thank you for all your hard work now and in the past.
I am learning and apreciative of all the ground work you have laid.

Thanks in advance.

TravelingHT

 

[John W. Vinson]

Dear Lord Kelvan:

In a sub sub form, I want the content of a combobox choices to change,
dependent on the "CustomerID" Value chosen in the main form.

Base the combo box on a Query referencing the mainform customerID as a
criterion. You may need to requery the combo box when the customer ID changes.

 

[Lord Kelvan]

mmm to irritrate what john said you need to have an on change event
for the combo box containing the customer id and customer name enfisis
on the customer id

and basically have some code that looks like

petcomboboxname.recordsource = "select petid,petname from pettablename
where pettablename.customerid = " & customercomboboxname.Column(0)
petcomboboxname.requery


so the bit of code that is customercomboboxname.Column(0) shoudl get
the customer id from the customer combo box and then insert it into
that query to get the relevant pet names. this is only as long as the
customer id is column 1 in the customer combo box.

hope this information helps you

Regards
Kelvan

 

[John W. Vinson]

mmm to irritrate what john said you need to have an on change event
for the combo box containing the customer id and customer name enfisis
on the customer id

AfterUpdate event (which fires when a new selection is made), not the Change
event (which fires after every keystroke), actually.

and basically have some code that looks like

petcomboboxname.recordsource = "select petid,petname from pettablename
where pettablename.customerid = " & customercomboboxname.Column(0)
petcomboboxname.requery

And it's not necessarily column(0) - best just to have the combo's Bound
Column set to the ID and use its Value property rather than the Column()
property.

 

[Lord Kelvan]

mmm i dont use bound objects though but yes for the event thing after
update

 

[Marshall Barton]

mmm to irritrate what john said you need to have an on change event
for the combo box containing the customer id and customer name enfisis
on the customer id

and basically have some code that looks like

petcomboboxname.recordsource = "select petid,petname from pettablename
where pettablename.customerid = " & customercomboboxname.Column(0)
petcomboboxname.requery


so the bit of code that is customercomboboxname.Column(0) shoudl get
the customer id from the customer combo box and then insert it into
that query to get the relevant pet names. this is only as long as the
customer id is column 1 in the customer combo box.

Setting the combo box's RowSource automatically reloads the
list, so adding an explicit Requery just wastes time and
resources.

A Me.subfrm1.Form.subfrm2.Form.cboPets.Requery is needed if
John's approach is used. In that case the RowSource query
contains Forms!mainform.Customer in the Pets table's foreign
key field's criteria (instead of resetting the RowSource
property). It is also a common practice to use the same
Requery in the main form's Current event to sync up the
subsub form when just navigating to different main form
records.

 

[Lord Kelvan]

yea i though that but since i wasn't testing it, it was a safe than
sorry thing it has been so long since i built a form

Regards
Kelvan

 

[TravelingHT]

Dear Lord Kelvan and John

I have a lot to take in form your posts. So the below is minus carefull
scruanization of the info you have added. (This I will do this evening)

Thanks to both of you for this, I have realy started to cristalize the
answer in my mind.

I did have it partially working in the past but as soon as I thought I had
cracked it and changed the value in the customerID it stoped working, this is
where the requery will solve the problem.

1. I need a query that has the petID, petName and CustomerID in it from
tlbPet.
2. I need in the criteria line of the CustomerID a way to reference the
value in the original combobox.(Think I have had this working)
3. I need the requery code to recheck when info is changed.

I know you 2 like putting the code in the "Row Source" propertie of the
PetID combobox. But it is visually easier for me to use the query, is there a
problem with using a query?

I will love if I get this working I will be able to help others on this
server in the same way you two did for me.

Again to both of you VERY MUCH THANKS.

TravelingHT

 

[John W. Vinson]

I know you 2 like putting the code in the "Row Source" propertie of the
PetID combobox. But it is visually easier for me to use the query, is there a
problem with using a query?

Kalvan's code *is* creating a Query. The language of queries is SQL; he's
writing VBA code to create SQL. Sure, you can use a query (that was my
suggestion, actually) but you'll need a line of VBA to requery the combo.

 

[TravelingHT]

Dear John:

Yep I got the code, I got the book VBA for Access for absolute beginners, a
Fantastic book, realy helped me get going.

I can write the code, but should I write it in the main form or the form
that is the sub sub form is there a difference or a reason one way or the
other.

Also very much thanks to Marshall Barton, who I forgot to thank in my
previous post.

Have to go to bed, will get back to this tomorrow morning.

Again thanks to all. TravelingHT
--
Anthony Williams
Histotech Exchange LLC
19 Whitmore St.
Lexington, VA 24450
(e-mail address removed)

 

[John W. Vinson]

Dear John:

Yep I got the code, I got the book VBA for Access for absolute beginners, a
Fantastic book, realy helped me get going.

I can write the code, but should I write it in the main form or the form
that is the sub sub form is there a difference or a reason one way or the
other.

I'd suggest putting it in both - in the afterupdate event of the mainform
control containing the ID (so that the combo box reflects the changed ID), and
in the subsubform's current event.

An alternative that I've read but not yet tried is to put the code in the
subsubform combo box's GotFocus event, setting the rowsource of the combo to
the desired query.

 

[TravelingHT]

OK all this is not yet over but I think we are close.

So here is what I have done.

1. Put this code in the frmlTrips (spelling correct)

Option Compare Database
Option Explicit

PrivateSub CustomerID_AfterUpdate()
Me.frmVisit.Form!frmPetsAtVisit.Form!PetID.Requery

End Sub

2. I have a query in the form frmPetsAtVisit.

tblPets.PetID, tblPets.CustomerID, tblPets.PetsName

The query populates the drop down list with all of the pets owned by all of
the customers.

At this point when I open up the form frmlTrips I get no error messages I
can change the name of the person at will but I get every animal owned by
every customer all the time.

3. If I add to the criteria line, and I use the expression builder to do it;
[forms]![frmlTrips]![CustomerID]

I get the message both when I open the query and the form frmlTrips
"Enter Paramater Value" "Forms!frmlTrips!CustomerID"

And I do not get this message unless I put that code into the criteria line
of the customerID colum of the query.

Any thoughts?
Is there anything that tells you you can not query a primary key or something?
And again thanks for your help.

 

[TravelingHT]

Dear all:

Also If I add the customers ID number to the box in the prompt "Enter
Paramater Value" the query works.

I get returned only the pets for that customer.

If I change the name of the customer in the main form there is no effect, no
"Enter Paramater Value" no change of pets names populating the list.

WHY! WHY!

Thanks again to all of you.

TravelingHT

OK all this is not yet over but I think we are close.

So here is what I have done.

1. Put this code in the frmlTrips (spelling correct)

Option Compare Database
Option Explicit

PrivateSub CustomerID_AfterUpdate()
Me.frmVisit.Form!frmPetsAtVisit.Form!PetID.Requery

End Sub

2. I have a query in the form frmPetsAtVisit.

tblPets.PetID, tblPets.CustomerID, tblPets.PetsName

The query populates the drop down list with all of the pets owned by all of
the customers.

At this point when I open up the form frmlTrips I get no error messages I
can change the name of the person at will but I get every animal owned by
every customer all the time.

3. If I add to the criteria line, and I use the expression builder to do it;
[forms]![frmlTrips]![CustomerID]

I get the message both when I open the query and the form frmlTrips
"Enter Paramater Value" "Forms!frmlTrips!CustomerID"

And I do not get this message unless I put that code into the criteria line
of the customerID colum of the query.

Any thoughts?
Is there anything that tells you you can not query a primary key or something?
And again thanks for your help.
--
Traveling Histologist

I'd suggest putting it in both - in the afterupdate event of the mainform
control containing the ID (so that the combo box reflects the changed ID), and
in the subsubform's current event.

An alternative that I've read but not yet tried is to put the code in the
subsubform combo box's GotFocus event, setting the rowsource of the combo to
the desired query.

 

[John W. Vinson]

OK all this is not yet over but I think we are close.

So here is what I have done.

1. Put this code in the frmlTrips (spelling correct)

Option Compare Database
Option Explicit

PrivateSub CustomerID_AfterUpdate()
Me.frmVisit.Form!frmPetsAtVisit.Form!PetID.Requery

End Sub

2. I have a query in the form frmPetsAtVisit.

tblPets.PetID, tblPets.CustomerID, tblPets.PetsName

The query populates the drop down list with all of the pets owned by all of
the customers.

At this point when I open up the form frmlTrips I get no error messages I
can change the name of the person at will but I get every animal owned by
every customer all the time.

3. If I add to the criteria line, and I use the expression builder to do it;
[forms]![frmlTrips]![CustomerID]

I get the message both when I open the query and the form frmlTrips
"Enter Paramater Value" "Forms!frmlTrips!CustomerID"

And I do not get this message unless I put that code into the criteria line
of the customerID colum of the query.

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
exist.

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.

 

[TravelingHT]

Dear John:

So this is what I have:

I have a "Combo Box" called "Customer ID, this is what comes up at the top
of properties, this is what I am trying to reference in my query select
language.

I have it's Row Source set to tblCustomers, the bound column is colum
1(CustomerID) but the column withs are 0;1.3;1.3; so you only see first and
last names, not CustomerID.

To try and answer your question & Sujestion:

1.So when you say is customer ID only a field name. It is a Field Name in
the underlying table tblCustomer. But also the name of the ComboBox in the
From frmCustomer. (I know I should have called it cmbCustomerID) but I am
still learning.

2.I am going to take my Combobox and write code to send the value to a Label
with code and see if I can reference the label with any better success.

Thanks again for your time.

--
Traveling Tech

OK all this is not yet over but I think we are close.

So here is what I have done.

1. Put this code in the frmlTrips (spelling correct)

Option Compare Database
Option Explicit

PrivateSub CustomerID_AfterUpdate()
Me.frmVisit.Form!frmPetsAtVisit.Form!PetID.Requery

End Sub

2. I have a query in the form frmPetsAtVisit.

tblPets.PetID, tblPets.CustomerID, tblPets.PetsName

The query populates the drop down list with all of the pets owned by all of
the customers.

At this point when I open up the form frmlTrips I get no error messages I
can change the name of the person at will but I get every animal owned by
every customer all the time.

3. If I add to the criteria line, and I use the expression builder to do it;
[forms]![frmlTrips]![CustomerID]

I get the message both when I open the query and the form frmlTrips
"Enter Paramater Value" "Forms!frmlTrips!CustomerID"

And I do not get this message unless I put that code into the criteria line
of the customerID colum of the query.

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
exist.

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.

 

[TravelingHT]

Dear John:

The Control Name "CustomerID" relates to a combobox in the form
frmlTrips(spellingCorrect). So I am not trying to reference a control in an
underlying table. I hope that is what you where asking.

I am considering sending the valule of the result of the combobox to a
label, using code, making it non visable and referencing that in my query.

Currently the combobox named CustomerID references the tlbCustomer and binds
column 1 (CustomerID) but displays column 2&3, first and last names.

Thanks for your help.

Traveling HT


Now
--
Anthony Williams
Histotech Exchange LLC
19 Whitmore St.
Lexington, VA 24450
(e-mail address removed)

OK all this is not yet over but I think we are close.

So here is what I have done.

1. Put this code in the frmlTrips (spelling correct)

Option Compare Database
Option Explicit

PrivateSub CustomerID_AfterUpdate()
Me.frmVisit.Form!frmPetsAtVisit.Form!PetID.Requery

End Sub

2. I have a query in the form frmPetsAtVisit.

tblPets.PetID, tblPets.CustomerID, tblPets.PetsName

The query populates the drop down list with all of the pets owned by all of
the customers.

At this point when I open up the form frmlTrips I get no error messages I
can change the name of the person at will but I get every animal owned by
every customer all the time.

3. If I add to the criteria line, and I use the expression builder to do it;
[forms]![frmlTrips]![CustomerID]

I get the message both when I open the query and the form frmlTrips
"Enter Paramater Value" "Forms!frmlTrips!CustomerID"

And I do not get this message unless I put that code into the criteria line
of the customerID colum of the query.

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
exist.

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.