Lookup table error in If statement.

[houstonbill]

Have a couple of modules that run when my DB opened. Tables primarily link to another source DB that receives downloads of data daily. My DB has some "Make Table" queries designed to run on the AutoExec macro. There is a lookup table as shown below.

UserName LastUpdate Running
e122685 10/18/2007

Problem is that depending on the sequence of opening up the databases, once every 1-2wks one days data/dates will drop. For example, yesterday the 10/16 data was pulled in and did show. However today, the 16th entries are gone and the 17th entries are showing. To adjust for this I am trying to put something in my module, but I am getting an error I cannot figure out. If someone could look at my module below and let me know why it is not working, I would greatly appreciate it/ It is the 7th line....If the Lookup Table etc, etc. This shows up in red and bounces to the word Lookup. I was trying to do this without help, but I can't seem to make it work.

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")
If the Lookup Table Last Refresh date =(date)

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"

Else: MsgBox "The Wits Data has not updated for Today"

End If

DoCmd.SetWarnings True

End Function

 

[TomCologne]

If the Lookup Table Last Refresh date =(date)

looks like a comment!

I'm also not sure why you'd need the nested If statement when you're going to open the Switchboard form anyway.
Wouldn't

If Running = False And LastUpdate < DateValue(Now())Then
...
Else
DoCmd.OpenForm "Switchboard"

do the same?
Maybe I'm missing something.

TomCologne

 

[houstonbill]

Sorry I did not get back with you sooner. Was out of town at my kids and didn't have my laptop with me or my work. I guess you are right about the nested statement. I am going to rethink what I am trying to do here and do a few things differently. Let me get back with you if I run into problems.