Passing where criteria from form to report doesn't work 2

[annie52]

I'm want to open a report that only shows records based on values I pass from a form. I don't get any errors but my code isn't finding any records when I pass values to the report. I know the record is there.

Private Sub cmdPrintMILSTRIP_Click()
On Error GoTo Err_cmdPrintMILSTRIP_Click

Dim stDocName As String

stDocName = "rptPrintMILSTRIP"
DoCmd.OpenReport stDocName, acPreview, , _
"Reports!rptPrintMILSTRIP.JD = Forms!frmReqnWatch.JD " _
& "And Reports!rptPrintMILSTRIP.Serial = Forms!frmReqnWatch.Serial"

Exit_cmdPrintMILSTRIP_Click:
Exit Sub

Err_cmdPrintMILSTRIP_Click:
If Err.Number = 2001 Or Err.Number = 2501 Then
Else
MsgBox "Error #" & Err.Number & Chr(10) & Chr(13) _
& Err.Description, vbOKOnly, "RPP Tracking System"
End If
Resume Exit_cmdPrintMILSTRIP_Click

End Sub

 

[LonnieJohnson]

Could you be getting the error 2001 or 2501?

ProDev, Builders of Affordable Software Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[annie52]

Hi, LonnieJohnson. Yes, I get error 2501. Here's what I have behind the report NoData event:

On Error GoTo Err_Report_NoData

MsgBox "There are no results to report", vbOKOnly, "RPP Tracking System Search Results"
Cancel = True

Exit_Report_NoData:
Exit Sub

Err_Report_NoData:
MsgBox "Error #" & Err.Number & Chr(10) & Chr(13) _
& Err.Description, vbOKOnly, "RPP Tracking System"
Resume Exit_Report_NoData

I just don't understand why I'm not finding data to show in the report. If I run a parameter query, I can pull up the data in the report. So, I guess I'm referencing the objects incorrectly or something to that effect.

 

[dhookom]

Can we assume the code is running in the form "frmReqnWatch"? If so, try:

Private Sub cmdPrintMILSTRIP_Click()
On Error GoTo Err_cmdPrintMILSTRIP_Click

    Dim stDocName As String
    Dim strWhere as String
    stDocName = "rptPrintMILSTRIP"
    'code assuming JD is numeric and Serial is text/string
    strWhere = "JD = " & Me.JD & " And Serial = """ & Serial & """ "

    DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_cmdPrintMILSTRIP_Click:
    Exit Sub

Err_cmdPrintMILSTRIP_Click:
    If Err.Number = 2001 Or Err.Number = 2501 Then
    Else
        MsgBox "Error #" & Err.Number & Chr(10) & Chr(13) _
            & Err.Description, vbOKOnly, "RPP Tracking System"
    End If
    Resume Exit_cmdPrintMILSTRIP_Click
    
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[annie52]

Hi, dhookom. No, the code runs from a command button on a subform (frmMILSTRIPIssues). The report displays information from the subform and the main form (frmReqnWatch). Specifically, the JD and Serial are on the main form and are both text/strings.

 

[MazeWorX]

Trick to getting the references correct every time

Open the form.
Open a query ... any query
Right click the criteria field
Select "build"
navigate the menu to 'Forms'
>>'Loaded Forms'
Find the control and select it
Paste
The referencee will now be in the window
copy and paste it into your code





Remember amateurs built the ark - professionals built the Titanic

[flush]

 

[dhookom]

Try:

Private Sub cmdPrintMILSTRIP_Click()
On Error GoTo Err_cmdPrintMILSTRIP_Click

    Dim stDocName As String
    Dim strWhere as String
    stDocName = "rptPrintMILSTRIP"
    'code assuming JD is numeric and Serial is text/string
    strWhere = "[JD] = """ & Parent.Form.JD & """ And [Serial] = """ & Parent.Form.Serial & """ "

    DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_cmdPrintMILSTRIP_Click:
    Exit Sub

Err_cmdPrintMILSTRIP_Click:
    If Err.Number = 2001 Or Err.Number = 2501 Then
    Else
        MsgBox "Error #" & Err.Number & Chr(10) & Chr(13) _
            & Err.Description, vbOKOnly, "RPP Tracking System"
    End If
    Resume Exit_cmdPrintMILSTRIP_Click
    
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[annie52]

dhookom -- I did not know I could reference that way (e.g., Parent.Form.Serial). What a great find! My button works perfectly now.

MazeWorX -- Wonderful trick!

Thank you both so much. Stars to both of you for starting my day with new knowledge!!