Multiple forms in query string

  • Thread starter Thread starter TooN
  • Start date Start date
T

TooN

Hello,

I have a form that contains two comboboxes. The first combo box is for a
product and the second for a location. The two comboboxes are linked to each
other according to entrance.

An example of what i implemented is here:

http://accessprogrammer.blogspot.com/2005/08/filter-combobox-based-on-another.html

The problem is that i have TWO forms with the same comboboxes data input.

How do i adjsut this string:

SELECT DISTINCT tblProductLocation.Product, tblProductLocation.Location
FROM tblProductLocation
WHERE (((tblProductLocation.Product)=forms!frmOrder.Product.value));

As you can see the form "frmOrder" is ok. How do i add another form in this
string ("frmOrderCreate")?

Thanks
 
You can do one of two things.

1. Rewrite the query so that it points to the 1st combo in the second form,
for the second form

or

2. This is the way I do it. I create a function that will return the
currently selected ProductID. It would look something like:

Public Function fnProdID(Optional SomeValue as variant = Null) as long

Static myProdID as long

If not isnull(SomeValue) then myProdID = SomeValue
fnProdID = myProdID

End Function

Because this function uses a static variable (myProdID), it retains its
value between calls to the function. So, in the AfterUpdate event of the 1st
combo box, you would have code similar to:

Private Sub cbo_Combo1_AfterUpdate

Call fnProdID(me.combo1)
me.cbo_Combo2.requery

End sub

Then, modify the query for the second combo box, so that it refers to the
function:

SELECT DISTINCT Product, Location
FROM tblProductLocation
WHERE Product = fnProdID()

Because you passed the value of the ProductID to the function in the
previous step, calling it now without a value will return the value that you
pushed into it.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
 
Back
Top