Validating Data

[spencer2780]

Hi,

I am trying to validate my database to not allow duplicate entries. I want it to match the Date, Time and Room number of an appointment in a the table up with that on the Form and then give a message saying "room already booked" if they match.

Any Ideas?

I also need some code to not allow the date to be entered if it's a Sunday and certain hours on a Saturday.

Hope you can help

 

[PHV]

not allow duplicate entries
Create a composite index not allowing duplicates on the relevant fields.
You may also consider the DLookUp function
if it's a Sunday
Take a look at the WeekDay function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[spencer2780]

Hi PH,

Thanks for the quick response but is there any way you could give me any more info on what i actually need to do.

What is a composite index and how do i create one?
Can you use the DLookUp function to look up three filds togather?
Again what is the WeekDay Function and how do i use it?

Sorry to be a pain but imagine i have know idear what i'm doing (cos i don't!).

 

[PHV]

Take a look at the Northwind database to get some knowledge.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[spencer2780]

Ok I'll give it ago.

Many Thanks

 

[spencer2780]

I am still having problems.

The Northwind database doesn't really help for what i want to do.

I have found some coding on how to create composite indexes but i don't know how to then use it for the validation.

Is there anyone out there who can post me a bit of code that does this sort of validation.

 

[spencer2780]

I have now worked it out if anyone is interested.

Not allow duplicate entries:
I created a index with the three fiels and set it to unique. This is at table level by clicking the indexes button on the toolbar.

Validating Saturday And Sunday.
This was done by adding this VBA code to the Before Update option.

Dim Str1 as String
Dim Str2 as String

Str1 = me.yourdatefield
Str2 = me.yourtimefield

If Format(Str1, "dddd") = "Sunday" Then
msgbox "Whatever You Like" ,vdexclamation
ElseIf
Format (Str1, "dddd") = "Saturday" And Str2 > #12.30 PM# Then
msgbox "Whatever You Like" ,vdexclamaion
End If
End If

Hope this is helpful to someone