Conditional reporting based on value of a field in a selected record

[Eddie]

I have the following code that I need help modifying:

Private Sub ChargeReport_Click()
On Error GoTo Err_ChargeReport_Click
    Dim stDocName As String
    stDocName = "Charges_Report"
    DoCmd.SendObject acReport, stDocName, acFormatPDF, , , , "Charge Sheet"
Exit_ChargeReport_Click:
    Exit Sub
Err_ChargeReport_Click:
    MsgBox Err.Description
    Resume Exit_ChargeReport_Click
 
End Sub

Currently, this code opens an input box that accepts the ID number for a particular record. Then it attaches a specific report for the selected record to an email. It works fine for this purpose.

I want to modify it so that it attaches one of several different reports depending upon the value of a [Staff_ID] field in the selected record.

I've tried a number of different solutions using an InputBox to get the record ID along with an If/ElseIf/Else construct that evaluates the [Staff_ID] field in order to determine which report to attach to the email, but I cannot find my error.

Any help would be appreciated.

 

[mh123]

If I may, how does the value of Staff_ID determine which reports to send or well how do you want it to determine which reports to send?

 

[Eddie]

Staff_ID is a lookup field that references a table with a list of staff names indexed by an ID #. There are several "charge reports"--each of which is unique to a particular staff person. So when the button is clicked, I want Access to prompt the user for the record ID#, find that record, determine the ID# of the staff who entered that record (contained in [Staff_ID]), and then output the report that corresponds to that staff person's ID#. I've tried to get at it thus:

Dim stDocName as String
If [Staff_ID] = 1 Then
     stDocName = "Charges_Report_1"
ElseIf [Staff_ID] = 2 Then
     stDocName = "Charges_Report_2"
ElseIf [Staff_ID] = [I]n[/I] Then
     stDocName = "Charges_Report_[I]n[/I]"
Else
     stDocName = "Charges_Report_Blank"
EndIf

 

[mh123]

Could you get the input from the textbox from the user, then use a DLookup to your table/query using the input ID (what the user inputs to txtbox) as a filter to get the Staff ID? Something like this (off the top of my head I may be off by an inch or a mile)

Dim StDocName as String
Dim RecordID as String
Dim StaffID as String
RecordID = 'however you get it from the textboxbla
StaffID = (DLookup("Staff_ID", "tableorquerynamehere", "RecordID = " & RecordID & "")) ' This should give you the Staff ID for whoever entered the RecordID for the value input into your textbox thing

StDocName = Charges_Report_" & StaffID & "

I hope this helps you on your way to get there but let me know how you get on!

 

[Eddie]

DLookup was just what I needed. Solved the problem for me. Thanks!