Create Report Using Multiple Combo box values 1

[73pixieGirl]

Hello,
I'm not a VB programmer so I'm a bit confused as to what to do. I have an unbound form with 13 combo boxes and 2 text boxes that the user can type in the start date and end date of a specific field. Each combo box's row source is a query that selects distinct values from what's in the table.
I have a button that opens a report (rptHardwareInformation), and the report's record source is a query (qryTest) with fields from the parent and child tables (tblGeneralInfo and tblHardware). The following is some code that's behind the button:

Private Sub cmdPreview_Click()
On Error GoTo Err_cmdPreview_Click
Dim stDocName As String
Dim strFilter As String

stDocName = "rptHardwareInformation"
strFilter = "1=1 "

If Not IsNull(Me!cboAPL.Value) Then
strFilter = strFilter & " And apl = " & Me!cboAPL.Value
End If

If Not IsNull(Me!cboOwner.Value) Then
strFilter = strFilter & " And owner = " & Me!cboOwner.Value
End If

If Not IsNull(Me!cboProjectName.Value) Then
strFilter = strFilter & " And projectName = " & Me!cboProjectName.Value
End If

...etc for the rest of the combo boxes

Docmd.OpenReport stDocName, acPreview, strFilter

End Sub

I've tried to piece together code from past threads. In order to allow the user to select values from multiple combo boxes to filter the report, I think I need code on each combo box on AfterUpdate - I'm just not sure what that code is.

I tried using the "Like" code in the criteria of each field in the query, but there must be too many combo boxes to search on b/c it doesn't work, and then I can't open the query in design mode.

I also am not sure how to search on the start and end dates.

Can someone point me in the right direction? I feel like I'm so close to figuring this out (or maybe that's wishful thinking). :-D

 

[dhookom]

it doesn't work" doesn't tell us much. All of your code expects all fields in the filter to be numeric. If ProjectName is text, you would need to change your code like:

    If Not IsNull(Me!cboProjectName.Value) Then
        strFilter = strFilter & " And projectName = """ & _
            Me!cboProjectName.Value & """ "
    End If

Typical Start Date code would look like:

    If Not IsNull(Me!txtStartDate) Then
        strFilter = strFilter & " And [DateField >= #" & _
            Me!txtStartDate & "# "
    End If

If you have issues, you might want to print the filter to the debug window. You can then press Ctrl+G to open and view the filter.

    Debug.Print strFilter
    Docmd.OpenReport stDocName, acPreview, strFilter

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[73pixieGirl]

Hi dhookom, thank you for responding. When I use the 'Like' criteria and select 2 combo box options, the report doesn't filter based on the two options. That's why I was thinking I needed an 'After Update' event. When I try to open the query in design mode to change it, I get the error 'There isn't enough memory to perform this operation. Close unneeded programs and try again.' I have closed every program and I still can't open the query in design mode. That's why I was trying the VB code.

Will you explain what this code does? It looks like it's concatenating strFilter with "and project name = ''" . Is the '&' the same as a '+'?

If Not IsNull(Me!cboProjectName.Value) Then
strFilter = strFilter & " And projectName = """ & _
Me!cboProjectName.Value & """ "
End If

 

[dhookom]

The ampersand [red]&[/red] is used to join/concatenate text values. The plus sign [red]+[/red] is generally used for math addition. You might find programmers use the plus with text if they want a null value to propogate. For instance
[tt][blue]
"This Text" + null = null
"This Text" & null = "This Text"
[/blue][/tt]

If you use "Like" in a filter/criteria, you can perform partial matches. "Like" is generally used with wildcard characters like "*".

You need to properly delimit values in your filter. If you play around with the query designer and view the SQL, you will notice how strings, numbers, and dates use various delimiters.
[tt][green]
Data Type Delimiter Example
============ ========= =========================
Text/Strings " or ' [LastName] = "Smith"
Numbers (none) [EmployeeID]= 123
Dates # [OrderDate]>=#10/01/2007#
[/green][/tt]


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[73pixieGirl]

Thank you for explaining! Now will you please explain what the strFilter = "1=1 ". I've seen that in a lot of the threads, but I'm not sure what it's supposed to do.

Also, I changed the code using -
If Not IsNull(Me!cboProjectName.Value) Then
strFilter = strFilter & " And projectName = """ & _
Me!cboProjectName.Value & """ "
End If
-for every combo box. When I hit ctrl+G, the output is 1=1 And projectName = "test" but the report is still pulling all of the records in my database. I tried using -
strFilter = "Select * from tblGeneralInfo, tblHardware where "

but then I get "Select * from tblGeneralInfo, tblHardware where And tblGeneralInfo.projectName = "test3" and it still returns all of the records.

What am I doing wrong? TIA!

 

[dhookom]

The "1=1 " has no effect on the filter and allows the building of additional criteria with " And ....". If I didn't start with "1=1 " then I would need to see if the " And " was required yet.

You should not use "SELECT ..." in the strFilter. I did just notice you are missing a comma

Docmd.OpenReport stDocName, acPreview,[b][COLOR=red yellow] , [/color][/b] strFilter

Do you have projects with names test and test3?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[73pixieGirl]

It works!

I do have a test and test3 projectName (very intuitive, huh?)...I was trying out different values when I sent you the code above. Now I'll work on importing the data into the DB!

Thank you so much for your help!!
STAR FOR YOU!