finding records using multiple field criteria

[Guest]

My database has a tblEvents that has an EventID field that is autonumbered.
The fields on which the EventID is based are site, date, and time. It is
possible for there to be more than one subject with the same site, date, and
time. During data entry, it is necessary to determine if the combination of
site, date, and time have already been used. If so, then the EventID
associated with that combination should be used. and no new record should be
written in tblEvents. If the combination is new, then a new record should be
written. Obviously a macro should be used in a data entry form. My question
is: what is the best macro action or function for searching for the three
field combination in tblEvents?

 

[Steve Schapel]

LAF,

You would put your macro on the Before Update event of your form. You
could put a Condition in the macro, something like this...
DCount("*","tblEvents","[site]='" & [site] & "' And [EventDate]=#" &
[EventDate] & "# And [EventTime]=#" & [EventTime] & "#")>0
.... and use these actions in your macro:
CancelEvent
RunCommand/Undo

By the way, 'date' and 'time' are Reserved Words (i.e. have a special
meaning) in Access, and as such should not be used as the name of a
field or control, so I have changed them in the example above. The
exmple also assumes that Site is a text data type.