Timer Form

[Don]

I have a table that acts as a timer to update data early in the morning. Once
runs, it puts a date in a table to indicate that the procedure has or has not
been triggered that day. This form is working but I have found that it does
not indicate that the macro that it triggers was actually successfully run.
Only that it was triggered. I would like the code if possible to tell if the
macro was successfully run. One possibility would be to add another field to
the table that holds the date to indicate say a 1 if successful and a 0 of
not successful. I think I can achieve this by adding an If......Then
statement. I can write a simple If...Then like the following addressing the
value of fields in a form;

If Not IsNull(Address) And IsNull(PostalCode) Then

MsgBox "You must enter a postal code.", vbExclamation

PostalCode.SetFocus 'Go back to PostalCode field.

Cancel = True 'Cancel saving the record.

End If

But this code is in a Message Box I created. How can I identify the message
boxes created by access; I don't know what their names are. I think I can
turn off the Messages boxes and have them all be answered affirmatively each
time the queries run. If that is the best option, then I need to know what
code could I use to insure all of the queries in the macro finished without
an error.

The code I am running is:

Private Sub Form_Timer()

Dim stDocName As String

If Time() > #6:30:00 AM# Then

If DLookup("LastTimerDate", "tblTimerDate") < DATE Then

stDocName = "Append & Update Linked Tables to Me tables"

DoCmd.RunMacro stDocName

CurrentDb.Execute _

"UPDATE tblTimerDate SET LastTimerDate = " & _

Format(DATE, "\#mm/dd/yyyy\#")

End If

End If



End Sub



How should I address this issue?

Thanks,

Dennis

 

[Rob Oldfield]

You need to include error handling in your code to catch errors. Take a
look at this:

http://allenbrowne.com/ser-23a.html

Having said that, I don't think there's any way to disable error messages
generated by macros, which is one of the reasons that you shouldn't really
be using them. You can convert an existing macro to VBA code by selecting
it and then using File, Save As and telling Access to change it to a module.
That also has the option of adding error handling... but you should look at
the link above anyway so that you know what it's doing.

 

[Don]

Rob,

The following is the code I now have for my Form Timer. I would like some
things added to what I have. Modifying my code how can I:

1) Disable the confirm action message boxes that pop-up so the action is
always excepted on the query questions yet allow error messages to be
responded to by the user?

2) I do not want the date to be changed in the table if the code does not
complete and an error code is displayed. How can I do that?

3) I have some error handeling in the code thanks to you. Does it look like
it works? I cannot make something error out on purpose only as a result of my
programming

------------------------------------------------------------------------------------------

Private Sub Form_Timer()

On Error GoTo Err_Form_Timer


If Time() > #6:30:00 AM# Then

If DLookup("LastTimerDate", "tblTimerDate") < DATE Then

' Appends Rx table to RX1 table "Adds new RX's to RX1 table"
DoCmd.OpenQuery "1Append Rx to RX1 Query", acNormal, acAdd
' Append Patient table to Patients table "Adds new records"
DoCmd.OpenQuery "2Append Patient to PatientsQuery", acNormal, acAdd
' Deletes records from Patients that are "GONE" over 1 year
DoCmd.OpenQuery "3Delete >1 years From Patients qry", acNormal, acAdd
' Update Housing Units from Patient table to Patients table
DoCmd.OpenQuery "4Update Housing from Patient to Patients", acNormal,
acEdit
' Deletes records from RX1 that are "GONE" over 1 year
DoCmd.OpenQuery "5RX1 Delete >1 years Query", acNormal, acEdit


CurrentDb.Execute _
"UPDATE tblTimerDate SET LastTimerDate = " & _
Format(DATE, "\#mm/dd/yyyy\#")

End If
Form_Timer_Exit:
Exit Sub

Err_Form_Timer:
MsgBox Error$
Resume Form_Timer_Exit
MsgBox Err.Number & Err.Description

Resume Form_Timer_Exit
End If

End Sub



Thanks,
Dennis

 

[Rob Oldfield]

Easiest way to get rid of the "about to update 23 records" thing is to use
docmd.setwarnings false. You do need to remember to use docmd.setwarnings
true afterwards to switch them back on.

It looks to me as if it should work. Best way of testing it will be to do
something straightforward - maybe just rename the table that is the target
of the first append query. If you can't do that because the data is live
then a) you should be working on a development copy, not a live db and b)
just add another action query that again references a table that doesn't
exist/tries to add text values onto a numeric field/does whatever else you
think might cause the queries to error.