Dynamic Query, Using ListBox and VBA

  • Thread starter Thread starter ryguy7272
  • Start date Start date
R

ryguy7272

I originally posted my question in the Queries DG, but didn’t get a response
from there. Since the question has a lot of VBA, I think I may get a
response from here. Basically, I am trying to create a Query on the fly,
using variables from a ListBox.

I got the idea, and the code, from this site:
http://www.databasedev.co.uk/queries.html

The name of the file that I am working with is:
Using a Microsoft Access Listbox to pass criteria to a query


This is the Row Source in my own ListBox:
SELECT DISTINCT tblLogin.strLogin FROM tblLogin UNION select "All" from
tblLogin;

When I double-click the Form to open it, I get prompted for an input, like
it is some kind of parameter Query, but this is when I open the Form, not the
Query. I think I need to use the UNION and the 'All'. However, when I do, I
only see the word 'All', and nothing else.

I would like the QSL to be something like this:
SELECT tblLogin.Login, *
FROM tblLogin
WHERE (((tblLogin.Login) In ('brooks')));

Below is the code:
Option Compare Database

Private Sub cmdOpenQuery_Click()

On Error GoTo Err_cmdOpenQuery_Click
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant

Set MyDB = CurrentDb()

strSQL = "SELECT * FROM tblLogin"

'Build the IN string by looping through the listbox
For i = 0 To lstLogin.ListCount - 1
If lstLogin.Selected(i) Then
strIN = strIN & "'" & lstLogin.Column(0, i) & "',"
End If
Next i

'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [strLogin] in (" & Left(strIN, Len(strIN) - 1) & ")"


MyDB.QueryDefs.Delete "qryLoginByID"
Set qdef = MyDB.CreateQueryDef("qryLoginByID", strSQL)

'Open the query, built using the IN clause to set the criteria
DoCmd.OpenQuery "qryLoginByID", acViewNormal

'Clear listbox selection after running query
For Each varItem In Me.lstLogin.ItemsSelected
Me.lstLogin.Selected(varItem) = False
Next varItem


Exit_cmdOpenQuery_Click:
Exit Sub

Err_cmdOpenQuery_Click:

If Err.Number = 5 Then
MsgBox "You must make a selection(s) from the list", , "Selection
Required !"
Resume Exit_cmdOpenQuery_Click
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_cmdOpenQuery_Click
End If

End Sub
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click


DoCmd.Close

Exit_cmdClose_Click:
Exit Sub

Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click

End Sub

When I run the Query, all values are returned!!

This is the SQL:
SELECT *
FROM tblLogin;

That's why all variables are returned. What I can't figure out is why the
appropriate string is not passed to the Query...
I think this is the string now, but it seems to be incorrect:
strWhere = " WHERE [strLogin] in (" & Left(strIN, Len(strIN) - 1) & ")"

I did this before and it worked fine; the solution escapes me now. Can
anyone see the error? I can't see it.

Regards,
Ryan---
 
Hi,
I am no VBA expert but . . .

It may be as simple as adding an extra Parentheses.
(" & Left(strIN, Len(strIN) - 1) & ")"
like this:
(" & Left(strIN, (Len(strIN) - 1)) & ")"

I find that if I think there is a problem I use variables and then step
through the code using F8 to check the value of the variables.

Dim strVar As String
Dim intVar As Integer

intVar = Len(strIN)-1
Debug.Print "intVar = " & intVar

strVar = Left(intVar)
Debug.Print "strVar = " & strVar

strWhere = " WHERE [strLogin] in (" & strVar & ")"
Debug.Print "strWhere= " & strWhere

Best Regards,
Patrick Wood

ryguy7272 said:
I originally posted my question in the Queries DG, but didn’t get a response
from there. Since the question has a lot of VBA, I think I may get a
response from here. Basically, I am trying to create a Query on the fly,
using variables from a ListBox.

I got the idea, and the code, from this site:
http://www.databasedev.co.uk/queries.html

The name of the file that I am working with is:
Using a Microsoft Access Listbox to pass criteria to a query


This is the Row Source in my own ListBox:
SELECT DISTINCT tblLogin.strLogin FROM tblLogin UNION select "All" from
tblLogin;

When I double-click the Form to open it, I get prompted for an input, like
it is some kind of parameter Query, but this is when I open the Form, not the
Query. I think I need to use the UNION and the 'All'. However, when I do, I
only see the word 'All', and nothing else.

I would like the QSL to be something like this:
SELECT tblLogin.Login, *
FROM tblLogin
WHERE (((tblLogin.Login) In ('brooks')));

Below is the code:
Option Compare Database

Private Sub cmdOpenQuery_Click()

On Error GoTo Err_cmdOpenQuery_Click
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant

Set MyDB = CurrentDb()

strSQL = "SELECT * FROM tblLogin"

'Build the IN string by looping through the listbox
For i = 0 To lstLogin.ListCount - 1
If lstLogin.Selected(i) Then
strIN = strIN & "'" & lstLogin.Column(0, i) & "',"
End If
Next i

'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [strLogin] in (" & Left(strIN, Len(strIN) - 1) & ")"


MyDB.QueryDefs.Delete "qryLoginByID"
Set qdef = MyDB.CreateQueryDef("qryLoginByID", strSQL)

'Open the query, built using the IN clause to set the criteria
DoCmd.OpenQuery "qryLoginByID", acViewNormal

'Clear listbox selection after running query
For Each varItem In Me.lstLogin.ItemsSelected
Me.lstLogin.Selected(varItem) = False
Next varItem


Exit_cmdOpenQuery_Click:
Exit Sub

Err_cmdOpenQuery_Click:

If Err.Number = 5 Then
MsgBox "You must make a selection(s) from the list", , "Selection
Required !"
Resume Exit_cmdOpenQuery_Click
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_cmdOpenQuery_Click
End If

End Sub
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click


DoCmd.Close

Exit_cmdClose_Click:
Exit Sub

Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click

End Sub

When I run the Query, all values are returned!!

This is the SQL:
SELECT *
FROM tblLogin;

That's why all variables are returned. What I can't figure out is why the
appropriate string is not passed to the Query...
I think this is the string now, but it seems to be incorrect:
strWhere = " WHERE [strLogin] in (" & Left(strIN, Len(strIN) - 1) & ")"

I did this before and it worked fine; the solution escapes me now. Can
anyone see the error? I can't see it.

Regards,
Ryan---
Click to expand...
 
I originally posted my question in the Queries DG, but didn’t get a response
from there.  Since the question has a lot of VBA, I think I may get a
response from here.  Basically, I am trying to create a Query on the fly,
using variables from a ListBox.  

I got the idea, and the code, from this site:http://www.databasedev.co.uk/queries.html

The name of the file that I am working with is:
Using a Microsoft Access Listbox to pass criteria to a query

This is the Row Source in my own ListBox:
SELECT DISTINCT tblLogin.strLogin FROM tblLogin UNION select "All" from
tblLogin;

When I double-click the Form to open it, I get prompted for an input, like
it is some kind of parameter Query, but this is when I open the Form, not the
Query. I think I need to use the UNION and the 'All'. However, when I do, I
only see the word 'All', and nothing else.

I would like the QSL to be something like this:
SELECT tblLogin.Login, *
FROM tblLogin
WHERE (((tblLogin.Login) In ('brooks')));

Below is the code:
Option Compare Database

Private Sub cmdOpenQuery_Click()

On Error GoTo Err_cmdOpenQuery_Click
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant

Set MyDB = CurrentDb()

strSQL = "SELECT * FROM tblLogin"

'Build the IN string by looping through the listbox
For i = 0 To lstLogin.ListCount - 1
If lstLogin.Selected(i) Then
strIN = strIN & "'" & lstLogin.Column(0, i) & "',"
End If
Next i

'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [strLogin] in (" & Left(strIN, Len(strIN) - 1) & ")"

MyDB.QueryDefs.Delete "qryLoginByID"
Set qdef = MyDB.CreateQueryDef("qryLoginByID", strSQL)

'Open the query, built using the IN clause to set the criteria
DoCmd.OpenQuery "qryLoginByID", acViewNormal

'Clear listbox selection after running query
For Each varItem In Me.lstLogin.ItemsSelected
Me.lstLogin.Selected(varItem) = False
Next varItem

Exit_cmdOpenQuery_Click:
Exit Sub

Err_cmdOpenQuery_Click:

If Err.Number = 5 Then
MsgBox "You must make a selection(s) from the list", , "Selection
Required !"
Resume Exit_cmdOpenQuery_Click
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_cmdOpenQuery_Click
End If

End Sub
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click

DoCmd.Close

Exit_cmdClose_Click:
Exit Sub

Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click

End Sub

When I run the Query, all values are returned!!

This is the SQL:
SELECT *
FROM tblLogin;

That's why all variables are returned. What I can't figure out is why the
appropriate string is not passed to the Query...
I think this is the string now, but it seems to be incorrect:
strWhere = " WHERE [strLogin] in (" & Left(strIN, Len(strIN) - 1) & ")"

I did this before and it worked fine; the solution escapes me now.  Can
anyone see the error? I can't see it.

Regards,
Ryan---
Click to expand...

look in the modules section of www.mvps.org/access

there's an example of using a multi-select listbox to collect query
parameters. Does exactly what you want.
 
In the example here:
http://www.databasedev.co.uk/queries.html
the Row source was:
SELECT DISTINCT tblCompanies.strCompanyCountries FROM tblCompanies UNION
select "All" from tblCompanies;

In my own example, the row Source turned out to be:
SELECT DISTINCT tblLoginID.Login FROM tblLoginID UNION select "All" from
tblLoginID;

For some reason the .str in front of the FieldName was unnecessary. Thus,
in the code, the str was also unnecessary.

Instead of the .str after the WHERE:
strWhere = " WHERE [strCompanyCountries] in (" & Left(strIN, Len(strIN)
- 1) & ")"

I used just the FieldName:
strWhere = " WHERE [Login] in (" & Left(strIN, Len(strIN) - 1) & ")"

Now, everything works great!!

Not sure what cause this issue. I’d appreciate it if someone could educate
me on this topic.


Regards,
Ryan---


--
RyGuy


[email protected] said:
I originally posted my question in the Queries DG, but didn’t get a response
from there. Since the question has a lot of VBA, I think I may get a
response from here. Basically, I am trying to create a Query on the fly,
using variables from a ListBox.

I got the idea, and the code, from this site:http://www.databasedev.co.uk/queries.html

The name of the file that I am working with is:
Using a Microsoft Access Listbox to pass criteria to a query

This is the Row Source in my own ListBox:
SELECT DISTINCT tblLogin.strLogin FROM tblLogin UNION select "All" from
tblLogin;

When I double-click the Form to open it, I get prompted for an input, like
it is some kind of parameter Query, but this is when I open the Form, not the
Query. I think I need to use the UNION and the 'All'. However, when I do, I
only see the word 'All', and nothing else.

I would like the QSL to be something like this:
SELECT tblLogin.Login, *
FROM tblLogin
WHERE (((tblLogin.Login) In ('brooks')));

Below is the code:
Option Compare Database

Private Sub cmdOpenQuery_Click()

On Error GoTo Err_cmdOpenQuery_Click
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant

Set MyDB = CurrentDb()

strSQL = "SELECT * FROM tblLogin"

'Build the IN string by looping through the listbox
For i = 0 To lstLogin.ListCount - 1
If lstLogin.Selected(i) Then
strIN = strIN & "'" & lstLogin.Column(0, i) & "',"
End If
Next i

'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [strLogin] in (" & Left(strIN, Len(strIN) - 1) & ")"

MyDB.QueryDefs.Delete "qryLoginByID"
Set qdef = MyDB.CreateQueryDef("qryLoginByID", strSQL)

'Open the query, built using the IN clause to set the criteria
DoCmd.OpenQuery "qryLoginByID", acViewNormal

'Clear listbox selection after running query
For Each varItem In Me.lstLogin.ItemsSelected
Me.lstLogin.Selected(varItem) = False
Next varItem

Exit_cmdOpenQuery_Click:
Exit Sub

Err_cmdOpenQuery_Click:

If Err.Number = 5 Then
MsgBox "You must make a selection(s) from the list", , "Selection
Required !"
Resume Exit_cmdOpenQuery_Click
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_cmdOpenQuery_Click
End If

End Sub
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click

DoCmd.Close

Exit_cmdClose_Click:
Exit Sub

Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click

End Sub

When I run the Query, all values are returned!!

This is the SQL:
SELECT *
FROM tblLogin;

That's why all variables are returned. What I can't figure out is why the
appropriate string is not passed to the Query...
I think this is the string now, but it seems to be incorrect:
strWhere = " WHERE [strLogin] in (" & Left(strIN, Len(strIN) - 1) & ")"

I did this before and it worked fine; the solution escapes me now. Can
anyone see the error? I can't see it.

Regards,
Ryan---
Click to expand...

look in the modules section of www.mvps.org/access

there's an example of using a multi-select listbox to collect query
parameters. Does exactly what you want.
Click to expand...
 
Hi,

I did not catch it before. Unless strLogin was a Global or Module level
Variable I see no way that using strLogin could work since it is not your
field name and you did not use: Dim strLogin As String.

Does that help?

Best Regards,
Pat

ryguy7272 said:
In the example here:
http://www.databasedev.co.uk/queries.html
the Row source was:
SELECT DISTINCT tblCompanies.strCompanyCountries FROM tblCompanies UNION
select "All" from tblCompanies;

In my own example, the row Source turned out to be:
SELECT DISTINCT tblLoginID.Login FROM tblLoginID UNION select "All" from
tblLoginID;

For some reason the .str in front of the FieldName was unnecessary. Thus,
in the code, the str was also unnecessary.

Instead of the .str after the WHERE:
strWhere = " WHERE [strCompanyCountries] in (" & Left(strIN, Len(strIN)
- 1) & ")"

I used just the FieldName:
strWhere = " WHERE [Login] in (" & Left(strIN, Len(strIN) - 1) & ")"

Now, everything works great!!

Not sure what cause this issue. I’d appreciate it if someone could educate
me on this topic.


Regards,
Ryan---


--
RyGuy


[email protected] said:
I originally posted my question in the Queries DG, but didn’t get a response
from there. Since the question has a lot of VBA, I think I may get a
response from here. Basically, I am trying to create a Query on the fly,
using variables from a ListBox.

I got the idea, and the code, from this site:http://www.databasedev.co.uk/queries.html

The name of the file that I am working with is:
Using a Microsoft Access Listbox to pass criteria to a query

This is the Row Source in my own ListBox:
SELECT DISTINCT tblLogin.strLogin FROM tblLogin UNION select "All" from
tblLogin;

When I double-click the Form to open it, I get prompted for an input, like
it is some kind of parameter Query, but this is when I open the Form, not the
Query. I think I need to use the UNION and the 'All'. However, when I do, I
only see the word 'All', and nothing else.

I would like the QSL to be something like this:
SELECT tblLogin.Login, *
FROM tblLogin
WHERE (((tblLogin.Login) In ('brooks')));

Below is the code:
Option Compare Database

Private Sub cmdOpenQuery_Click()

On Error GoTo Err_cmdOpenQuery_Click
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant

Set MyDB = CurrentDb()

strSQL = "SELECT * FROM tblLogin"

'Build the IN string by looping through the listbox
For i = 0 To lstLogin.ListCount - 1
If lstLogin.Selected(i) Then
strIN = strIN & "'" & lstLogin.Column(0, i) & "',"
End If
Next i

'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [strLogin] in (" & Left(strIN, Len(strIN) - 1) & ")"

MyDB.QueryDefs.Delete "qryLoginByID"
Set qdef = MyDB.CreateQueryDef("qryLoginByID", strSQL)

'Open the query, built using the IN clause to set the criteria
DoCmd.OpenQuery "qryLoginByID", acViewNormal

'Clear listbox selection after running query
For Each varItem In Me.lstLogin.ItemsSelected
Me.lstLogin.Selected(varItem) = False
Next varItem

Exit_cmdOpenQuery_Click:
Exit Sub

Err_cmdOpenQuery_Click:

If Err.Number = 5 Then
MsgBox "You must make a selection(s) from the list", , "Selection
Required !"
Resume Exit_cmdOpenQuery_Click
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_cmdOpenQuery_Click
End If

End Sub
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click

DoCmd.Close

Exit_cmdClose_Click:
Exit Sub

Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click

End Sub

When I run the Query, all values are returned!!

This is the SQL:
SELECT *
FROM tblLogin;

That's why all variables are returned. What I can't figure out is why the
appropriate string is not passed to the Query...
I think this is the string now, but it seems to be incorrect:
strWhere = " WHERE [strLogin] in (" & Left(strIN, Len(strIN) - 1) & ")"

I did this before and it worked fine; the solution escapes me now. Can
anyone see the error? I can't see it.

Regards,
Ryan---
Click to expand...

look in the modules section of www.mvps.org/access

there's an example of using a multi-select listbox to collect query
parameters. Does exactly what you want.
Click to expand...
Click to expand...
 
Back
Top