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.

-- 
Anthony Williams

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

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
-- 
Anthony Williams




"Lord Kelvan" wrote:

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

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

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


"Lord Kelvan" wrote:

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

On Mon, 1 Sep 2008 14:21:01 -0700, TravelingHT
<akwillims75@hotmail.com(stopthespamer)> wrote:

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

             John W. Vinson [MVP]

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

On Mon, 1 Sep 2008 15:19:26 -0700 (PDT), Lord Kelvan 
wrote:

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

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

             John W. Vinson [MVP]

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

Lord Kelvan wrote:

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

-- 
Marsh
MVP [MS Access]

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

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


"Lord Kelvan" wrote:

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

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
anthony@histotechexchange.com



"John W. Vinson" wrote:

> On Mon, 1 Sep 2008 17:23:00 -0700, TravelingHT
> <akwillims75@hotmail.com(stopthespamer)> wrote:
> 
> >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.
> -- 
> 
>              John W. Vinson [MVP]
>

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


"John W. Vinson" wrote:

> On Mon, 1 Sep 2008 18:29:10 -0700, TravelingHT
> <akwillims75@hotmail.com(stopthespamer)> wrote:
> 
> >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.
> -- 
> 
>              John W. Vinson [MVP]
>

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


"TravelingHT" wrote:

> 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
> 
> 
> "John W. Vinson" wrote:
> 
> > On Mon, 1 Sep 2008 18:29:10 -0700, TravelingHT
> > <akwillims75@hotmail.com(stopthespamer)> wrote:
> > 
> > >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.
> > -- 
> > 
> >              John W. Vinson [MVP]
> >

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


"John W. Vinson" wrote:

> On Tue, 2 Sep 2008 08:15:07 -0700, TravelingHT
> <akwillims75@hotmail.com(stopthespamer)> wrote:
> 
> >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.
> -- 
> 
>              John W. Vinson [MVP]
>

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
anthony@histotechexchange.com



"John W. Vinson" wrote:

> On Tue, 2 Sep 2008 08:15:07 -0700, TravelingHT
> <akwillims75@hotmail.com(stopthespamer)> wrote:
> 
> >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.
> -- 
> 
>              John W. Vinson [MVP]
>

Just to let you know, if anyone is still with me, the Row Source for the 
combobox is QryCustomerName Not sure if this makes any difference.

Yours truly.

Traveling HT


"TravelingHT" wrote:

> 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
> anthony@histotechexchange.com
> 
> 
> 
> "John W. Vinson" wrote:
> 
> > On Tue, 2 Sep 2008 08:15:07 -0700, TravelingHT
> > <akwillims75@hotmail.com(stopthespamer)> wrote:
> > 
> > >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.
> > -- 
> > 
> >              John W. Vinson [MVP]
> >