prevent an append query from running

[brucle]

I have an append query that enters a day's routes to a dispatch table, based on the value of a combo box. There are also two fields that are populated at time of departure and return, respectively. I want to make sure no one can enter the day's routes twice. In other words if a record contains a route but not a departure time, the query won't run.

I think I need an If statement but I am having trouble with the syntax.

 

[WayneRyan]

brucle,

You can have a command button with OnClick code:

Dim dtmLatest As Date

dtmLatest = DMax("[YourField]", "YourTable")

If dtmLatest >=  Date Then
   MsgBox("Already processed today.")
   Exit Sub
Else
   DoCmd.OpenQuery ....
End If

at least it gets us started,
Wayne

 

[dcx693]

You can do a Dcount or open a recordset version of the query to see if records exist with a route but no departure time. Which do you prefer? Dcounts are generally slow with large tables/queries, but simpler in concept. Either way, you'll need to execute the code from a form.

 

[brucle]

I have a form that contains the combo box. This value is used in the append query that runs when I click a command button. It is also used in several other forms connected to this form.

I tried Wayne's solution, but got an Else without if statement

Here is the code behind the button:

Private Sub Command9_Click()
On Error GoTo Err_Command9_Click
Dim dtmLatest As Date
dtmLatest = DMax("[ROUTE]", "tblDispatch")
If dtmLatest >= Date Then MsgBox ("Already processed today.")
Else

Dim stDocName As String

stDocName = "qryAppendRoutes"
DoCmd.OpenQuery stDocName, acNormal, acEdit
End If
Exit_Command9_Click:
Exit Sub

Err_Command9_Click:
MsgBox ERR.Description
Resume Exit_Command9_Click

End Sub

 

[WayneRyan]

brucle,

If dtmLatest >= Date Then MsgBox ("Already processed today.")

This If statement is not "compound".

Structure should be:

If Something Then
   Action
Else
  Other Action
End If

Wayne