When using an SQL statement in VB is there a way it can access your GUI information?
Here is an example of what I am talking about:

If by use of check boxes I have a number of items that can be added to group. Can the SQL statement make use of this group instead of a table?

Docmd.runSQL "INSERT INTO [Some Table] (data1, data2, data3, …, datan) " _
           & "SELECT stuff FROM [Some Other Table] " _
           & "WHERE specificItem IN (An array, or some other data container)

I am currently making a temporary table and storing a list of 2 or 3 values inside it. This means that I have the overhead of creating a table, having the table queried and then deleting the table when I’m done, when I could spoon feed the correct data directly into the SQL statement!

Any advice would be appreciated.

Thanks
-Roni


Roni Estein
[email protected]
https://www.e-drugsCanada.com

__________________
Roni Estein
[email protected]
https://www.e-drugsCanada.com

You can use the In() predicate. for each check box, if it is checked, you can add the item to a variable like this IN(1,2,3,5,8)

dim i as integer 'form level variable
dim strInString as string 'form level variable


'sub level
if i = 0 then
    strInString = listbox1.value
else
        strInString = strInString & "," & listbox1.value
end if


 when you submit the value to create the sql statement you do this

dim str as string

str = Select * from employees where empid IN( "
str = str & strInString
str = str & ");"

I would probably use a list box and have the user select the values from a combo box one by one into the list box. On each click event for combo box, add new item to listbox. then do a loop for each item. If the user select an item twice, the IN predicate will only use one anyway.
Hope this helps.



Sal

Thanks Sal,

It worked like a charm, does that work for all data type? Does it just assume and interpret what it is supposed to find?

-Roni

Roni Estein
[email protected]
https://www.e-drugsCanada.com

No. if you want to add a string, use '' to separate items. It also depends on the back end database, Jet, SQL Server, Oracle. All you are doing is creating a string anyway so there is no interpretation.




Sal