Help with End If

[Jody]

I have never used "End If" before, but I think I need it in the following code:

---------------------------------------------------------------------------------------
Private Sub RunReport_Click()
On Error GoTo Err_RunReport_Click

Dim stDocName As String
DoCmd.OpenQuery "AppendConsumerTable"
DoCmd.SendObject acSendReport, "ReplacementReport", acFormatSNP,
"(e-mail address removed)", "(e-mail address removed)", "(e-mail address removed)",
"Replacement Parts Request", "Please review the attached document and take
the necessary action.", 0
DoCmd.OpenQuery "UpdatePartSentQuery"
DoCmd.OpenQuery "DeleteConsumerTable_qry"
DoCmd.OpenForm "Menu"
DoCmd.CLOSE acForm, "Confirmation"

Exit_RunReport_Click:
Exit Sub

Err_RunReport_Click:
MsgBox Err.Description
Resume Exit_RunReport_Click

End Sub
---------------------------------------------------------------------------------------

Yesterday, when the user clicked the button to execute this series of
events, the code failed which resulted in the following:
1: The data was not appended to the Customer Table
2: The report was rendered empty
3: The data WAS deleted from the temp table
4: The Menu form opened
5: The "Confirmation Form" did close

The desired result I am seeking is that if the first command fails, then
stop executing the code, and so on for each additional line. I am just
unsire of the syntax of the End If sttement.

Any help is appreciated.

 

[Douglas J. Steele]

You can't just use an End If. End If is the conclusion of an If statement.

One option would be to use the Execute method of the QueryDef object, and
then check how many records got appended by the query.

Private Sub RunReport_Click()
On Error GoTo Err_RunReport_Click

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef
Dim stDocName As String

Set dbCurr = CurrentDb()
Set qdfCurr = dbCurr.QueryDefs("AppendConsumerTable")
With qdfCurr
.Execute dbFailOnError
If .RecordsAffected > 0 Then
DoCmd.SendObject acSendReport, "ReplacementReport", _
acFormatSNP, "(e-mail address removed)", _
"(e-mail address removed)", "(e-mail address removed)", _
"Replacement Parts Request", _
"Please review the attached document and take " & _
" the necessary action.", 0
DoCmd.OpenQuery "UpdatePartSentQuery"
DoCmd.OpenQuery "DeleteConsumerTable_qry"
DoCmd.OpenForm "Menu"
DoCmd.CLOSE acForm, "Confirmation"
End If
End With

Exit_RunReport_Click:
Exit Sub

Err_RunReport_Click:
MsgBox Err.Description
Resume Exit_RunReport_Click

End Sub

You may want to use similar techniques to run the other queries. (It has the
advantage that it doesn't present the "Access is about to ...." prompt)

 

[Jody]

Can you explain what

Dim dbCurr As DAO.Database and
Dim qdfCurr As DAO.QueryDef

mean?
These two arguments are creating compile errors. If I comment them out, the
script runs without a hitch, but I am afraid that this will nullify the
solution to the problem.

Other than that, I believe this is exactly what I was looking for.

Thank You.

 

[Douglas J. Steele]

You must be using either Access 2000 or Access 2002.

In Access 97 and earlier, the only method of communicating with the database
was through DAO (Data Access Objects). Around the time of Access 2000,
Microsoft introduced a new method call ADO (ActiveX Data Objects). While DAO
was designed specifically to work with Jet databases (i.e. MDB or MDE
files), for some reason they decided not to include a reference to DAO by
default, only having the reference to ADO. Fortunately, they came to their
senses in Access 2003 and newer: references are set to both by default.

Go into the VB Editor and select Tools | References from the menu bar.
Scroll through the list of available references until you find the entry for
Microsoft DAO 3.6 Object Library. Select it (by checking the box to its
left), then exit the dialog.

 

[Jody]

PERFECT! That works splendidly. Thank you for your help with the initial
problem and for helping me understand.