Open only reports containing the specified record

[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

'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 & "'"
          
          If Nz(DLookup("Case_ID", "Tbl_Risk_Profile", "[Case_ID]= '" & Temp3 & "'"), 0) <> 0 Then
          DoCmd.OpenReport "Rpt_Risk_Profile_by_Case", acNormal, , "[Case_ID]='" & Temp3 & "'"
          
          If Nz(DLookup("Case_ID", "TBL_VAT_Risk_Profile_Diagnostic_Tools", "[Case_ID]= '" & Temp3 & "'"), 0) <> 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 & "'"
          
          If Nz(DLookup("Case_ID", "TBL_PAYE_Risk_Profile", "[Case_ID]= '" & Temp3 & "'"), 0) <> 0 Then
          DoCmd.OpenReport "Rpt_PAYE_Risk_Profile_by_Case", acNormal, , "[Case_ID]='" & Temp3 & "'"
          
          If Nz(DLookup("Case_ID", "TBL_Customs_Risk_Profile", "[Case_ID]= '" & Temp3 & "'"), 0) <> 0 Then
          DoCmd.OpenReport "Rpt_Customs_Risk_Profile_by_Case", 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
End Sub

 

[pbaldy]

You haven't said what the problem is, but does adding proper indenting show any potential problems?

  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 & "'"

      If Nz(DLookup("Case_ID", "Tbl_Risk_Profile", "[Case_ID]= '" & Temp3 & "'"), 0) <> 0 Then
        DoCmd.OpenReport "Rpt_Risk_Profile_by_Case", acNormal, , "[Case_ID]='" & Temp3 & "'"

        If Nz(DLookup("Case_ID", "TBL_VAT_Risk_Profile_Diagnostic_Tools", "[Case_ID]= '" & Temp3 & "'"), 0) <> 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 & "'"

          If Nz(DLookup("Case_ID", "TBL_PAYE_Risk_Profile", "[Case_ID]= '" & Temp3 & "'"), 0) <> 0 Then
            DoCmd.OpenReport "Rpt_PAYE_Risk_Profile_by_Case", acNormal, , "[Case_ID]='" & Temp3 & "'"

            If Nz(DLookup("Case_ID", "TBL_Customs_Risk_Profile", "[Case_ID]= '" & Temp3 & "'"), 0) <> 0 Then
              DoCmd.OpenReport "Rpt_Customs_Risk_Profile_by_Case", 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

 

[charlie442]

Hi

Sorry about the multiple postings - I wasn't sure under which topic to post it. I won't do it again.

The problem is as follows: when profiling a companys tax we may look at either the CIT, VAT, PAYE, customs or some combination of the tax types. Depending on this selection only the relevant profiling tables are populated. Thus when the button is pushed I only want the reports which are populated to open. For example if we are looking at VAT and PAYE only for a particular company I only want the reports which have that case ID in the VAT and PAYE profiling tables to open.

Does this make sense? If not I can try and explain further.

 

[charlie442]

Hi

The correct indentation has solved the problem. Thanks for your assistance - I had no idea the indention made that difference. I thought it was to make it easier to read.

Thanks again

 

[charlie442]

Hi

Actually this is still not working correctly. I tried it first on an example where there are CIT and VAT profiles. Hence it ran through the first three reports and I thought it was working. However it seems to stop as soon as it reaches a section of code where there is no case id for a profile of of that tax type and then not move on to the next section to see if there is a case id in the following tax type/ section.

Does this make sense? For example if there is only a case id in the customs profiling table then the report will not open because it has gone first to look for a CIT profile and when it sees that there is not one the process stops instead of continuing on to see if there are VAT, PAYE , cutoms profiles etc.

I am really confused by this all. I guess I need a piece of code which says if the case id is not found in a particular table then it must move on and look in the next section of code

Thanks

 

[pbaldy]

The indenting was not meant to solve the problem but to illustrate the logical flaw in the code. Each If test is within the previous If, so is only tested if the previous test was true. No test will be run if the preceeding test was false. You want

If Whatever Then
  'do something
End If

If SomethingElse Then
  'do something else
End If