Re: Simple query with criteria dependent on current form value   

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]

date: Mon, 01 Sep 2008 18:55:44 -0600   author:   John W. Vinson

Re: Simple query with criteria dependent on current form value   

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]

date: Mon, 01 Sep 2008 23:49:41 -0600   author:   John W. Vinson

Re: Simple query with criteria dependent on current form value   

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]

date: Tue, 02 Sep 2008 11:03:15 -0600   author:   John W. Vinson