Field rules/validation rules

[sk88]

I have a table which consists of field names like ID, Unit, DATE, Time, Site

Time means either AM, PM, LUNCH.

Each date (eg. 2nd March, 2012) could have 3 different TIME.
Each date and time is stored in different rows/as separate record

How do I set the rules to my query or my table that 2nd march of TIME - PM cannot be a repeat and so on? I know how to set rules for each field in the table itself but my problem is 2nd March could be repeated 3 times in that column and Time could be repeated too and if 2nd March, 2012 of TIME - PM already exits, Access should warn the user...

Is there a way to do that?

Thanks

[June7]

Options:

1. set Date, Time, Site, Unit as compound primary key.

2. use VBA code in some event of data entry form to search table for the inputs

[sk88]

Options:

1. set Date, Time, Site, Unit as compound primary key.

2. use VBA code in some event of data entry form to search table for the inputs

Thanks, June!

How do I set to compound primary key? :S

and for if I use VBA code.. what should it be?

[June7]

1. Table in design view, select the fields simultaneously, set as primary key

2. Simplest code might be a DLookup function. The real trick is figuring out what even to put code in. Maybe a 'Save Record' button click. How restrictive is the data? Can there be more than one unit per date per Site?

If Not IsNull(DLookup("ID", "tablename", "Unit='" & Me.Unit & "', AND [Date]=#" & Me.Date & "# AND [Time]='" & Me.Time & "' AND Site='" & Me.Site & "'")) Then
MsgBox "Record for this date, time, unit, site already exists."
Else
'code to save record, quit form
End If

Review this http://office.microsoft.com/en-us/ac...010341717.aspx

[sk88]

1. Table in design view, select the fields simultaneously, set as primary key

2. Simplest code might be a DLookup function. The real trick is figuring out what even to put code in. Maybe a 'Save Record' button click. How restrictive is the data? Can there be more than one unit per date per Site?

If Not IsNull(DLookup("ID", "tablename", "Unit='" & Me.Unit & "', AND [Date]=#" & Me.Date & "# AND [Time]='" & Me.Time & "' AND Site='" & Me.Site & "'")) Then
MsgBox "Record for this date, time, unit, site already exists."
Else
'code to save record, quit form
End If

Review this http://office.microsoft.com/en-us/ac...010341717.aspx

Thanks June.. . The composite primary key did not help. I was still able to add 2 of the same date and time ..

I am going to try the second option now. .thanks

[sk88]

1. Table in design view, select the fields simultaneously, set as primary key

2. Simplest code might be a DLookup function. The real trick is figuring out what even to put code in. Maybe a 'Save Record' button click. How restrictive is the data? Can there be more than one unit per date per Site?

If Not IsNull(DLookup("ID", "tablename", "Unit='" & Me.Unit & "', AND [Date]=#" & Me.Date & "# AND [Time]='" & Me.Time & "' AND Site='" & Me.Site & "'")) Then
MsgBox "Record for this date, time, unit, site already exists."
Else
'code to save record, quit form
End If

Review this http://office.microsoft.com/en-us/ac...010341717.aspx

June,
I just tried using that code and I am getting error. Please see the attachment.

Also, I am wondering if I can use the DLookup for only Date and Time only? and I can put the code in "AFTER UPDATE" Event? do you think that will work?

[June7]

Yeah, sorry for typo. Remove the comma in front of AND.

Use whatever criteria is appropriate. If you use AfterUpdate, must be in events for both Date and Time fields.

[sk88]

Yeah, sorry for typo. Remove the comma in front of AND.

Use whatever criteria is appropriate. If you use AfterUpdate, must be in events for both Date and Time fields.

This is what I put in afterupdate.... somewhere is wrong I think.. I thought of removing SITE and Unit .. . but its not working ... Can you please tell me where I went wrong?

If Not IsNull(DLookup("ID", "table", "Dateaudit"=#" & Me.Dateaudit & "' AND [time]=#" & Me.time & "# )) Then

MsgBox "Record for this date, time, unit, site already exists."

Else
'code to save record, quit form
End If

[June7]

Errors shown red:

If Not IsNull(DLookup("ID", "table", "Dateaudit"=#" & Me.Dateaudit & "' AND [time]=#" & Me.time & "#)) Then

Correct:
If Not IsNull(DLookup("ID", "table", "Dateaudit=#" & Me.Dateaudit & "# AND [time]='" & Me.time & "'")) Then


Your table is named 'table'? And 'time' is actual name of field?

[sk88]

Errors shown red:

If Not IsNull(DLookup("ID", "table", "Dateaudit"=#" & Me.Dateaudit & "' AND [time]=#" & Me.time & "#)) Then

Correct:
If Not IsNull(DLookup("ID", "table", "Dateaudit=#" & Me.Dateaudit & "# AND [time]='" & Me.time & "'")) Then


Your table is named 'table'? And 'time' is actual name of field?

Yes.. the table name is table and the actual name of the field is time

I just tried using

If Not IsNull(DLookup("ID", "table", "Dateaudit=#" & Me.Dateaudit & "# AND [time]='" & Me.time & "'")) Then

it is still giving me error and says Runtime Error 3464 - Data type mismatch in criteria expression.

[June7]

Dateaudit is a date/time datatype and time is a text datatype? Text uses apostrophe delimiter, date uses # and number uses nothing.

Could there be other Unit and Site entries for this date and time?

[sk88]

Dateaudit is a date/time datatype and time is a text datatype? Text uses apostrophe delimiter, date uses # and number uses nothing.

Could there be other Unit and Site entries for this date and time?

Oh! My time is stored as Number. Like AM = 0, PM means 1, LUNCH means 2

When you say - number uses nothing. Does that mean the code will be --

If Not IsNull(DLookup("ID", "table", "Dateaudit=#" & Me.Dateaudit & "# AND [time]=" & Me.time & ")) Then

[sk88]

and the just tried the above code and it did not work either.
I also tried [time] = & Me.time & ))
No luck on that one too...

[June7]

Remove the last & " marks.

Quotes must be in pairs.

[sk88]

Remove the last & " marks.

Quotes must be in pairs.

YAY! it works!! Thanks, June!!