I have a form called LookUpByStore that displays records from a query. The records are products that we carry in our chain of retail stores. I have a combobox at the top of the form called StoreCombo that lists the store numbers as text. ("109", "111", etc.)
I have a textbox called txtSearch and a button called SearchBtn. I wrote the code below to fetch the store number from the StoreCombo and then search for products within that store only. The columns involved are:
StoreNum - which is chosen in the combobox
and then I need to search these three columns for whatever is entered in the textbox:
ItemNameWeight - which is a text field
ItemNum - which is a number
ItemUPCCode - which is text
Here is my code currently, which I understand searches the ItemNameWeight AND sets the StoreNum, while the ItemNum and ItemUPCCode are searched without the storenum being set. I had this set as a macro, which worked, but I need to add the StoreNum to the other two fields as well, so it will go over the 255 character limit for macros.
[StoreNum]=[Forms]![LookupByStore]![StoreCombo] And [ItemNameWeight] Like"*"& [Forms]![LookupByStore]![txtSearch] &"*"Or [ItemNum] Like"*"& [Forms]![LookupByStore]![txtSearch] &"*"Or [ItemUPCCode] Like"*"& [Forms]![LookupByStore]![txtSearch]
I tried to move the code to VBA like this:
PrivateSub SearchBtn_Click()
DoCmd.ApplyFilter , [StoreNum] = [Forms]![LookupByStore]![StoreCombo] And [ItemNameWeight] Like"*"& [Forms]![LookupByStore]![txtSearch] &"*"Or [ItemNum] Like"*"& [Forms]![LookupByStore]![txtSearch] &"*"Or [ItemUPCCode] Like"*"& [Forms]![LookupByStore]![txtSearch] &"*"
EndSub
But this gives me an error 2427 "You entered an expression that has no value"
A) What am I doing wrong
and
B) What is the best practice to add StoreNum to the other two columns in VBA, because I know writing, "[StoreNum] = [Forms]![LookupByStore]![StoreCombo] And " each time is not the correct way to do it.
Thanks, Tony