Multiple Combo Boxes to setup a report

[djgecko]

I am not very good with VBA but I will try to get all the details in here. I am trying to use 5 combo boxes and 2 text boxes to look up and generate a report based off of those selections. I want it to generate a report as well once I click on the command button. I have the 2 text boxes setup as from and to date boxes that I pulled off of another site. I included attachments to try to clarify this a little bit.

The Date Box jpg is the code that i pulled from another site and I am hoping to incorporate the rest of the code into that button. Please help, I've been trying to figure this out for months.

Private Sub Submit_Click()
 
On Error GoTo Err_Handler      
    Dim strReport As String
    Dim strDateField As String
    Dim strWhere As String
    Dim lngView As Long
    Const strcJetDate = "\#mm\/dd\/yyyy\#"  
 
    strReport = "Search"            
    strDateField = "[Date]"       
    lngView = acViewReport             
 
    If IsDate(Me.startdate) Then strWhere = "(" & strDateField & " >= " & Format(Me.startdate, strcJetDate) & ")"
    End If
    If IsDate(Me.enddate) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strDateField & " < " & Format(Me.enddate + 1, strcJetDate) & ")"
    End If
 
   If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If
 
Debug.Print strWhere  
 
    DoCmd.OpenReport strReport, lngView, , strWhere
 
Exit_Handler:
    Exit Sub
 
Err_Handler:
    If Err.Number <> 2501 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
    End If
    Resume Exit_Handler
 
End Sub

[PhilOfWalton]

Firstly, welcome to Bytes.

Your query should have been posted under Access, not Visual Basic.

Due to the limitations of the Website, images are not all that good, so code should always be pasted between the "[Code/]" Tags

If the image of the form showed only the form, not the surrounding area, it might be readable.

That said, will ALL the Combo boxes and dates be filled in. In other words, if only the dates are filled in, do you want your report to show ALL the data between those two dates?

Phil

[djgecko]

I apologize for the post. Not all of the boxes will be filled in, it will only pull from the filled in boxes and display those records. It is for aircraft records so if the dates are filled in as well as the aircraft number I want it to pull every record for that aircraft between those dates.

[PhilOfWalton]

OK, that clarifies things a bit.

I realise that you are new to Bytes, but it essential that you answer questions as accurately as you can, and respond to requests.

I asked for your code and a clearer image of your form, but got neither. We can't help without the requested info.

Phil

[djgecko]

Hopefully that helps a little bit.

[PhilOfWalton]

Yes, most helpful.

You're om the right track.

All you need to do is check if each Combo box has a value, and if so, add it to the strWhere.

Again you already have the technique of checking whether the strWhere is NOT Null in which case, you add the " AND ", otherwise it starts of with a "WHERE "

Don't forget, if the ComboBox has a string value in it (and I suspect all do other than Document #), then the string needs to be surrounded with quotes.

Something like

" AND Aircraft = " & Chr$(34) & CboAircraft & Chr$(34)
 

Depending on which column is used to hold the name of the Aircraft, the code may have to say

" AND Aircraft = " & Chr$(34) & CboAircraft.Column(1) & Chr$(34)
 

One thing you can do to check how you are progressing, is to create a query, and past the strWhere from your Debug Window into the query's Criteria.

Hope that gets you started.

Phil