pass list from form to query

  • Thread starter Thread starter mike_0_007
  • Start date Start date
M

mike_0_007

Hi, I'm having a problem trying to build query selection criteria from
a form... what I'm trying to do is
pass a value from vba to a textbox for use by a query. The value is a
LIST as follows -
in('a','b','c')

the query works ok if i type the above in directly into the field. But
if i try and use the variable
i always return zero records. I've tried =, like, and various
combinations between the textbox
value and the surrounding string in the query, with no luck. Can
anyone help me out?
 
You'll have to write the query in code as a VBA select statement. Something
like:

"SELECT CostCode, ItemID, CostCodeName, ModelID FROM tblItem WHERE ModelID
In (" & Me.txtSelected & ");"

txtSelected is a hidden text box holding the value for the in clause.
 
You'll have to write the query in code as a VBA select statement. Something
like:

"SELECT CostCode, ItemID, CostCodeName, ModelID FROM tblItem WHERE ModelID
In (" & Me.txtSelected & ");"

txtSelected is a hidden text box holding the value for the in clause.
Click to expand...

thanks for the reply.. can you explain why this is so? i can pass
other values my way without issue.
can't pass a list?
 
thanks for the reply.. can you explain why this is so? i can pass
other values my way without issue.
can't pass a list?
Click to expand...

I'm not absolutely sure, but I think that the query expression service reads
a variable as a single value. If you knew the maximum number of values you
had, you could probably put that many hidden text boxes on your form and do
something like:

Where ID In(Forms!Form1!Text1, Forms!Form1!Text2, Forms!Form1!Text3)
 
I'm not absolutely sure, but I think that the query expression service reads
a variable as a single value. If you knew the maximum number of values you
had, you could probably put that many hidden text boxes on your form and do
something like:

Where ID In(Forms!Form1!Text1, Forms!Form1!Text2, Forms!Form1!Text3)
Click to expand...

nice idea, and that method does work. I appreciate it!
 
Back
Top