Simple query with criteria dependent on current form value

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

On Mon, 1 Sep 2008 17:23:00 -0700, TravelingHT
(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]

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




"John W. Vinson" wrote:

On Mon, 1 Sep 2008 17:23:00 -0700, TravelingHT
(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]

On Mon, 1 Sep 2008 18:29:10 -0700, TravelingHT
(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]

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

On Tue, 2 Sep 2008 08:15:07 -0700, TravelingHT
(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.Requer y

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:

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
(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.Requer y

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




"John W. Vinson" wrote:

On Tue, 2 Sep 2008 08:15:07 -0700, TravelingHT
(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.Requer y

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]