Trouble with Where clause in DoCmd.OpenReport method Options

[2c00t]

I have a form (frmRecall_Roster) with a button that should run a report (Recall Roster) based on the ID in a text box on the form (frm_Sup_ID). The field on the report is rpt_Sup_ID and should match the field on the form.

When I run the following code I still get prompted for a parameter in rpt_Sup_ID. What am I doing wrong? I'm a complete newbie at coding in VBA though I do have a LITTLE experience in C++. Small words and very specific instructions would be most appreciated. LOL.


By the way, this is in a loop so that eventually I will be able to email the reports (instead of opening in preview mode) to the various supervisors.
___________________________________
Private Sub btn_Single_Report_Click()
On Error GoTo Err_btn_Single_Report_Click


Me.Loop_Counter.Value = Me.Count_Sup.Value


Commence:
If Me.Loop_Counter.Value = 1 Then GoTo Cease Else GoTo Begin


Begin:
Me.Loop_Counter.Value = Me.Loop_Counter.Value - 1


stWhere = "[rpt_Sup_ID] = '" & Me![frm_Sup_ID] & "'"


DoCmd.OpenReport "Recall Roster", acViewPreview, , stWhere
DoCmd.GoToRecord acDataForm, "frm_Recall_Roster", acNext
Me.Refresh
GoTo Commence
Exit Sub


Cease:
MsgBox "Done"
Exit Sub


Exit_btn_Single_Report_Click:
Exit Sub


Err_btn_Single_Report_Click:
MsgBox Err.Description
Resume Exit_btn_Single_Report_Click


End Sub


Thanks in advance for any help.

 

[Golom]

Getting prompted for a parameter usually means that you have specified something in your SQL that the system doesn't recognize as a field name. Check that [rpt_Sup_ID] is a correct field name.

You also need to learn about FOR loops instead of all that GoTo Stuff

Private Sub btn_Single_Report_Click()
    On Error GoTo Err_btn_Single_Report_Click
    Dim n                           As Long

    For n = Me.Count_Sup.Value To 1 Step -1

        stWhere = "[rpt_Sup_ID] = '" & Me![frm_Sup_ID] & "'"

        DoCmd.OpenReport "Recall Roster", acViewPreview, , stWhere
        DoCmd.GoToRecord acDataForm, "frm_Recall_Roster", acNext
        Me.Refresh
    Next

    MsgBox "Done"

Exit_btn_Single_Report_Click:
    Exit Sub

Err_btn_Single_Report_Click:
    MsgBox Err.Description
    Resume Exit_btn_Single_Report_Click

End Sub

 

[JerryKlmns]

Getting prompted for a parameter usually means that you have specified something in your SQL that the system doesn't recognize as a field name.

or your query looks like

PARAMETERS .....
SELECT .....
FROM .....

Switch to SQL view and delete that PARAMETERS line

 

[BSman]

Is you ID field a number? Then you need to lose the single quotes around it, which only apply to a text/string value. So your where statement should be:

stWhere = "[rpt_Sup_ID] = " & Me![frm_Sup_ID]

Bob