Check for Primary Key Violations before entering data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form set up to enter data into a table. (the table is named
Monolith, and the form is (unfortunately) named "Another try at
entering/editing data by day")

The table stores customer counts in different areas of the casino, so the
primary key is spread across 2 fields: Date and Time. (time is limited to 12
values - even numbered hours)

The first two fields on the form are Date and Time (time is a drop-down
list).

What I would like to do is check for a primary key violation (duplicate
values) before the user starts entering the actual count data. Ideally, this
would pop up a form allowing the user to chose between changing the Date/Time
values or Opening up a form to edit the data for that specific date and time.


I'm assuming, I need to write an event procedure for "AfterUpdate" on the
Time field, checking for duplicate values, but I'm not sure how to do this
with a Multiple-Field Primary Key.

Suggestions?
Aaron
 
Aaron,

Here's one possible approach...

If DCount("*","Monolith","[Date]=#" & Me![Date] & _
"# And [Time]=" & Me![Time])>0 Then
DoCmd.OpenForm "YourCorrectionForm"
End If

By the way, as an aside, "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 or database object. I suggest you change this if
you can.
 
Thanks for the response!
As for the reserved names, I'm afraid I'm too deep into this project to go
back now....

I used the code you suggested, but I keep getting an error message
I've tried it using new values, and values I know should produce duplicates,
and both come up with an error box that says:

Syntax error (missing operator) in query expression '[Date]=#11/26/2004# AND
[Time]=6:00:00 PM'

The date and time chang with what I enter in the fields, but the rest of the
error message remains the same.
Suggestions?

Steve Schapel said:
Aaron,

Here's one possible approach...

If DCount("*","Monolith","[Date]=#" & Me![Date] & _
"# And [Time]=" & Me![Time])>0 Then
DoCmd.OpenForm "YourCorrectionForm"
End If

By the way, as an aside, "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 or database object. I suggest you change this if
you can.

--
Steve Schapel, Microsoft Access MVP


Air-ron said:
I have a form set up to enter data into a table. (the table is named
Monolith, and the form is (unfortunately) named "Another try at
entering/editing data by day")

The table stores customer counts in different areas of the casino, so the
primary key is spread across 2 fields: Date and Time. (time is limited to 12
values - even numbered hours)

The first two fields on the form are Date and Time (time is a drop-down
list).

What I would like to do is check for a primary key violation (duplicate
values) before the user starts entering the actual count data. Ideally, this
would pop up a form allowing the user to chose between changing the Date/Time
values or Opening up a form to edit the data for that specific date and time.


I'm assuming, I need to write an event procedure for "AfterUpdate" on the
Time field, checking for duplicate values, but I'm not sure how to do this
with a Multiple-Field Primary Key.

Suggestions?
Aaron
Click to expand...
Click to expand...
 
Air-ron said:
Thanks for the response!
As for the reserved names, I'm afraid I'm too deep into this project to go
back now....

I used the code you suggested, but I keep getting an error message
I've tried it using new values, and values I know should produce duplicates,
and both come up with an error box that says:

Syntax error (missing operator) in query expression '[Date]=#11/26/2004# AND
[Time]=6:00:00 PM'
Click to expand...

The time value needs # delimiters the same as the date value.
 
Agreed, if the [Time] field is a Date/Time data type. I understood from
your original post that this was not the case. So. just to expand on
Rick's post, try...
If DCount("*","Monolith","[Date]=#" & Me![Date] & _
"# And [Time]=#" & Me![Time] & "#")>0 Then
DoCmd.OpenForm "YourCorrectionForm"
End If
 
Yes, it is Date/Time data type, but restricted to the 12 possible values upon
data entry.

Thank you both for your help!
Aaron

Steve Schapel said:
Agreed, if the [Time] field is a Date/Time data type. I understood from
your original post that this was not the case. So. just to expand on
Rick's post, try...
If DCount("*","Monolith","[Date]=#" & Me![Date] & _
"# And [Time]=#" & Me![Time] & "#")>0 Then
DoCmd.OpenForm "YourCorrectionForm"
End If

--
Steve Schapel, Microsoft Access MVP

Rick said:
The time value needs # delimiters the same as the date value.
Click to expand...
Click to expand...
 
Back
Top