Button supposed to open only reports where case is populated

[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

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
          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 & "'"
     Else
                                            
          MsgBox "Case ID Not Opened!"
          DoCmd.OpenForm "Frm_Main_Switchboard"
          
        End If
        End If
        End If
        End If
    End If
End If
End Sub

 

[smig]

what is not working exactly ?
do you get any error ?

 

[missinglinq]

smig's question needs to be answered. What I see, right off, is that you're using the Nz() function against whatever the DLookup function returns. If it doesn't return a value, you're assigning the Integer 0 (zero) to Answer3.

But then, in your next line,

If Answer3 <> "0" Then

you're comparing Answer3 to "0" which, because of the quotation marks, is a String.

Because of this I would suspect that

If Answer3 <> "0" Then

will always be True.

Linq ;0)>

 

[smig]

Truth is I didn't go inspecting the code carefully
It's easier when you ask a question like this to describe the end result. I guess in this case it will always give the MsgBox "Case ID Not Opened!" and go to switchboard

smig's question needs to be answered. What I see, right off, is that you're using the Nz() function against whatever the DLookup function returns. If it doesn't return a value, you're assigning the Integer 0 (zero) to Answer3.

But then, in your next line,

If Answer3 <> "0" Then

you're comparing Answer3 to "0" which, because of the quotation marks, is a String.

Because of this I would suspect that

If Answer3 <> "0" Then

will always be True.

Linq ;0)>

the same go for Answer4
also after you set Answers5 and 6 you ask about Answer4.
You ask for Answer5 twice

BTW - there is no need to use all these answers... you can use:
if Nz(DLookup("Case_ID", "Tbl_Profiling_Cases", "[Case_ID]= '" & Temp3 & "'"), 0) <> 0 Then
....