Opening only populated reports

[charlie442]

Hi

I am having a little trouble with my code here. I have a button on a form which opens reports on a particular Case ID. the button pulls up an input box asking the user to specify the Case ID to open the reports for. After that It should always print "Rpt_Profiling_Cases", then it should open any of the profiles (CIT, VAT, PAYE or Customs) for which this Case ID is present in any of the CIT, VAT, PAYE or Customs Profile Tables respectively. And lastly it should always open "Rpt_Case_Historical_Comments" .

I have tried writing this using if statements but it is not working.

Any assistance greatly appreciated (code below)

Charlie

Private Sub Command24_Click()
'This function allows the user to open the risk profile form to a specific case
Dim stSql As String
Dim Temp3 As String
Dim Answer3 As String
Dim Answer4 As String
Dim Answer5 As String
Dim Answer6 As String

'InputBox prompts user to enter Case ID for the form they wish to open.
Temp3 = InputBox("Enter Case ID", "Search for Case ID", "Case ID")
If Temp3 = "" Or Temp3 = "0" Then        'redirect to main switchboard if cancel is pressed or no value is entered on input box
      DoCmd.OpenForm "Frm_Main_Switchboard"
Else
      Answer3 = Nz(DLookup("Case_ID", "Tbl_Profiling_Cases", "[Case_ID]= '" & Temp3 & "'"), 0)
     If Answer3 = "0" Then
          MsgBox "Case ID Not Opened!"
          DoCmd.OpenForm "Frm_Main_Switchboard"
     Else
         
          DoCmd.OpenReport "Rpt_Profiling_Cases", acNormal, , "[Case_ID]='" & Temp3 & "'"
          DoCmd.OpenReport "Rpt_Case_Historical_Comments", acNormal, , "[Case_ID]='" & Temp3 & "'"
         
          Answer4 = Nz(DLookup("Case_ID", "Tbl_Risk_Profile", "[Case_ID]= '" & Temp3 & "'"), 0)
          If Answer4 <> "0" Then
          DoCmd.OpenReport "Rpt_Risk_Profile_by_Case", acNormal, , "[Case_ID]='" & Temp3 & "'"
          
          Answer5 = Nz(DLookup("Case_ID", "TBL_VAT_Risk_Profile_Diagnostic_Tools", "[Case_ID]= '" & Temp3 & "'"), 0)
          If Answer4 <> "0" Then
          DoCmd.OpenReport "Rpt_VAT_DT_Risk_Profile_by_Case", acNormal, , "[Case_ID]='" & Temp3 & "'"
          DoCmd.OpenReport "Rpt_VAT_IO_Risk_Profile_by_Case", acNormal, , "[Case_ID]='" & Temp3 & "'"
                                                     
          Answer5 = Nz(DLookup("Case_ID", "TBL_PAYE_Risk_Profile", "[Case_ID]= '" & Temp3 & "'"), 0)
          If Answer4 <> "0" Then
          DoCmd.OpenReport "Rpt_PAYE_Risk_Profile_by_Case", acNormal, , "[Case_ID]='" & Temp3 & "'"
          
          Answer6 = Nz(DLookup("Case_ID", "TBL_Customs_Risk_Profile", "[Case_ID]= '" & Temp3 & "'"), 0)
          If Answer4 <> "0" Then
          DoCmd.OpenReport "Rpt_Customs_Risk_Profile_by_Case", acNormal, , "[Case_ID]='" & Temp3 & "'"
                                   
          DoCmd.OpenReport "Rpt_Profiling_Cases", acNormal, , "[Case_ID]='" & Temp3 & "'"
          DoCmd.OpenReport "Rpt_Case_Historical_Comments", acNormal, , "[Case_ID]='" & Temp3 & "'"
          
          
        End If
        End If
        End If
        End If
    End If
End If

 

[pbaldy]

Please don't post the same question more than once:

http://www.access-programmers.co.uk/forums/showthread.php?t=212848