VBA SQL String from Controls on Form

jccrvb

Registered User.
Local time
Today, 15:52
Joined
Apr 8, 2009
Messages
10
Hi Bob...

Set mydb = CurrentDb()
On Error Resume Next
DoCmd.DeleteObject acQuery, "test"

strSQL = "SELECT * FROM tblScrubbedData "
strWhere = "WHERE "

If Not IsNull(Me.cbo_codeshare) Then
strWhere = "[CodeShare] In ("
For Each varItem In Me.cbo_codeshare.ItemsSelected
strWhere = strWhere & Me.cbo_codeshare.ItemData(varItem) & ", "
Next
flgSelectAll = True
strWhere = Left(strWhere, Len(strWhere) - 2) & ") "
End If

If Not IsNull(Me.lst_eqpa) Then
strWhere = "[EqpA] In ("
For Each varItem In Me.lst_eqpa.ItemsSelected
strWhere = strWhere & Me.lst_eqpa.ItemData(varItem) & ", "
Next
flgSelectAll = True
strWhere = Left(strWhere, Len(strWhere) - 2) & ") "
End If

If Not IsNull(Me.lst_orgs) Then
strWhere = strWhere & "[OrgS] In ("
For Each varItem In Me.lst_orgs.ItemsSelected
strWhere = strWhere & Me.lst_orgs.ItemData(varItem) & ", "
Next
flgSelectAll = True
strWhere = Left(strWhere, Len(strWhere) - 2) & ") "
End If

'########### I THINK THIS IS SUPPOSED TO BE "IF NOT flgSelectAll" ####################
'########### But I get NOTHING AT ALL if I put IF NOT here ####################


If flgSelectAll Then
strSQL = strSQL & strWhere
End If

Set qdef = mydb.CreateQueryDef("test", strSQL)
DoCmd.OpenQuery "test", acViewNormal

For Each varItem In Me.cbo_codeshare.ItemsSelected
Me.cbo_codeshare.Selected(varItem) = False
Next varItem

For Each varItem In Me.lst_orgs.ItemsSelected
Me.lst_orgs.Selected(varItem) = False
Next varItem

For Each varItem In Me.lst_eqpa.ItemsSelected
Me.lst_eqpa.Selected(varItem) = False
Next varItem

strWhere = ""
 
Can you post the database? I PM'd you the link to the instructions on how to upload.

Also, this was in the wrong category (this is for VB6). This one would go in the Modules and VBA normally (just an FYI) :)
 
Oops...I am trying to put together the form and table now in a temp db...I am linked to a SQL server, have pw protection...etc...yikes!
 
Hi Bob,
Here you go...I stripped as much as I could simply to make db smaller...
Thanks,
me
 

Attachments

Here you go - there was actually quite a few more things to take care of.

Hopefully it will be self-explanatory but if you have questions post back.
 

Attachments

Oh, forgot to tell you that you might either

1. Remove the *ALL* feature from the listboxes or else

2. Add code to bypass that part if *ALL* is selected. (I didn't do that)
 
Hi Bob,
Thank you so much. I just now got to looking at it. I thank you for the date code...I was just going to append that as part of the "WHERE"...but your way is much more clean and efficient. I do intend to remove the "*ALL*" UNION from the row sources, to be sure.
I am going to read through line by line...as you stated, hopefully I will understand it...if not, I'll take you up on your offer to ask.
I will reply again when I totally understand it.
Again Thank You!
 
Hi Bob,
Working great! I also added a "strFROM" that includes JOINS and a "strGROUP"! I can't thank you enough for your help...and I like this board too...I thank you for that too...
 
Hi Bob,
Working great! I also added a "strFROM" that includes JOINS and a "strGROUP"! I can't thank you enough for your help...and I like this board too...I thank you for that too...

Cool, glad to hear that you seem to be "on your way" with it. Although it is not the simplest of things to do, doing the dynamic SQL building can be very powerful. I still remember me going over to someone at Boeing, back about 12 years ago and asking them if it was possible to do what you and I just did. They said it was possible but they didn't want to do it for me and they didn't have time to teach me. So I was stuck and it took quite a while longer until I got this down. So, I can understand totally how it can be to try to get something to work like that. I also know how much nicer it can make your user interface :D. Good luck and feel free to post other questions as they come up. I would say you are good on either forum and we have some really sharp people here as well.
 
Oh, so I'll also give you the official:

welcometoawf.png
 

Users who are viewing this thread

Back
Top Bottom