Dynamic Query, Using ListBox

[ryguy7272]

I am trying to create a Query on the fly, using variables from a ListBox. I
did this before and it worked fine; the solution escapes me now. 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) & ")"

Can anyone see the error? I can't see it.


Regards,
Ryan---

 

[ryguy7272]

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, liek
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')));

If someone sees the error, please help!!

Regards,
Ryan---


--
RyGuy

I am trying to create a Query on the fly, using variables from a ListBox. I
did this before and it worked fine; the solution escapes me now. 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) & ")"

Can anyone see the error? I can't see it.


Regards,
Ryan---

 

[ryguy7272]

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 someon could educate
me on this topic.


Regards,
Ryan---


--
RyGuy

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, liek
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')));

If someone sees the error, please help!!

Regards,
Ryan---


--
RyGuy

I am trying to create a Query on the fly, using variables from a ListBox. I
did this before and it worked fine; the solution escapes me now. 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) & ")"

Can anyone see the error? I can't see it.


Regards,
Ryan---