Pausing macro execution with if condition (1 Viewer)

[lupis]

I have a form with a macro that has the action "SendObject" that sends a report by email. In the report itself I have macro that displays a messagebox if no records were found.

What I want o achieve is to halt the "SendObject" macro if no records were found and stop loading Outlook, instead display the parameter box for the report once more to let the user input a valid string after the messagebox that warns for No Records

-Sohail

 

[vbaInet]

Instead of interfering with Outlook, convert the macro to vba (there's a button that does that in Access) and before the SendObject code perform a records count using the DCount() function and if it's not equal to 0 proceed to SendObject. Else show a MSGBOX informing the user that records do not exists for that report.

Here's a link on DCount():

http://www.techonthenet.com/access/functions/domain/dcount.php

 

[lupis]

I tried it out, it works.. I am using a textbox in a form to provide for the serachstring. My problems is that I need to see my report before firing the SendObject command. In my case I see the report behind the form but its not accessable becuase I have a messagebox waiting for a prompt. If the report would be in front of the form, I could see it before replying the prompt of the messagebox.

This is the code I am using:

'------------------------------------------------------------
' cmdSendReport_Click
'
'------------------------------------------------------------
Private Sub cmdSendReport_Click()
Dim varName As String
Dim intNumOfRecs As Integer
Dim intResponse As Integer
 
varName = Me![txtName]
 
If IsNull(varName) Then
  MsgBox "You must enter a Name", vbExclamation, "No Name Entered"
    Me![txtName].SetFocus
      Exit Sub
End If
 
Do
  intNumOfRecs = DCount("*", "qSalesQueryLastName", "[Name] = '" & varName & "'")
 
  If intNumOfRecs = 0 Then
    intResponse = MsgBox("No Records exists for a Name of [" & varName & "], try again?", _
                          vbQuestion + vbYesNo + vbDefaultButton1, "Parameter Prompt")
      If intResponse = vbYes Then Me![txtName].SetFocus: Exit Sub
  Else
  'Record(s) exist, Run Macro or execute code here
 
 
    On Error GoTo cmdSendReport_Click_Err
 
 
 
   DoCmd.OpenReport "PurchaseByPerson", acViewPreview
      intResponse = MsgBox("EMail Report?", vbQuestion + vbYesNo, "E-Mail Confirmation")
        If intResponse = vbYes Then
          DoCmd.Close acReport, "PurchaseByPerson"
          DoCmd.SendObject acReport, "PurchaseByPerson", "PDFFormat(*.pdf)", "", "", "", "Dina poster", _
                           "Härär dem poster du har köpt av CBC", True, ""
        Else
          DoCmd.Close acReport, "PurchaseByPerson"
            Exit Sub
        End If
  End If
 
 
cmdSendReport_Click_Exit:
    Exit Sub
 
cmdSendReport_Click_Err:
    MsgBox Error$
    Resume cmdSendReport_Click_Exit
 
 
 
 
Loop Until intNumOfRecs > 0 Or intResponse = vbNo
 
End Sub

 

[vbaInet]

I don't understand why you would want to see a BLANK report PLUS show a message box to say records do not exists? If someone can see a blank report it is obvious that records do not exists. This will cause unecessary execution and extra few milliseconds wait for the user before he/she can perform another search.

Also, your prompt asking if the user wants to perform another search is unecessary. Just always send the focus to the textbox because you don't have an alternative action for vbNo.

 

[lupis]

I am not asking for a blank report, after supplying the searchstring to the textbox, if there is a valid result I want to the report to be displayed before sending it to outlook, or if no record is found to prompt for a new search.

 

[vbaInet]

I am not asking for a blank report, after supplying the searchstring to the textbox, if there is a valid result I want to the report to be displayed before sending it to outlook, ...

Just as you've mentioned it, follow those logical steps. Open the report, then Send.

or if no record is found to prompt for a new search.

No point in prompting for a new search as I mentioned before. Just prompt with a message saying no records found. The user will decide whether to perform another search or not. It's logical.

 

[lupis]

But I want to show the user the result of the search, I want the user to view the result of the search, halt execution of the SendObject, if the user approves of the resultset, then unpaus, so the SendObject can be executed. Thats why I had the second messagebox, I thought I could show the user the result of the searchstring.

In the code I supplied earlier, can you alter the code to make it work that way, in this order:

1. After click
2. If no records found, Prompt with a message "No records found", and show the searchstring supplied, and give back focus to the textbox on the form
3. If records found, the report gets focus in front of the form, paus execution of SendObject code with a prompt (CRUCIAL THAT THE USER SEES THE RESULT OF THE SEARCH BEFORE SENDING IT)
4. If user chooses to procede, execute SendObject

With the code I provided previously, could you alter changes in that code,so it could behave as I mentioned earlier. My problem is even though the reports get generated, it does get focus in front of the from, and the SendObject code gets exexuted and the Outlook form cant be minimized so I can see the report, you can either send the mail or cancel it.

thanks
-Sohail

 

[vbaInet]

Then you would need to move the Msgbox "Do you want to send this report" and the SendObject code to the ON ACTIVATE event of the report.

For the focus problem, change the Modal property of the report to Yes, or do it in code for each time you open the report:

DoCmd.OpenReport "ReportName", , , , acDialog

 

[lupis]

This is the code for the report on activate:

'------------------------------------------------------------
' Report_Activate
'
'------------------------------------------------------------
Private Sub Report_Activate()
On Error GoTo Report_Activate_Err

    
DoCmd.OpenReport "PurchaseByPerson", , , , , acDialog
      intResponse = MsgBox("EMail Report?", vbQuestion + vbYesNo, "E-Mail Confirmation")
        If intResponse = vbYes Then
          DoCmd.Close acReport, "PurchaseByPerson"
          DoCmd.SendObject acReport, "PurchaseByPerson", "PDFFormat(*.pdf)", "", "", "", "Dina poster", _
                           "Härär dem poster du har köpt av CBC", True, ""
  DoCmd.OpenReport "PurchaseByPerson", acViewPreview
        Else
          DoCmd.Close acReport, "PurchaseByPerson"
            Exit Sub
        End If

Report_Activate_Exit:
    Exit Sub

Report_Activate_Err:
    MsgBox Error$
    Resume Report_Activate_Exit

End Sub

so from within the form for running the report how do I call the on activate event for the report?

'------------------------------------------------------------
' cmdSendReport_Click
'
'------------------------------------------------------------
Private Sub cmdSendReport_Click()
'Dim varName As String
'Dim intNumOfRecs As Integer
'Dim intResponse As Integer
 
varName = Me![txtName]
 
If IsNull(varName) Then
  MsgBox "You must enter a Name", vbExclamation, "No Name Entered"
    Me![txtName].SetFocus
      Exit Sub
End If
 
Do
  intNumOfRecs = DCount("*", "qSalesQueryLastName", "[Name] = '" & varName & "'")
 
  If intNumOfRecs = 0 Then
    intResponse = MsgBox("No Records exists for a Name of [" & varName & "], try again?", _
                          vbQuestion + vbYesNo + vbDefaultButton1, "Parameter Prompt")
      If intResponse = vbYes Then Me![txtName].SetFocus: Exit Sub
  Else
  'Record(s) exist, Run Macro or execute code here
    

    On Error GoTo cmdSendReport_Click_Err
    
 'Code to run the On Activate event

 
  End If


cmdSendReport_Click_Exit:
    Exit Sub

cmdSendReport_Click_Err:
    MsgBox Error$
    Resume cmdSendReport_Click_Exit



  
Loop Until intNumOfRecs > 0 Or intResponse = vbNo

End Sub

 

[vbaInet]

You don't need to call the event, in fact you can't call the event. It automatically fires after the report loads. So like I mentioned, the only code that should be in there is the Msgbox code and SendObject if they user says Yes. Nothing to do with OpenReport or OpenForm.

Check out the On Activate event in the help files. Hit F1 to open up the help files.

 

[lupis]

After iniating the report from the form

DoCmd.OpenReport "PurchaseByPerson", , , , , acDialog

is printing the report but neither the messagebox or the SendObject ever gets fired

 

[vbaInet]

Let's see the code in both events.

 

[lupis]

Here is the code for the form:

'------------------------------------------------------------
' cmdSendReport_Click
'
'------------------------------------------------------------
Private Sub cmdSendReport_Click()
Dim varName As String
Dim intNumOfRecs As Integer
Dim intResponse As Integer
 
varName = Me![txtName]
 
If IsNull(varName) Then
  MsgBox "You must enter a Name", vbExclamation, "No Name Entered"
    Me![txtName].SetFocus
      Exit Sub
End If
 
Do
  intNumOfRecs = DCount("*", "qSalesQueryLastName", "[Name] = '" & varName & "'")
 
  If intNumOfRecs = 0 Then
    intResponse = MsgBox("No Records exists for a Name of [" & varName & "], try again?", _
                          vbQuestion + vbYesNo + vbDefaultButton1, "Parameter Prompt")
      If intResponse = vbYes Then Me![txtName].SetFocus: Exit Sub
  Else
  'Record(s) exist, Run Macro or execute code here
    

    On Error GoTo cmdSendReport_Click_Err
    
   

   DoCmd.OpenReport "PurchaseByPerson", , , , , acDialog
    
  End If


cmdSendReport_Click_Exit:
    Exit Sub

cmdSendReport_Click_Err:
    MsgBox Error$
    Resume cmdSendReport_Click_Exit



  
Loop Until intNumOfRecs > 0 Or intResponse = vbNo

End Sub

and the code for the report:

'------------------------------------------------------------
' Report_Activate
'
'------------------------------------------------------------
Private Sub Report_Activate()
On Error GoTo Report_Activate_Err

    

      intResponse = MsgBox("EMail Report?", vbQuestion + vbYesNo, "E-Mail Confirmation")
        If intResponse = vbYes Then
          DoCmd.Close acReport, "PurchaseByPerson"
          DoCmd.SendObject acReport, "PurchaseByPerson", "PDFFormat(*.pdf)", "", "", "", "Dina poster", _
                           "Härär dem poster du har köpt av CBC", True, ""
  DoCmd.OpenReport "PurchaseByPerson", acViewPreview
        Else
          DoCmd.Close acReport, "PurchaseByPerson"
            Exit Sub
        End If

Report_Activate_Exit:
    Exit Sub

Report_Activate_Err:
    MsgBox Error$
    Resume Report_Activate_Exit

End Sub

 

[vbaInet]

Got your message.

I don't see why you would need to loop through to perform a search using the same criteria. It would ALWAYS return the same result. Use this code instead and drop the Activate code:

'------------------------------------------------------------
' cmdSendReport_Click
'
'------------------------------------------------------------
Private Sub cmdSendReport_Click()
On Error GoTo cmdSendReport_Click_Err 

   Dim varName As String
 
   If len(Me![txtName] & "") = Then
      MsgBox "You must enter a Name", vbExclamation, "No Name Entered"
      txtName.SetFocus
      Exit Sub
   End If

   varName = Me![txtName]
 
   If DCount("*", "qSalesQueryLastName", "[Name] = '" & varName & "'") = 0 Then
      if MsgBox("No Records exists for a Name of [" & varName & "], try again?", _
                 vbQuestion + vbYesNo + vbDefaultButton1, "Parameter Prompt") = vbyes then
     ' Set focus on txtName
      else
         ' Close Search form
      end if
   Else
      DoCmd.OpenReport "PurchaseByPerson", , , , , acDialog
      ' SendObject here or just open the report as above, then create a Send To E-mail menu
      ' that will initiate the SendObject function
   end if

cmdSendReport_Click_Exit:
    Exit Sub

cmdSendReport_Click_Err:
    MsgBox Error$
    Resume cmdSendReport_Click_Exit

End Sub