Pausing macro execution with if condition

[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

[ADezii]

@lupis
Let the User enter the Criteria for the Report on a Text Box on a Form, then check for the existence of any Records prior to opening the Report itself. Loop until Records matching the Criteria are met, or until the User says No.

Dim varLastName As Variant
Dim intNumOfRecs As Integer
Dim intResponse As Integer
 
varLastName = Me![txtLastName]
 
If IsNull(varLastName) Then
  MsgBox "You must enter a Last Name", vbExclamation, "No Name Entered"
    Me![txtLastName].SetFocus
      Exit Sub
End If
 
Do
  intNumOfRecs = DCount("*", "Employees", "[LastName] = '" & varLastName & "'")
 
  If intNumOfRecs = 0 Then
    intResponse = MsgBox("No Records exists for a Last name of [" & varLastName & "], try again?", _
                          vbQuestion + vbYesNo + vbDefaultButton1, "Parameter Prompt")
      If intResponse = vbYes Then Me![txtLastName].SetFocus: Exit Sub
  Else    'Record(s) exist, Run Macro or execute code here
    'Now OK, Run Macro or initiate SendObject
  End If
Loop Until intNumOfRecs > 0 Or intResponse = vbNo

P.S. - Instead of a Parameter Prompt, the Criteria for the Report's Row Source would point to a Form Field as in:

Forms![frmYaDaYaDa]![txtLastName]

[lupis]

Great, I am really thanksfull for your advice. I have just one more question.

Here is the code for the "SendObject":

'Record(s) exist, Run Macro or execute code here
 
 
    On Error GoTo cmdSendReport_Click_Err
 
    DoCmd.OpenReport "PurchaseByPerson", acViewReport, "", "", acNormal
 
    DoCmd.SendObject acReport, "PurchaseByPerson", "PDFFormat(*.pdf)", "", "", "", "Dina poster", "Härär dem poster du har köpt av CBC", True, ""
 

When the Send Object fires, and the Outlook form pops up, the minimize property is disabled, so I cant view the report behind, I can maximize the email form or cancel the email form, which then fires a messagebox telling "The Send Object action was cancelled" This denies the usser to see the generated report prior to sending it. Is there any way to have focus on the report first instead of the mailform, or to have the mailform minimize property set to true?

-Thanks

[ADezii]

@lupis
This is a little tricky, but you can try the following:

Dim varLastName As Variant
Dim intNumOfRecs As Integer
Dim intResponse As Integer
 
varLastName = Me![txtLastName]
 
If IsNull(varLastName) Then
  MsgBox "You must enter a Last Name", vbExclamation, "No Name Entered"
    Me![txtLastName].SetFocus
      Exit Sub
End If
 
Do
  intNumOfRecs = DCount("*", "Employees", "[LastName] = '" & varLastName & "'")
 
  If intNumOfRecs = 0 Then
    intResponse = MsgBox("No Records exists for a Last name of [" & varLastName & "], try again?", _
                          vbQuestion + vbYesNo + vbDefaultButton1, "Parameter Prompt")
      If intResponse = vbYes Then Me![txtLastName].SetFocus: Exit Sub
  Else    'Record(s) exist, Run Macro or execute code here
    DoCmd.OpenReport "rptTest", 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, "rptTest"
            Exit Sub
        End If
  End If
Loop Until intNumOfRecs > 0 Or intResponse = vbNo

[lupis]

I tried it out. This is what happens:

1. On button click
2. Email confirmation messagebox appears
3. Report is only vissible beyond the form, not accessable because Email confirmation has focus
4. On yes, the report dissapears and Outlook form appears
5. On No, report closes

If the report had focus in front of the form,then it would work, becuase I could move the messagebox around and the confirm to email report or not.

This is the code I am firing for the button event:

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

[lupis]

Also when supplying a empty string, I am getting run time error 94 "Invalid use of null"
The debugger stops at:

varName = Me![txtName]

[ADezii]

@lupis
Why does the Report need to be accessible and simply not visible?

[lupis]

Sorry, I mean actually vissible, its is partially vissible, but as long as the SendObject has focus with the email form and the report is behind the form, I cant see the report completly or its result