Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 4th, 2003, 09:04 PM
Authorized User
 
Join Date: Sep 2003
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
Default Direct data access instead of sub-query

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
 
Old November 5th, 2003, 10:26 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old November 5th, 2003, 08:54 PM
Authorized User
 
Join Date: Sep 2003
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old November 6th, 2003, 11:55 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
DIRECT ACCESS TO DATASET FIELDS kostaskon Pro VB Databases 0 February 27th, 2008 12:54 PM
Exporting Query Data From Access to Excel JimInSouthernCal Access VBA 3 December 21st, 2007 08:34 AM
C# Structure (to sort and direct access) murallas C# 0 December 27th, 2006 05:43 AM
Passing values direct to data report without shlsoft VB Databases Basics 0 November 6th, 2006 07:51 AM
Retrieving data direct from dbase?? junemo MySQL 16 June 16th, 2004 02:08 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.