Validation Help

[KDa]

Hello,

For my Year 12 Computing course, I need to develop a database which will take bookings for a village hall. The database consists of a Bookings Table, a Customer Data Table and a Hiring Cost Table. It is in the Bookings Table that I am having the most trouble.

I believe I have already solved my double booking dilemma by making the Date of Event and Type of Event fields in the Bookings Table into a composite key, thereby prohibiting the entering of data where Date of Event and Type of Event are the same, making them unique.

Here is my main problem at the moment, I need a validation rule on the Date of Event or perhaps the Date of Booking field which means a booking must be made at least a week, and no more than two months in advance of the date of event. The thing is this validation rule is only needed for occasional bookings. Present, again on the bookings table is a Booking Type field which has a lookup consisting of the following “Regular” and “Occasional”, regular meaning bookings that are made at the start of the year, for the whole year.

Ideally, I would like to have it so that when the booking type field is set to regular, the validation rule for the date isn’t needed, and when the booking type field is set to occasional, the validation rule for the date is needed/present.

Could anyone help me with this? As any help would be much appreciated.

Many thanks
Daniel

If anyone would like any screenshots of the Bookings Table, email me at daniel_goodman@hotmail.com

 

[carash77ash]

Try this
Private Sub Booking_AfterUpdate()
On Error GoTo Err_Booking_AfterUpdate

If Me.booking_type = "occasional" Then
Me.booking_date = Now()
Dim strTblName As String, strValidRule As String
Dim strValidText As String
Dim intX As Integer
Dim thedate As Date
Dim seconddate As Date

thedate = DateDiff(d, bookingdate, Startdate)
secondate = DateDiff(d, bookingdate, Startdate)

strTblName = "Booking"
strValidRule = "thedate >= 7" And "seconddate <=60"
strValidText = "You can only make a booking that is No less then 1 Week Away and No greater Than 2 Months"
intX = SetTableValidation(strTblName, strValidRule, strValidText)
Else
End If

Err_Booking_AfterUpdate:
GoTo Exit_booking_afterupdate

Exit_booking_afterupdate:
Exit Sub

End Sub


Function SetTableValidation(strTblName As String, _
strValidRule As String, strValidText As String) _
As Integer

Dim dbs As Database, tdf As TableDef

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTblName)
tdf.ValidationRule = strValidRule
tdf.ValidationText = strValidText
End Function

Hope this helps

Ash