List Box linked to Query

G

Guest

I am using a list box to identify the criteria for a query. Please help:

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 strIO As String
Dim strIP As String
Dim strIQ As String
Dim strIR As String
Dim strIS As String
Dim flgSelectAll As Boolean
Dim varItem As Variant
dtDate1 = Forms!Activity_View_frm!Date1
dtDate2 = Forms!Activity_View_frm!Date2





Set MyDB = CurrentDb()

strSQL = "SELECT * FROM Activity_tbl"

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

'Build the IN string by looping through the listbox
For i = 0 To ZoneList.ListCount - 1
If ZoneList.Selected(i) Then
If ZoneList.Column(0, i) = "*All" Then
flgSelectAll = True
End If
strIO = strIO & "'" & ZoneList.Column(0, i) & "',"
End If
Next i

'Build the IN string by looping through the listbox
For i = 0 To AccountTypeList.ListCount - 1
If AccountTypeList.Selected(i) Then
If AccountTypeList.Column(0, i) = "*All" Then
flgSelectAll = True
End If
strIP = strIP & "'" & AccountTypeList.Column(0, i) & "',"
End If
Next i


'Build the IN string by looping through the listbox
For i = 0 To DepartmentList.ListCount - 1
If DepartmentList.Selected(i) Then
If DepartmentList.Column(0, i) = "*All Departments" Then
flgSelectAll = True
End If
strIQ = strIQ & "'" & DepartmentList.Column(0, i) & "',"
End If
Next i


'Build the IN string by looping through the listbox
For i = 0 To SalesPitchList.ListCount - 1
If SalesPitchList.Selected(i) Then
If SalesPitchList.Column(0, i) = "*All Sales Pitches" Then
flgSelectAll = True
End If
strIR = strIR & "'" & SalesPitchList.Column(0, i) & "',"
End If
Next i


'Build the IN string by looping through the listbox
For i = 0 To ActivityList.ListCount - 1
If ActivityList.Selected(i) Then
If ActivityList.Column(0, i) = "*All Activities" Then
flgSelectAll = True
End If
strIS = strIS & "'" & ActivityList.Column(0, i) & "',"
End If
Next i


'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [Customer Name] in (" & Left(strIN, Len(strIN) - 1) &
") and [Zone] in (" & Left(strIO, Len(strIO) - 1) & ") and [Account Type] in
(" & Left(strIP, Len(strIP) - 1) & ") and [Department] in (" & Left(strIQ,
Len(strIQ) - 1) & ") and [Business Type] in (" & Left(strIR, Len(strIR) - 1)
& ") and [Activity] in (" & Left(strIS, Len(strIS) - 1) & ") and [Date]
Between #" & dtDate1 & "# And #" & dtDate2 & "#"



'If "*All Customers" was selected in the listbox, don't add the WHERE
condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If

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

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

'Clear listbox selection after running query
For Each varItem In Me.CustomerList.ItemsSelected
Me.CustomerList.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
 
D

Douglas J. Steele

It would help if you identified the problem you're having...

A couple of things jump out at me.

You're going to run into problems if you haven't selected anything from one
or more of the listboxes: Left(strIN, Len(strIN) - 1) is going to give you
an error.

As well, you're using the same flgSelectAll variable for all of the
listboxes: the user might have specified values in one (or more) of the
other listboxes.

Finally, you're ignoring the date comparison when flgSelectAll is true.

Any of those what you need help with?


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


JJ said:
I am using a list box to identify the criteria for a query. Please help:

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 strIO As String
Dim strIP As String
Dim strIQ As String
Dim strIR As String
Dim strIS As String
Dim flgSelectAll As Boolean
Dim varItem As Variant
dtDate1 = Forms!Activity_View_frm!Date1
dtDate2 = Forms!Activity_View_frm!Date2





Set MyDB = CurrentDb()

strSQL = "SELECT * FROM Activity_tbl"

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

'Build the IN string by looping through the listbox
For i = 0 To ZoneList.ListCount - 1
If ZoneList.Selected(i) Then
If ZoneList.Column(0, i) = "*All" Then
flgSelectAll = True
End If
strIO = strIO & "'" & ZoneList.Column(0, i) & "',"
End If
Next i

'Build the IN string by looping through the listbox
For i = 0 To AccountTypeList.ListCount - 1
If AccountTypeList.Selected(i) Then
If AccountTypeList.Column(0, i) = "*All" Then
flgSelectAll = True
End If
strIP = strIP & "'" & AccountTypeList.Column(0, i) & "',"
End If
Next i


'Build the IN string by looping through the listbox
For i = 0 To DepartmentList.ListCount - 1
If DepartmentList.Selected(i) Then
If DepartmentList.Column(0, i) = "*All Departments" Then
flgSelectAll = True
End If
strIQ = strIQ & "'" & DepartmentList.Column(0, i) & "',"
End If
Next i


'Build the IN string by looping through the listbox
For i = 0 To SalesPitchList.ListCount - 1
If SalesPitchList.Selected(i) Then
If SalesPitchList.Column(0, i) = "*All Sales Pitches" Then
flgSelectAll = True
End If
strIR = strIR & "'" & SalesPitchList.Column(0, i) & "',"
End If
Next i


'Build the IN string by looping through the listbox
For i = 0 To ActivityList.ListCount - 1
If ActivityList.Selected(i) Then
If ActivityList.Column(0, i) = "*All Activities" Then
flgSelectAll = True
End If
strIS = strIS & "'" & ActivityList.Column(0, i) & "',"
End If
Next i


'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [Customer Name] in (" & Left(strIN, Len(strIN) - 1)
&
") and [Zone] in (" & Left(strIO, Len(strIO) - 1) & ") and [Account Type]
in
(" & Left(strIP, Len(strIP) - 1) & ") and [Department] in (" & Left(strIQ,
Len(strIQ) - 1) & ") and [Business Type] in (" & Left(strIR, Len(strIR) -
1)
& ") and [Activity] in (" & Left(strIS, Len(strIS) - 1) & ") and [Date]
Between #" & dtDate1 & "# And #" & dtDate2 & "#"



'If "*All Customers" was selected in the listbox, don't add the WHERE
condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If

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

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

'Clear listbox selection after running query
For Each varItem In Me.CustomerList.ItemsSelected
Me.CustomerList.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
Click to expand...
 
G

Guest

I'm having trouble specifically with the Where Clause. I'm getting the error:
"Object Required"

I think I have the Selection problem covered with the error message.
Basically the idea is to use several list boxes to define several critera for
a query.

Douglas J. Steele said:
It would help if you identified the problem you're having...

A couple of things jump out at me.

You're going to run into problems if you haven't selected anything from one
or more of the listboxes: Left(strIN, Len(strIN) - 1) is going to give you
an error.

As well, you're using the same flgSelectAll variable for all of the
listboxes: the user might have specified values in one (or more) of the
other listboxes.

Finally, you're ignoring the date comparison when flgSelectAll is true.

Any of those what you need help with?


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


JJ said:
I am using a list box to identify the criteria for a query. Please help:

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 strIO As String
Dim strIP As String
Dim strIQ As String
Dim strIR As String
Dim strIS As String
Dim flgSelectAll As Boolean
Dim varItem As Variant
dtDate1 = Forms!Activity_View_frm!Date1
dtDate2 = Forms!Activity_View_frm!Date2





Set MyDB = CurrentDb()

strSQL = "SELECT * FROM Activity_tbl"

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

'Build the IN string by looping through the listbox
For i = 0 To ZoneList.ListCount - 1
If ZoneList.Selected(i) Then
If ZoneList.Column(0, i) = "*All" Then
flgSelectAll = True
End If
strIO = strIO & "'" & ZoneList.Column(0, i) & "',"
End If
Next i

'Build the IN string by looping through the listbox
For i = 0 To AccountTypeList.ListCount - 1
If AccountTypeList.Selected(i) Then
If AccountTypeList.Column(0, i) = "*All" Then
flgSelectAll = True
End If
strIP = strIP & "'" & AccountTypeList.Column(0, i) & "',"
End If
Next i


'Build the IN string by looping through the listbox
For i = 0 To DepartmentList.ListCount - 1
If DepartmentList.Selected(i) Then
If DepartmentList.Column(0, i) = "*All Departments" Then
flgSelectAll = True
End If
strIQ = strIQ & "'" & DepartmentList.Column(0, i) & "',"
End If
Next i


'Build the IN string by looping through the listbox
For i = 0 To SalesPitchList.ListCount - 1
If SalesPitchList.Selected(i) Then
If SalesPitchList.Column(0, i) = "*All Sales Pitches" Then
flgSelectAll = True
End If
strIR = strIR & "'" & SalesPitchList.Column(0, i) & "',"
End If
Next i


'Build the IN string by looping through the listbox
For i = 0 To ActivityList.ListCount - 1
If ActivityList.Selected(i) Then
If ActivityList.Column(0, i) = "*All Activities" Then
flgSelectAll = True
End If
strIS = strIS & "'" & ActivityList.Column(0, i) & "',"
End If
Next i


'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [Customer Name] in (" & Left(strIN, Len(strIN) - 1)
&
") and [Zone] in (" & Left(strIO, Len(strIO) - 1) & ") and [Account Type]
in
(" & Left(strIP, Len(strIP) - 1) & ") and [Department] in (" & Left(strIQ,
Len(strIQ) - 1) & ") and [Business Type] in (" & Left(strIR, Len(strIR) -
1)
& ") and [Activity] in (" & Left(strIS, Len(strIS) - 1) & ") and [Date]
Between #" & dtDate1 & "# And #" & dtDate2 & "#"



'If "*All Customers" was selected in the listbox, don't add the WHERE
condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If

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

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

'Clear listbox selection after running query
For Each varItem In Me.CustomerList.ItemsSelected
Me.CustomerList.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
Click to expand...
Click to expand...
 
D

Douglas J. Steele

I realize your intent is to use several list boxes to define several
criteria for a query.

However, as I pointed out, you haven't included code to handle the case
where nothing is selected in a particular listbox, and if the user selects
the "*All" entry in any one of the listboxes, your code assumes they've
selected "*All" in all of them.

What's the value of strWhere when you get an error?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


JJ said:
I'm having trouble specifically with the Where Clause. I'm getting the
error:
"Object Required"

I think I have the Selection problem covered with the error message.
Basically the idea is to use several list boxes to define several critera
for
a query.

Douglas J. Steele said:
It would help if you identified the problem you're having...

A couple of things jump out at me.

You're going to run into problems if you haven't selected anything from
one
or more of the listboxes: Left(strIN, Len(strIN) - 1) is going to give
you
an error.

As well, you're using the same flgSelectAll variable for all of the
listboxes: the user might have specified values in one (or more) of the
other listboxes.

Finally, you're ignoring the date comparison when flgSelectAll is true.

Any of those what you need help with?


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


JJ said:
I am using a list box to identify the criteria for a query. Please help:

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 strIO As String
Dim strIP As String
Dim strIQ As String
Dim strIR As String
Dim strIS As String
Dim flgSelectAll As Boolean
Dim varItem As Variant
dtDate1 = Forms!Activity_View_frm!Date1
dtDate2 = Forms!Activity_View_frm!Date2





Set MyDB = CurrentDb()

strSQL = "SELECT * FROM Activity_tbl"

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

'Build the IN string by looping through the listbox
For i = 0 To ZoneList.ListCount - 1
If ZoneList.Selected(i) Then
If ZoneList.Column(0, i) = "*All" Then
flgSelectAll = True
End If
strIO = strIO & "'" & ZoneList.Column(0, i) & "',"
End If
Next i

'Build the IN string by looping through the listbox
For i = 0 To AccountTypeList.ListCount - 1
If AccountTypeList.Selected(i) Then
If AccountTypeList.Column(0, i) = "*All" Then
flgSelectAll = True
End If
strIP = strIP & "'" & AccountTypeList.Column(0, i) & "',"
End If
Next i


'Build the IN string by looping through the listbox
For i = 0 To DepartmentList.ListCount - 1
If DepartmentList.Selected(i) Then
If DepartmentList.Column(0, i) = "*All Departments" Then
flgSelectAll = True
End If
strIQ = strIQ & "'" & DepartmentList.Column(0, i) & "',"
End If
Next i


'Build the IN string by looping through the listbox
For i = 0 To SalesPitchList.ListCount - 1
If SalesPitchList.Selected(i) Then
If SalesPitchList.Column(0, i) = "*All Sales Pitches" Then
flgSelectAll = True
End If
strIR = strIR & "'" & SalesPitchList.Column(0, i) & "',"
End If
Next i


'Build the IN string by looping through the listbox
For i = 0 To ActivityList.ListCount - 1
If ActivityList.Selected(i) Then
If ActivityList.Column(0, i) = "*All Activities" Then
flgSelectAll = True
End If
strIS = strIS & "'" & ActivityList.Column(0, i) & "',"
End If
Next i


'Create the WHERE string, and strip off the last comma of the IN
string
strWhere = " WHERE [Customer Name] in (" & Left(strIN, Len(strIN) -
1)
&
") and [Zone] in (" & Left(strIO, Len(strIO) - 1) & ") and [Account
Type]
in
(" & Left(strIP, Len(strIP) - 1) & ") and [Department] in (" &
Left(strIQ,
Len(strIQ) - 1) & ") and [Business Type] in (" & Left(strIR,
Len(strIR) -
1)
& ") and [Activity] in (" & Left(strIS, Len(strIS) - 1) & ") and [Date]
Between #" & dtDate1 & "# And #" & dtDate2 & "#"



'If "*All Customers" was selected in the listbox, don't add the
WHERE
condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If

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

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

'Clear listbox selection after running query
For Each varItem In Me.CustomerList.ItemsSelected
Me.CustomerList.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
Click to expand...
Click to expand...
Click to expand...
 
A

Andy

Pardon the intrusion,

Just thought I might offer an alternative approach. I too use list boxes
with multiselect set, and then run a query based on their selections.
But I let the query call a module to validate the setting in the list box.

I would write a function say checkZone like this (air code)

function checkZone(zone as string) as boolean
dim ctl as control
set ctl =forma![form name]!Zonelist
checkZone = falseend function


I would create an expression in my query checkZone([Zone]) and set
criteria to True.


Andy
I'm having trouble specifically with the Where Clause. I'm getting the error:
"Object Required"

I think I have the Selection problem covered with the error message.
Basically the idea is to use several list boxes to define several critera for
a query.

Douglas J. Steele said:
It would help if you identified the problem you're having...

A couple of things jump out at me.

You're going to run into problems if you haven't selected anything from one
or more of the listboxes: Left(strIN, Len(strIN) - 1) is going to give you
an error.

As well, you're using the same flgSelectAll variable for all of the
listboxes: the user might have specified values in one (or more) of the
other listboxes.

Finally, you're ignoring the date comparison when flgSelectAll is true.

Any of those what you need help with?


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


JJ said:
I am using a list box to identify the criteria for a query. Please help:

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 strIO As String
Dim strIP As String
Dim strIQ As String
Dim strIR As String
Dim strIS As String
Dim flgSelectAll As Boolean
Dim varItem As Variant
dtDate1 = Forms!Activity_View_frm!Date1
dtDate2 = Forms!Activity_View_frm!Date2





Set MyDB = CurrentDb()

strSQL = "SELECT * FROM Activity_tbl"

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

'Build the IN string by looping through the listbox
For i = 0 To ZoneList.ListCount - 1
If ZoneList.Selected(i) Then
If ZoneList.Column(0, i) = "*All" Then
flgSelectAll = True
End If
strIO = strIO & "'" & ZoneList.Column(0, i) & "',"
End If
Next i

'Build the IN string by looping through the listbox
For i = 0 To AccountTypeList.ListCount - 1
If AccountTypeList.Selected(i) Then
If AccountTypeList.Column(0, i) = "*All" Then
flgSelectAll = True
End If
strIP = strIP & "'" & AccountTypeList.Column(0, i) & "',"
End If
Next i


'Build the IN string by looping through the listbox
For i = 0 To DepartmentList.ListCount - 1
If DepartmentList.Selected(i) Then
If DepartmentList.Column(0, i) = "*All Departments" Then
flgSelectAll = True
End If
strIQ = strIQ & "'" & DepartmentList.Column(0, i) & "',"
End If
Next i


'Build the IN string by looping through the listbox
For i = 0 To SalesPitchList.ListCount - 1
If SalesPitchList.Selected(i) Then
If SalesPitchList.Column(0, i) = "*All Sales Pitches" Then
flgSelectAll = True
End If
strIR = strIR & "'" & SalesPitchList.Column(0, i) & "',"
End If
Next i


'Build the IN string by looping through the listbox
For i = 0 To ActivityList.ListCount - 1
If ActivityList.Selected(i) Then
If ActivityList.Column(0, i) = "*All Activities" Then
flgSelectAll = True
End If
strIS = strIS & "'" & ActivityList.Column(0, i) & "',"
End If
Next i


'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [Customer Name] in (" & Left(strIN, Len(strIN) - 1)
&
") and [Zone] in (" & Left(strIO, Len(strIO) - 1) & ") and [Account Type]
in
(" & Left(strIP, Len(strIP) - 1) & ") and [Department] in (" & Left(strIQ,
Len(strIQ) - 1) & ") and [Business Type] in (" & Left(strIR, Len(strIR) -
1)
& ") and [Activity] in (" & Left(strIS, Len(strIS) - 1) & ") and [Date]
Between #" & dtDate1 & "# And #" & dtDate2 & "#"



'If "*All Customers" was selected in the listbox, don't add the WHERE
condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If

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

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

'Clear listbox selection after running query
For Each varItem In Me.CustomerList.ItemsSelected
Me.CustomerList.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
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

I don't see the point in looping through the listbox entries more than once
(which is what you seem to be suggesting).

Also, something I didn't point out earlier, there's no need to look at the
Selected property for every item in the listbox: there's an ItemsSelected
collection that lets you examine only those rows that are selected:

Dim varSelected As Variant

For Each varSelected In CustomerList.ItemsSelected
If CustomerList.Column(0, varSelected) = "*All Customers" Then
flgSelectAll = True
Exit For
End If
strIN = strIN & "'" & CustomerList.Column(0, varSelected) & "',"
Next varSelected


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Andy said:
Pardon the intrusion,

Just thought I might offer an alternative approach. I too use list boxes
with multiselect set, and then run a query based on their selections. But
I let the query call a module to validate the setting in the list box.

I would write a function say checkZone like this (air code)

function checkZone(zone as string) as boolean
dim ctl as control
set ctl =forma![form name]!Zonelist
checkZone = falseend function


I would create an expression in my query checkZone([Zone]) and set
criteria to True.


Andy
I'm having trouble specifically with the Where Clause. I'm getting the
error: "Object Required"

I think I have the Selection problem covered with the error message.
Basically the idea is to use several list boxes to define several critera
for a query.

Douglas J. Steele said:
It would help if you identified the problem you're having...

A couple of things jump out at me.

You're going to run into problems if you haven't selected anything from
one or more of the listboxes: Left(strIN, Len(strIN) - 1) is going to
give you an error.

As well, you're using the same flgSelectAll variable for all of the
listboxes: the user might have specified values in one (or more) of the
other listboxes.

Finally, you're ignoring the date comparison when flgSelectAll is true.

Any of those what you need help with?


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I am using a list box to identify the criteria for a query. Please
help:

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 strIO As String
Dim strIP As String
Dim strIQ As String
Dim strIR As String
Dim strIS As String
Dim flgSelectAll As Boolean
Dim varItem As Variant
dtDate1 = Forms!Activity_View_frm!Date1
dtDate2 = Forms!Activity_View_frm!Date2





Set MyDB = CurrentDb()

strSQL = "SELECT * FROM Activity_tbl"

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

'Build the IN string by looping through the listbox
For i = 0 To ZoneList.ListCount - 1
If ZoneList.Selected(i) Then
If ZoneList.Column(0, i) = "*All" Then
flgSelectAll = True
End If
strIO = strIO & "'" & ZoneList.Column(0, i) & "',"
End If
Next i

'Build the IN string by looping through the listbox
For i = 0 To AccountTypeList.ListCount - 1
If AccountTypeList.Selected(i) Then
If AccountTypeList.Column(0, i) = "*All" Then
flgSelectAll = True
End If
strIP = strIP & "'" & AccountTypeList.Column(0, i) & "',"
End If
Next i


'Build the IN string by looping through the listbox
For i = 0 To DepartmentList.ListCount - 1
If DepartmentList.Selected(i) Then
If DepartmentList.Column(0, i) = "*All Departments" Then
flgSelectAll = True
End If
strIQ = strIQ & "'" & DepartmentList.Column(0, i) & "',"
End If
Next i


'Build the IN string by looping through the listbox
For i = 0 To SalesPitchList.ListCount - 1
If SalesPitchList.Selected(i) Then
If SalesPitchList.Column(0, i) = "*All Sales Pitches" Then
flgSelectAll = True
End If
strIR = strIR & "'" & SalesPitchList.Column(0, i) & "',"
End If
Next i


'Build the IN string by looping through the listbox
For i = 0 To ActivityList.ListCount - 1
If ActivityList.Selected(i) Then
If ActivityList.Column(0, i) = "*All Activities" Then
flgSelectAll = True
End If
strIS = strIS & "'" & ActivityList.Column(0, i) & "',"
End If
Next i


'Create the WHERE string, and strip off the last comma of the IN
string
strWhere = " WHERE [Customer Name] in (" & Left(strIN, Len(strIN) -
1) &
") and [Zone] in (" & Left(strIO, Len(strIO) - 1) & ") and [Account
Type] in
(" & Left(strIP, Len(strIP) - 1) & ") and [Department] in (" &
Left(strIQ,
Len(strIQ) - 1) & ") and [Business Type] in (" & Left(strIR,
Len(strIR) - 1)
& ") and [Activity] in (" & Left(strIS, Len(strIS) - 1) & ") and [Date]
Between #" & dtDate1 & "# And #" & dtDate2 & "#"



'If "*All Customers" was selected in the listbox, don't add the
WHERE
condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If

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

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

'Clear listbox selection after running query
For Each varItem In Me.CustomerList.ItemsSelected
Me.CustomerList.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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dynamic Query, Using ListBox and VBA 4
Query Using List Boxes 2
Linking Multiple list boxes to create a query. 4
multi-select list box query 1
Query using 3 listboxes 1
Dynamic Query, Using ListBox 2
Code for List Boxes 17
how to use list box contents as query criterion 2

Top