Writing Code to Open Query after user selected fields to include from ListBox

shuffine@hcreit.com

Registered User.
Local time
Today, 17:32
Joined
Dec 18, 2013
Messages
19
Access 2007-10
Listbox created: List62 (I know I need to rename it, but for now)
Multi-select: Extended
Row Source Type: Field List
Row Source: qryFieldList
Open Query: qrySelectedFields (I added primary key to first column, just to have at least one destination field).

Goal: to select multiple fields within listbox, click on command button to open query "qrySelectedFields" with selected fields from list box.

This is the code I have on the command_click:

Dim varItem As Variant
Dim strSQL As String
If Me.List62.ItemsSelected.Count = 0 Then
MsgBox "Please select one or more fields.", vbExclamation, "Healthcare REIT"
Me.List62.SetFocus
End If
Exit Sub
For Each varItem In Me.List62.ItemsSelected
strSQL = strSQL & ",[" & varItem & "]"
Next varItem
strSQL = "SELECT" & Mid(strSQL, 3) & " FROM qryFieldList"
CurrentDb.QueryDefs("qrySelectedFields").SQL = strSQL
DoCmd.OpenQuery "qrySelectedFields", acViewNormal, acReadOnly


It does absolutely nothing - doesn't add the fields to "qrySelectedFields", doesn't open the query, notta. Please help! :banghead:
 
Your code is unreadable, no wonder you cant spot the mistake
Code:
Dim varItem As Variant
Dim strSQL As String
If Me.List62.ItemsSelected.Count = 0 Then
    MsgBox "Please select one or more fields.", vbExclamation, "Healthcare REIT"
    Me.List62.SetFocus
End If
Exit Sub

strSQL = "SELECT" & Mid(strSQL, 3) & " FROM qryFieldList"

Failing that try adding
Debug.print strSQL and manually trying the resulting query in a new query object, should show you your problem
 
Sorry it didn't keep the format when I copied and paste, and got in a rush and click the submit button too soon.

I hope this is better:

Dim varItem As Variant
Dim strSQL As String

If Me.List62.ItemsSelected.Count = 0 Then
MsgBox "Please select one or more fields.", vbExclamation, "Healthcare REIT"
Me.List62.SetFocus
End If
Exit Sub

For Each varItem In Me.List62.ItemsSelected
strSQL = strSQL & ",[" & varItem & "]"
Next varItem

strSQL = "SELECT" & Mid(strSQL, 3) & " FROM qryFieldList"

CurrentDb.QueryDefs("qrySelectedFields").SQL = strSQL
DoCmd.OpenQuery "qrySelectedFields", acViewNormal, acReadOnly


With that, I am not real sure how to approach your last suggestion:
Try adding Debug.print strSQL and manually trying the resulting query in a new query object, should show you your problem Where are you wanting me to add this? within the code above? and are you saying to create a new query? I have never used the debug.print function before. I appreciate your help.
 
it won't keep the indented lines indented either - but hoping it is better than the first try.
 
Try using CODE tags when posting code that needs to keep spacing
[cade]Code here
More code [/cade]
Replacing the a for an O to make it work
Or simply click the # sign in the menu of the post

Code:
Dim varItem As Variant
Dim strSQL As String
 
[COLOR="Red"]STOP[/COLOR]

If Me.List62.ItemsSelected.Count = 0 Then
       MsgBox "Please select one or more fields.", vbExclamation, "Healthcare REIT"
       Me.List62.SetFocus
End If
Exit Sub
 
For Each varItem In Me.List62.ItemsSelected
       strSQL = strSQL & ",[" & varItem & "]"
Next varItem
 
strSQL = "SELECT" & Mid(strSQL, 3) & " FROM qryFieldList"
 
CurrentDb.QueryDefs("qrySelectedFields").SQL = strSQL
[COLOR="YellowGreen"]debug.print strSQL[/COLOR]
DoCmd.OpenQuery "qrySelectedFields", acViewNormal, acReadOnly

The debug.print you would add there.... added for you in green.
If you have never used debug.print, have you ever used STOP and/or F8 to step thru your code line by line?

Stop added in red so the code stops executing there and you then execute the code line by line by pressing F8
 
Namliam,
That is a great trick - I have read many books about the line-by-line but no one has made it understandable and easy as this. Thank you.

So, it went through each line, and when it got to:

CurrentDb.QueryDefs("qrySelectedFields").SQL = strSQL

a run-time error occurred:

Invalid SQL statement; expected DELETE, INSERT, PROCEDURE, SELECT, or UPDATE

I have one field included in the grid of my query. I am at a lose on how to proceed.
 
That is surprising, given that the code you posted will never get to that line.....

You can try putting the debug.print in front of that line
i.e.
debug.print strSQL
CurrentDb.QueryDefs("qrySelectedFields").SQL = strSQL

So you can actually see the SQL, also if you are getting to that line with your code... The code you are using is different from the one here on the forum.
 
I removed the Exit Sub then tried it again.

and that's the message I receive after this line is highlighted:

CurrentDb.QueryDefs("qrySelectedFields").SQL = strSQL

When I remove the stop and run the code - it receives another run-time error:

The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.


I think I know what is going on...but not real sure. The query I created for the listbox, I named the field differently (allowing the user to understand what the field is). So when selecting the fields in the listbox, it isn't matching with the fields in the "result" query. Am I close?
 
No you think wrong, did you do the debug.print strSQL and did you look at the SQL that is printed in the immediate window?

or attempted to look at the SQL in the query ??

Since I am about to log off for the day I will give you a break :D

This line:
strSQL = "SELECT" & Mid(strSQL, 3) & " FROM qryFieldList"

Results in an invalid SQL statement for 2 reasons....
1) there is no space behind select
2) the 3 needs to be 2

strSQL = "SELECT " & Mid(strSQL, 2) & " FROM qryFieldList"
 

Users who are viewing this thread

Back
Top Bottom