Open a report based on query with multiple criteria

[echamorro]

Hi there, I am new in VBA. I created a query from a table that lists several fields. Every time the query is used, it asks the user to enter the part number he/she wants to see and the quantity to print. I created a report from that query and it works fine. Once the user enters that information the report is printed/viewed. Instead of the user enter the information, I created a form with two combo boxes. So the user can select the part number from the combo box and the quantities from the other combo box. I can't connect the values from the combo boxes to the report. the report continues asking for the data. This is what I have so far. I removed the data from the quantity combo box for now. I am using Access 2010.

Private Sub OpenSPKanbanReport_Click()
On Error GoTo Err_OpenSPKanbanReport_Click

    Dim stDocName As String
    Dim strQueryName As String
    Dim strPartNumber As String

    stDocName = "BARCODE SP kanban report w_trigger"
    strQueryPart = [PART_NO] = Forms![frmMain]![cboPartNumber]
strPartNumber = Me.cboPartNumber.Value
    

    DoCmd.OpenReport stDocName, acPreview, "SP kanban query", strQueryPart

Or

DoCmd.OpenReport stDocName, acPreview, , strPartNumber


Exit_OpenSPKanbanReport_Click:
    Exit Sub

Err_OpenSPKanbanReport_Click:
    MsgBox Err.Description
    Resume Exit_OpenSPKanbanReport_Click

[jimatqsi]

Hi echamorro. Welcome to Bytes.com. Please use Code tags when posting code. To do that, just click the [Code/] button and paste your code between the tags.

Try setting the value of strPartnumber like this:

strPartNumber="[Part_No]='" & me.cboPartNumber & "'"

The where string parameter requires the name of the field and the comparison operator to be used. Also, since it is a string, the value must be in quotes. I used single quotes but you can use double quotes if you want. It makes the code a little trickier, in my opinion. However, if a part number can have single quotes within the part number, then you have to take additional action to prevent problems.

Be sure to remove the prompt in the query itself. That will stop the pop-up prompt from happening.

Jim

[echamorro]

Thanks, it worked! Now I have another dilemma I need to use both fields. This is what I have done, but it is not working. Can you please help again?

stDocName = "BARCODE SP kanban report w_trigger"
    strQueryName = "SP kanban query"
    strPartNumber = "[Part_No]='" & Me.cboPartNumber & "'"
    strQuantity = "[Count]='" & Me.cboQuantity.Value & "'"
    DoCmd.OpenReport stDocName, acViewPreview, strQueryName, strPartNumber & "AND" & strQuantity
Or
    strSQL = "[Part_No]='" & Me.cboPartNumber & "'  And [Count]='" & Me.cboQuantity & "'"
    DoCmd.OpenReport stDocName, acViewPreview, strQueryName, strSQL

[echamorro]

Thanks I found what I was doing wrong. First of all, the quantity field is double, not string. I converted the string to double and it worked.
The only thing I can't figured out is to use second option (please see code below) which uses the two field separately instead of a single SQL statement.

this code works.
    strSQL = "[Part_No]='" & Me.cboPartNumber & "'  And [Count]<=" & CDbl(Me.cboQuantity.Value)
    DoCmd.OpenReport stDocName, acViewPreview, strQueryName, strSQL


    'this code doesn't work
    'strPartNumber = "[Part_No]='" & Me.cboPartNumber & "'"
    'strQuantity = "[Count]=" & CDbl(Me.cboQuantity.Value)
    'DoCmd.OpenReport stDocName, acViewPreview, strQueryName, strPartNumber & " AND " & strQuantity

[jimatqsi]

Could it be because you are using <= in the first and = in the second?