List Box Query

[thingssocomplex]

I have a list box that allows multi select, when I enter in the criteria of my query [forms]![Control]![lstData] it comes back with "engine does recognize forms!Control!lstData as a valid field name expression" can anybody help me with this?

 

[mdow]

I do this kind of thing quite often. I have a form that allows users to select Clients, Campaigns, and Date range to filter a report. You can use the same logic even if your query is not used in a report.

The basic idea is to change the SQL where condition to include all selected items. For example, if client A and B are selected, the sql where clause would be: where client=’A’ or client=’B’. I use 2 queries for this. For example, qryMyQuery-SQL and qryMyQuery-Rpt. The first query would be my stable query that contains only a Where clause for date. The sql from this query is copied, modified and then used to change the sql in my report query. Also, I have 3 labels in the report header that list the date range and each selected item or <All> if no items were selected from the list box.


Private Sub Report_Open(Cancel As Integer)

On Error GoTo Bungled

Dim strTitle As String: strTitle = "Your Title"
Dim db As Database
Dim rs As Recordset
Dim strCampaign, strCampLbl, strClient, strClientLbl, strSQL As String
Dim ctl As Control
Dim varItem As Variant
Dim qd As QueryDef

Set db = CurrentDb()

DoCmd.OpenForm "frmReportCriteria", , , , , acDialog, "Same Date"

If Not IsLoaded("frmReportCriteria") Then
Cancel = True
End If

Set qd = CurrentDb.QueryDefs("ClientNetOfPOD-SQL")
strSQL = qd.SQL
qd.Close
strSQL = Left(strSQL, Len(strSQL) - 3) 'remove semi

Set ctl = Forms!frmReportCriteria.lstClient

If ctl.ItemsSelected.Count > 0 Then
strClient = " And (tblRevenue.Client ='"
If ctl.ItemsSelected.Count > 1 Then
strClientLbl = "Clients: "
Else: strClientLbl = "Client: "
End If
For Each varItem In ctl.ItemsSelected
strClient = strClient & ctl.ItemData(varItem) & "' OR tblRevenue.Client ='"
strClientLbl = strClientLbl & ctl.ItemData(varItem) & ", "
Next varItem
strClient = Left(strClient, Len(strClient) - 24) & ")"
Me.lblClient.Caption = Left(strClientLbl, Len(strClientLbl) - 2)
Else: Me.lblClient.Caption = "Clients: <All>"
End If
Set ctl = Forms!frmReportCriteria.lstCampaign

If ctl.ItemsSelected.Count > 0 Then
strCampaign = " And (tblRevenue.[Program Name] ='"
If ctl.ItemsSelected.Count > 1 Then
strCampLbl = "Campaigns: "
Else: strCampLbl = "Campaign: "
End If
For Each varItem In ctl.ItemsSelected
strCampaign = strCampaign & ctl.ItemData(varItem) & "' OR tblRevenue.[Program Name] ='"
strCampLbl = strCampLbl & ctl.ItemData(varItem) & ", "
Next varItem
strCampaign = Left(strCampaign, Len(strCampaign) - 32) & ")"
Me.lblCampaign.Caption = Left(strCampLbl, Len(strCampLbl) - 2)
Else: Me.lblCampaign.Caption = "Campaigns: <All>"
End If

Set qd = CurrentDb.QueryDefs("ClientNetOfPOD-Rpt")

qd.SQL = strSQL & strClient & strCampaign
qd.Close

Me.lblReportDte.Caption = "For Monday Week " & Forms!frmReportCriteria.BeginDate

Set ctl = Nothing

Abscond:
Exit Sub

Bungled:

DoCmd.Hourglass False

If Err.Number = 2450 Then
Cancel = True
Resume Abscond
Else
MsgBox Err.Number & ": " & Err.Description, vbCritical, strTitle
Resume Abscond
End If

End Sub

 

[boblarson]

thingssocomplex:

What is this form? Is it a subform or just a normal form? If a subform then you need to refer to the right thing, which you wouldn't be by using that code. (Also CONTROL is an Access Reserved Word so you shouldn't have that as a field or object name)

You can see a quick, and easy, way to get the right control reference for your query here:
http://www.btabdevelopment.com/ts/refer2sfrms


@mdow:
Your code is similar to what I do with many search forms with some differences. It isn't always necessary to change the underlying query. In fact, I've found it kind of rare to have to modify the query and just open the report using the Where Condition. But I can see why you might do it this way because of having the same search form for multiple reports but with different field names that would need the same datatype for searching. I think we could clean up your code a little (and it would be easier to read if you had used code tags here - which you should do if you are posting anything over a few lines).

 

[jdraw]

As a side issue, this is not doing what you think.

Dim strCampaign, strCampLbl, strClient, strClientLbl, strSQL As String

These variables
strCampaign, strCampLbl, strClient, strClientLbl will be DIMmed as variant - NOT String.

You must be explicit with Dim, otherwise the default is Variant.

You can say
Dim strCampaign as string, strCampLbl as string, strClient as string
Dim strClientLbl as string
Dim strSQL As String

 

[boblarson]

As a side issue, this is not doing what you think.



These variables
strCampaign, strCampLbl, strClient, strClientLbl will be DIMmed as variant - NOT String.

You must be explicit with Dim, otherwise the default is Variant.

You can say
Dim strCampaign as string, strCampLbl as string, strClient as string
Dim strClientLbl as string
Dim strSQL As String

Good point. I had missed that was in there. It isn't like VB6 where you could do that type of declaration. It is true that, in VBA, it requires the individual datatypes. Good catch.