Using DLookUp in a combo box

[Guest]

Can I use a DLookUp function in a combo box to limit the choices it gives
based on a selection made in a separate combo box in the same form? If so,
how? Is there another/easier way to do it?

 

[Douglas J Steele]

Sorry, but I don't understand your question. How would you use DLookup in a
combo box?

Sounds as though you're trying to create cascading combo boxes.

See http://office.microsoft.com/en-au/assistance/HA011730581033.aspx or
http://support.microsoft.com/?id=209576 or
http://www.fontstuff.com/access/acctut10.htm for suggestions

 

[Guest]

I doubt the DLookup would be what you want. DLookup returns only one value.
The normal way to do what you want is called "Cascading Combo Boxes". The
technique is to create a query for the row source for combo 2 that is
filtered on the value in combo 1 and requery combo 2 in the After Update
event of combo1.

So lets say Combo 2 has a row source of:

"SELECT Style_Code from tblProducts WHERE Product_Code = '" & Me.ComboONe &
"';"

Now, to have Combo 2 show only the styles available for the Product selected
in Combo 1:

Private Sub ComboOne_AfterUpdate()

Me.ComboTwo.Requery

End Function

 

[Guest]

I am just starting to really learn access, so i appologize, but what is meant
by the "Me" in me.combo1 or me.combo2.requery? Cascading Combo Boxes seems
to work however I can not get combo2 to update when combo1 updates, even when
i requery the form. I'm thinking this is becuase I did not include the "me"
because I didn't understand what it meant and could not get it to work when
included.

 

[Guest]

Me. is a shorcut way of saying Forms!MyFormName when you are referring to
the current form. You should always use one of the two qualifiers above.

strSomeVAr = Combo1
is usually valid, but there are times Access can get confused about who
Combo1 belongs to. It is also easier for you and anyone who has to read your
code to know what you are referencing. You should use:
strSomeVar = Me.Combo1

I doubt that this naming problem caused you not to get the result you
expected. The sequence is:
In the AfterUpdate event of Combo 1, requery Combo 2
In the AfterUpdate event of Combo 2, requery the form, there is no need to
requery Combo 1 or 2

If you would post your code, I can have a look at it to see if there are
problems.