one command to print reports based on field value

[ancat2012]

Hello, I have searched all over and did not see any threads on this.

I have 1 table called tbl_Inventory with fields: Address(text), Type(text), Customer (text), Select (yes/no)

I have a main form with combo box where user can select customer and the subform will display a list of addresses for that selected customer. Subform displays all fields in continuous format. Select field is a column with check boxes that user can select addresses they want to print. The field Type has one of 3 value: SFR, Condo, and Multi. So the subform looks something like this.

123 Lincoln SFR John Doe (checkbox)
321 Broadway Condo John Doe (checkbox)
.
.
etc.

3 reports: SFR, Condo, and Multi

Now I want one command button to print all selected addresses with corresponding reports based on Type field.

for example, if selected record is a SFR, then print SFR report for that record
if selected record is a Condo, then print Condo report, etc.

The code below print out all selected records on same report type.

Please help!

[FONT=&quot]Select Case Me![Type][/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] Case "SFR"[/FONT]
[FONT=&quot] strDocName = "SFR"[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] Case "CONDO"[/FONT]
[FONT=&quot] strDocName = "CONDO"[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] Case "MULTI"[/FONT]
[FONT=&quot] strDocName = "MULTI"[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]End Select[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]DoCmd.OpenReport strDocName, acViewNormal, , "Select = yes", acWindowNormal[/FONT]

 

[ancat2012]

I use the following codes and it prints out the reports I want but the reports do not have any data from tbl_Selected Assets. The reports are bound to tbl_Selected Assets with filter to select the records with the corresponding Type. When I open the reports outside of this code, they have the correct data loaded.

What did I missed? thanks

Private Sub Command59_Click()


Dim rs As DAO.Recordset
Dim strDocName As String
If Me.Dirty Then Me.Dirty = False

Set rs = CurrentDb.OpenRecordset("tbl_SelectedAssets")

Do While Not rs.EOF

If rs![InventoryType] = "HELOC" Then
strDocName = "HELOC"

ElseIf rs![InventoryType] = "TAD" Then
strDocName = "TAD"

ElseIf rs![InventoryType] = "REGULAR" Then
strDocName = "REGULAR"

ElseIf rs![InventoryType] = "CCRP" Then
strDocName = "CCRP"

End If

DoCmd.OpenReport strDocName, acPreview, , rs!Route = yes, acWindowNormal

rs.MoveNext
Loop
rs.Close


End Sub

 

[boblarson]

Looks like your Where Condition is off. Is Route a boolean field? If not then change what I have below to put single quotes around the word YES. I have also used a Select Case instead of the ElseIF's.

Private Sub Command59_Click()
 
    Dim rs As DAO.Recordset
    Dim strDocName As String
    Dim blnError As Boolean
    If Me.Dirty Then Me.Dirty = False
    Set rs = CurrentDb.OpenRecordset("tbl_SelectedAssets")
    Do While Not rs.EOF
        Select Case rs![InventoryType]
        Case "HELOC"
            strDocName = "HELOC"
        Case "TAD"
            strDocName = "TAD"
        Case "REGULAR"
            strDocName = "REGULAR"
        Case "CCRP"
            strDocName = "CCRP"
        Case Else
            MsgBox "An Inventory Type that is not supported in this code has ocurred." & vbCrLf & _
                   "Please contact your database administrator", vbCritical, "Error"
            blnError = True
        End Select
 
        If Not blnError Then
            DoCmd.OpenReport strDocName, acPreview, , [B][COLOR=red]"[Route]= Yes"[/COLOR][/B]
        End If
            rs.MoveNext
        Loop
        rs.Close
 
    End Sub