By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,967 Members | 1,208 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,967 IT Pros & Developers. It's quick & easy.

SQL query in OnClick subroutine of a listbox

P: 2
I want to write an SQL query inside the Onclick event of a list which will allow me to populate another list based on the selected value.

So suppose the selected value is
Dim ID As Integer
ID = SourceList.Column(0) //This works fine btw

I want to be able to do something like this
Expand|Select|Wrap|Line Numbers
  1. DestinationList.Value(
  2. select field1,field2,field3
  3. from tablexyz
  4. where tablexyz.field1=ID
  5. )
The problem is:I don't know how to tell it to link the destination list to my select query
Sep 16 '08 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 15k+
P: 31,760
Not too far off Saratee.

Marseilles v Liverpool is just about to kick off, but when I can I will come back and look at giving some pointers.

As I say you're not too far off already. The SQL would need to be built up in a VBA string and the ListBox.RecordSource (not .Value) is what needs to be set.
Sep 16 '08 #2

Expert Mod 15k+
P: 31,760
Something like :
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. ...
  3. strSQL = "SELECT [Field1],[Field20,[Field3] " & _
  4.           "FROM [TableXYZ] " & _
  5.           "WHERE [Field1]=%ID"
  6. strSQL = Replace(strSQL, "%ID", Me.ID)
  7. DestinationList.RecordSource = strSQL
First you set up the variable strSQL with the SQL template (%ID is a placeholder), then you replace %ID with the actual value found on the form. This assumes Me.ID is a numeric value.
Sep 16 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.