Need help to correct code that runs append queries

[houstonbill]

I am stuck on how to fix code. Have a linked database that has 4 append queries that must run once daily, one of which is a Date query. The primary tables used are linked to another database that when opened 1st time each day, the data from our mainframe loads the previous business days data. Therefore, the primary database must run first and must carry updated information. Because different people are involved in the opening of these databases, sometimes the timing is off and messes up the data in my adhoc database or drops days. Today I went in and bypassed the AutoExec and found that both Tuesday (4th) and Thursday (6th) were gone, even though they were there on Friday. I have an AutoExecMaco to run code in 2 modules. One to get user detail and the 2nd that runs the queries shown below. How can I made sure that if this adhoc database is opened first, or at anytime really, that it will not populate any tables unless the previous business days data is on the linked tables.

Public Function OpenQuery()
Dim LastUpdate, Running, UserID, PrevUpdater

UserID = LogonID()
PrevUpdater = DLookup("UserName", "tbl_UpdateManager")
LastUpdate = DLookup("LastUpdate", "tbl_UpdateManager")
Running = DLookup("Running", "tbl_UpdateManager")
DoCmd.SetWarnings False

If Running = False Then
If LastUpdate < DateValue(Now()) Then
DoCmd.RunSQL "UPDATE tbl_UpdateManager SET tbl_UpdateManager.Running = True;"

DoCmd.OpenQuery "qryDateToday"
DoCmd.OpenQuery "InventProfFac"
DoCmd.OpenQuery "Count Of Shelf Comb"
DoCmd.OpenQuery "InvenRespID"
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tbl_UpdateManager SET tbl_UpdateManager.LastUpdate = #" & DateValue(Now()) & "#, tbl_UpdateManager.UserName = '" & UserID & "', tbl_UpdateManager.Running = False;"
Else
DoCmd.OpenForm "Switchboard"
End If
Else
DoCmd.OpenForm "Switchboard"
End If

DoCmd.SetWarnings True

End Function

 

[lestatdelioncourt]

you could just check to make sure that the previous days data is in the tables before u run the queries. (might not work if there is no data for weekends/ holidays).

Alternatively change the process that populates the previous days data to populate a run date somewhere, then even if there is no data it populates the date to say that it has run. then check this date before u run the queries.

 

[SkipVought]

What specific parameters define whether the other database has bee completely updated?

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]