I am creating a tennis court booking system. I am trying to create a macro that will not allow the same court to be booked on the same date at the same time.
I have the following tables and fields
tblMembers
MemberID
FirstName
LastName
etc
tblCourts
CourtID
CourtName
tblSchedule
ScheduleID
ScheduleDate
CourtID
tblSheduleDetails
SheduleDetailsID
SheduleID
MemberID
SheduleStartTime
SheduleEndTime
I have tried to modify the code form a similar post but am getting an error for "Cancel = True" - Private Sub Form_Current()
-
If Me.NewRecord = True Then
-
Dim strWhere As String, strMessage As String
-
Dim rsClone As Recordset
-
-
strWhere = "(([BookingsSubform].Form![CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
-
") AND ([BookingsSubform].[Form]![ScheduleID]=" & Me.[BookingsSubform].[Form]![ScheduleID] & _
-
") AND ([BookingsTimeSubform].[Form]![ScheduleEndTime]>=#" & _
-
Format(Me.[BookingsTimeSubform].[Form]![ScheduleStartTime], "Medium Time") & _
-
"#) AND ([BookingsTimeSubform].[Form]![ScheduleStartTime]<=#" & _
-
Format(Me.[BookingsTimeSubform].[Form]![ScheduleEndTime], "Medium Time") & _
-
"#))"
-
-
Set rsClone = Me.RecordsetClone
-
rsClone.MoveFirst
-
rsClone.FindFirst strWhere
-
-
If rsClone.NoMatch Then
-
MsgBox ("test")
-
Cancel = True
-
Exit Sub
-
End If
-
End If
-
End Sub
Any help would be very nice indeed.
John
50 5479
Just to let the know the error is "Compile error: variable not defined"
try moving your code to the form's before update event. that is the correct event for your validation.
NeoPa 32,556
Expert Mod 16PB
As MSeda says, the code is from a - Private Sub Form_BeforeUpdate(Cancel As Integer)
-
End Sub
...procedure where the variable Cancel is predefined.
Trying to use this code in an OnCurrent event procedure will not work as there is no variable defined there for it to make sense. Hence your error message.
Ok i have moved the code to the before update event (thanks Mseda) but double bookings are still allowed (the code has no effect). Any ideas from anyone regarding the code.
Thanks
John
- Private Sub Form_Current()
-
If Me.NewRecord = True Then
-
Dim strWhere As String, strMessage As String
-
Dim rsClone As Recordset
-
-
strWhere = "(([BookingsSubform].Form![CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
-
") AND ([BookingsSubform].[Form]![ScheduleID]=" & Me.[BookingsSubform].[Form]![ScheduleID] & _
-
") AND ([BookingsTimeSubform].[Form]![ScheduleEndTime]>=#" & _
-
Format(Me.[BookingsTimeSubform].[Form]![ScheduleStartTime], "Medium Time") & _
-
"#) AND ([BookingsTimeSubform].[Form]![ScheduleStartTime]<=#" & _
-
Format(Me.[BookingsTimeSubform].[Form]![ScheduleEndTime], "Medium Time") & _
-
"#))"
-
-
Set rsClone = Me.RecordsetClone
-
rsClone.MoveFirst
-
rsClone.FindFirst strWhere
-
-
If rsClone.NoMatch Then
-
MsgBox ("test")
-
Cancel = True
-
Exit Sub
-
End If
-
End If
-
End Sub
Your search criteria is set up wrong. You're using [BookingsSubform].Form![CourtID] when you should be using the name of the field from the table.
In other words as Rabbit says ... -
-
strWhere = "(([CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
-
Mary
Sorry if i am a slow understandig what you guys mean but i am a newbie to VBA. Now i have this code: -
strWhere = "(([CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
-
") AND ([ScheduleDate]=" & Me.[BookingsSubform].[Form]![ScheduleDate] & _
-
") AND ([BookingsTimeSubform].[Form]![ScheduleEndTime]>=#" & _
-
Format(Me.[BookingsTimeSubform].[Form]![ScheduleStartTime], "Medium Time") & _
-
"#) AND ([BookingsTimeSubform].[Form]![ScheduleStartTime]<=#" & _
-
Format(Me.[BookingsTimeSubform].[Form]![ScheduleEndTime], "Medium Time") & _
-
"#))"
-
But it still doesnot work.
Any more ideas?
Thanks
John
-
AND [BookingsTimeSubform].[Form]![ScheduleEndTime]>=#" & _
-
Format(Me.[BookingsTimeSubform].[Form]![ScheduleStartTime], "Medium Time") & _
-
"#) AND ([BookingsTimeSubform].[Form]![ScheduleStartTime]<=#" & _
-
Format(Me.[BookingsTimeSubform].[Form]![ScheduleEndTime], "Medium Time") & _
-
All this is doing is comparing values on the form. What is it you are trying to achieve here. If you explain the logic of the criteria I can help further.
Mary
I am trying to not allow double bookings to be entered in the form. This involves not allowing a particular court to be booked on a particular date for a time that is already taken. For example
Court 1, 18/02/07, 11:00 AM - 01:00 PM and
Court 1, 18/02/07, 11:00 AM - 12:00 PM and
Court 1, 18/02/07, 11:00 AM - 01:00 PM should not be allowed as there are repeats and overlaps
But other entries such as
court 1, 18/02/07, 12:00 PM-01:00 PM and
court 1, 18/02/07, 01:00 PM-02:00 PM and
court 1, 18/02/07, 02:00 PM-05:00 PM should be allowed as there are no repeats or overlaps.
Hope that helps tell me if you need any more detail.
Thanks, John
Hi John,
Your code won't work because the RecordsetClone won't allow you to reference the start and end times of the bookings. Try the following instead: -
Private Sub Form_BeforeUpdate(Cancel As Integer)
-
Dim db as DAO.Database
-
Dim rs as DAO.Recordset
-
Dim strMessage As String
-
Dim startTime As Date
-
Dim endTime As Date
-
Dim test As Boolean
-
-
test = False
-
If Me.NewRecord = True Then
-
set db = CurrentDb
-
Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
-
"FROM tblSchedule INNER JOIN tblScheduleDetails " & _
-
"ON tblSchedule.ScheduleID=tblScheduleDetails.ScheduleID " & _
-
"WHERE [CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
-
" AND [ScheduleDate]=#" & Me.[BookingsSubform].[Form]![ScheduleDate] & "#)"
-
-
startTime = Me.[BookingsTimeSubform].[Form]![ScheduleStartTime]
-
endTime = Me.[BookingsTimeSubform].[Form]![ScheduleEndTime]
-
If rs.RecordCount=0 Then Exit Sub
-
rs.MoveFirst
-
Do Until rs.EOF
-
If startTime=rs!ScheduleStartTime Or endTime=rs!ScheduleEndTime Then
-
test = True
-
ElseIf startTime>rs!ScheduleStartTime And startTime<rs!ScheduleEndTime Then
-
test = True
-
ElseIf endTime<rs!ScheduleEndTime And endTime>rs!ScheduleStartTime Then
-
test = True
-
End If
-
If test Then
-
rs.MoveLast
-
Else
-
rs.MoveNext
-
End If
-
Loop
-
-
If test=False Then
-
MsgBox ("Time is available")
-
Else
-
MsgBox ("Time is unavailable")
-
Cancel = True
-
End If
-
End If
-
-
rs.Close
-
Set rs=Nothing
-
Set db=Nothing
-
-
End Sub
-
I really think there has to be a better way to do this though.
Mary
Mary, thanks for your time on this. -
Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
-
"FROM tblSchedule INNER JOIN tblScheduleDetails " & _
-
"ON tblSchedule.ScheduleID=tblScheduleDetails.Schedule ID " & _
-
"WHERE [CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
-
" AND [ScheduleDate]=#" & Me.[BookingsSubform].[Form]![ScheduleDate] & "#)"
-
This code was highlighted in red with the error "compile error: expected: list seperator or )". I couldnt solve this sorry to be a pain.
John
Sorry my fault the ending bracket should be outside the quote as follows: -
Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
-
"FROM tblSchedule INNER JOIN tblScheduleDetails " & _
-
"ON tblSchedule.ScheduleID=tblScheduleDetails.Schedule ID " & _
-
"WHERE [CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
-
" AND [ScheduleDate]=#" & Me.[BookingsSubform].[Form]![ScheduleDate] & "#")
-
Im sorry to say the code has no effect. I dont know what else to try.
Any ideas?
Thanks for your time anyway.
John
Try removing it from the form event altogether and put it in the after update event of the ScheduleEndTime control instead with some small amendments as follows: -
Private Sub ScheduleEndTime_AfterUpdate()
-
Dim db as DAO.Database
-
Dim rs as DAO.Recordset
-
Dim strMessage As String
-
Dim startTime As Date
-
Dim endTime As Date
-
Dim test As Boolean
-
-
test = False
-
If Me.NewRecord = True Then
-
set db = CurrentDb
-
Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
-
"FROM tblSchedule INNER JOIN tblScheduleDetails " & _
-
"ON tblSchedule.ScheduleID=tblScheduleDetails.Schedule ID " & _
-
"WHERE [CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
-
" AND [ScheduleDate]=#" & Me.[BookingsSubform].[Form]![ScheduleDate] & "#)"
-
-
startTime = Me.[BookingsTimeSubform].[Form]![ScheduleStartTime]
-
endTime = Me.[BookingsTimeSubform].[Form]![ScheduleEndTime]
-
If rs.RecordCount=0 Then Exit Sub
-
rs.MoveFirst
-
Do Until rs.EOF
-
If startTime=rs!ScheduleStartTime Or endTime=rs!ScheduleEndTime Then
-
test = True
-
ElseIf startTime>rs!ScheduleStartTime And startTime<rs!ScheduleEndTime Then
-
test = True
-
ElseIf endTime<rs!ScheduleEndTime And endTime>rs!ScheduleStartTime Then
-
test = True
-
End If
-
If test Then
-
rs.MoveLast
-
Else
-
rs.MoveNext
-
End If
-
Loop
-
-
If test=False Then
-
MsgBox ("Time is available")
-
Else
-
MsgBox ("Time is unavailable")
-
Me.ScheduleStartTime = Null
-
Me.ScheduleEndTime = Null
-
End If
-
End If
-
-
rs.Close
-
Set rs=Nothing
-
Set db=Nothing
-
-
End Sub
Thanks for your continued help. -
Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
-
"FROM tblSchedule INNER JOIN tblScheduleDetails " & _
-
"ON tblSchedule.ScheduleID=tblScheduleDetails.ScheduleID " & _
-
"WHERE [CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
-
" AND [ScheduleDate]=#" & Me.[BookingsSubform].[Form]![ScheduleDate] & "#")
-
is getting an error of "run time error 2465, MS access can not find the field 'I' referred to in your expression."
is getting an error of "run time error 91, object variable or With block variable not set."
John
Thanks for your continued help. -
Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
-
"FROM tblSchedule INNER JOIN tblScheduleDetails " & _
-
"ON tblSchedule.ScheduleID=tblScheduleDetails.ScheduleID " & _
-
"WHERE [CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
-
" AND [ScheduleDate]=" & Me.[BookingsSubform].[Form]![ScheduleDate])
-
is getting an error of "run time error 2465, MS access can not find the field 'I' referred to in your expression."
Try running this query on it's own but make sure the form is opened in the background. First be sure to chang [MainForm] to the name of your main form. Let me know what happens. -
SELECT [ScheduleStartTime], [ScheduleEndTime]
-
FROM tblSchedule INNER JOIN tblScheduleDetailsON tblSchedule.ScheduleID=tblScheduleDetails.ScheduleID
-
WHERE [CourtID]=[Forms]![MainForm]![BookingsSubform].Form![CourtID]
-
AND [ScheduleDate]= [Forms]![MainForm]![BookingsSubform].[Form]![ScheduleDate]
is getting an error of "run time error 91, object variable or With block variable not set."
John
Change the last few lines of code from this ... -
End If
-
End If
-
-
rs.Close
-
Set rs=Nothing
-
Set db=Nothing
-
-
End Sub
-
to this ... -
End If
-
rs.Close
-
Set rs=Nothing
-
Set db=Nothing
-
End If
-
-
End Sub
-
I tried to create the query in SQL view with bookings instead of mainform is that right? But i could not save it due to an error of "Syntax error in FROM clause".
John
I tried to create the query in SQL view with bookings instead of mainform is that right? But i could not save it due to an error of "Syntax error in FROM clause".
John
Try this ... -
SELECT [ScheduleStartTime], [ScheduleEndTime]
-
FROM tblSchedule INNER JOIN tblScheduleDetails
-
ON tblSchedule.[ScheduleID]=tblScheduleDetails.[ScheduleID]
-
WHERE [CourtID]=[Forms]![MainForm]![BookingsSubform].Form![CourtID]
-
AND [ScheduleDate]= [Forms]![MainForm]![BookingsSubform].[Form]![ScheduleDate]
-
There were a couple of spacing errors
I did what you said and got an error of "The Microsoft Jet database engine cannot find the input table or query tblSchedule. Make sure it exists and that its name is spelled correctly. (Error 3078)" when trying to run the query.
I have checked spellings and they are correct.
John
I did what you said and got an error of "The Microsoft Jet database engine cannot find the input table or query tblSchedule. Make sure it exists and that its name is spelled correctly. (Error 3078)" when trying to run the query.
I have checked spellings and they are correct.
John
That is very strange John. Try opening the table and see if it opens alright and if the data looks OK.
Mary
That is very strange John. Try opening the table and see if it opens alright and if the data looks OK.
Mary
You could try this test.
Create a new query in access design view and add the tblSchedule and tblSchedule details. Then just drag down the appropriate fields and change the view to SQL query view and see what might be going on with the table name.
Ok it likes schedule on its own not tblschedule. Thanks this gives me a list of doubles bookings, is there any way I can stop these from being entered in the first place.
Thanks
John
Ok it likes schedule on its own not tblschedule. Thanks this gives me a list of doubles bookings, is there any way I can stop these from being entered in the first place.
Thanks
John
Did you move the code to the After Update event of the ScheduleStartTime control on the form?
Mary
Yes i moved it to the after update event. I am still getting this error "run time error 2465, MS access can not find the field 'I' referred to in your expression."
John
Sorry no i havent moved that code yet. Which part do i replace
i know its a silly question but i had ago and the code went red.
John
Sorry no i havent moved that code yet. Which part do i replace
i know its a silly question but i had ago and the code went red.
John
Put this in the After Update event of the ScheduleEndTime control -
Private Sub ScheduleEndTime_AfterUpdate()
-
Dim db as DAO.Database
-
Dim rs as DAO.Recordset
-
Dim strMessage As String
-
Dim startTime As Date
-
Dim endTime As Date
-
Dim test As Boolean
-
-
test = False
-
If Me.NewRecord = True Then
-
set db = CurrentDb
-
Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
-
"FROM Schedule INNER JOIN tblScheduleDetails " & _
-
"ON Schedule.ScheduleID=tblScheduleDetails.Schedule ID " & _
-
"WHERE [CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
-
" AND [ScheduleDate]=#" & Me.[BookingsSubform].[Form]![ScheduleDate] & "#)"
-
-
startTime = Me.[BookingsTimeSubform].[Form]![ScheduleStartTime]
-
endTime = Me.[BookingsTimeSubform].[Form]![ScheduleEndTime]
-
If rs.RecordCount=0 Then Exit Sub
-
rs.MoveFirst
-
Do Until rs.EOF
-
If startTime=rs!ScheduleStartTime Or endTime=rs!ScheduleEndTime Then
-
test = True
-
ElseIf startTime>rs!ScheduleStartTime And startTime<rs!ScheduleEndTime Then
-
test = True
-
ElseIf endTime<rs!ScheduleEndTime And endTime>rs!ScheduleStartTime Then
-
test = True
-
End If
-
If test Then
-
rs.MoveLast
-
Else
-
rs.MoveNext
-
End If
-
Loop
-
-
If test=False Then
-
MsgBox ("Time is available")
-
Else
-
MsgBox ("Time is unavailable")
-
Me.ScheduleStartTime = Null
-
Me.ScheduleEndTime = Null
-
End If
-
-
rs.Close
-
Set rs=Nothing
-
Set db=Nothing
-
-
End If
-
-
End Sub
-
Sorry been away for a few days.
For this code: -
Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
-
"FROM Schedule INNER JOIN ScheduleDetails " & _
-
"ON Schedule.ScheduleID=ScheduleDetails.ScheduleID " & _
-
"WHERE [CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
-
"AND [ScheduleDate]=#" & Me.[BookingsSubform].[Form]![ScheduleDate] & "#")
-
I am getting an error of "run time error 2465, MS access cant find the field 'I' referred to in your expression."
While i was away i created a new query that showed the double bookings. Is there any way I can validate these fields using this query as data is entered.
Thanks
johnblack
Check that all the field names are exactly correct and that the subform object is called BookingsSubform (you will find this by checking the object properties under other for Name).
If they are all correct then try this. I have made one minor change. -
Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
-
"FROM Schedule INNER JOIN ScheduleDetails " & _
-
"ON Schedule.ScheduleID=ScheduleDetails.ScheduleID " & _
-
"WHERE [CourtID]=" & Me.[BookingsSubform].Form![CourtID] & _
-
"AND [ScheduleDate]=#" & Me.[BookingsSubform].Form![ScheduleDate] & "#")
-
Mary
Hello again mary.
Im sorry to say I have more bad news. I did what you said and am still getting the same error as before. When i click debug the yellow arrow is pointing at this line: -
"AND [ScheduleDate]=#" & Me.[BookingsSubform].Form![ScheduleDate] & "#")
-
Any more ideas?
I could send you the database if you want.....
johnblack
Hello again mary.
Im sorry to say I have more bad news. I did what you said and am still getting the same error as before. When i click debug the yellow arrow is pointing at this line: -
"AND [ScheduleDate]=#" & Me.[BookingsSubform].Form![ScheduleDate] & "#")
-
Any more ideas?
I could send you the database if you want.....
johnblack
John
Why has your user profile changed? Which should I send a PM to?
Mary
Mary.
I had a problem with the johnblack one so I set up a new one but then got the old one working again.
So pm the johnblack one if you want or other one up 2 you.
John
Mary.
I had a problem with the johnblack one so I set up a new one but then got the old one working again.
So pm the johnblack one if you want or other one up 2 you.
John
Since you are currently logged in as atc I'll send it there.
Mary
NeoPa 32,556
Expert Mod 16PB
Hello again mary.
Im sorry to say I have more bad news. I did what you said and am still getting the same error as before. When i click debug the yellow arrow is pointing at this line: -
"AND [ScheduleDate]=#" & Me.[BookingsSubform].Form![ScheduleDate] & "#")
-
Any more ideas?
I could send you the database if you want.....
johnblack
That's because the previous line doesn't include a separator (space) after the previous control reference in the WHERE clause.
NeoPa 32,556
Expert Mod 16PB
The code should instead read : - Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
-
"FROM Schedule INNER JOIN ScheduleDetails " & _
-
"ON Schedule.ScheduleID=ScheduleDetails.ScheduleID " & _
-
"WHERE [CourtID]=" & Me.[BookingsSubform].Form![CourtID] & " " & _
-
"AND [ScheduleDate]=#" & Me.[BookingsSubform].Form![ScheduleDate] & "#")
Good catch, how did I miss that? :D
Mary did you receive the file in the end.
John
Mary did you receive the file in the end.
John
No John the second email had no attachment either.
Mary
No John the second email had no attachment either.
Mary
There were a couple of problems John.
1. You were trying to refer to controls on SubformA from SubformB so you can't use the Me. you need the full reference.
2. The rs.MoveNext has to be removed from the IF statement as it runs regardless of the whether the test is true or not.
Code should now be as follows: -
Private Sub ScheduleEndTime_AfterUpdate()
-
Dim db As DAO.Database
-
Dim rs As DAO.Recordset
-
Dim strMessage As String
-
Dim startTime As Date
-
Dim endTime As Date
-
Dim test As Boolean
-
-
test = False
-
If Me.NewRecord = True Then
-
Set db = CurrentDb
-
Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
-
"FROM Schedule INNER JOIN ScheduleDetails " & _
-
"ON Schedule.ScheduleID=ScheduleDetails.ScheduleID " & _
-
"WHERE [CourtID]=" & Forms![Bookings]![BookingsSubform].Form![CourtID] & " " & _
-
"AND [ScheduleDate]=#" & Forms![Bookings]![BookingsSubform].Form![ScheduleDate] & "#")
-
-
startTime = Me.ScheduleStartTime
-
endTime = Me.ScheduleEndTime
-
If rs.RecordCount = 0 Then Exit Sub
-
rs.MoveFirst
-
Do Until rs.EOF
-
If startTime = rs!ScheduleStartTime Or endTime = rs!ScheduleEndTime Then
-
test = True
-
ElseIf startTime > rs!ScheduleStartTime And startTime < rs!ScheduleEndTime Then
-
test = True
-
ElseIf endTime < rs!ScheduleEndTime And endTime > rs!ScheduleStartTime Then
-
test = True
-
End If
-
If test Then
-
rs.MoveLast
-
End If
-
rs.MoveNext
-
Loop
-
-
If test = False Then
-
MsgBox ("Time is available")
-
Else
-
MsgBox ("Time is unavailable")
-
Me.ScheduleStartTime = Null
-
Me.ScheduleEndTime = Null
-
End If
-
-
rs.Close
-
Set rs = Nothing
-
Set db = Nothing
-
-
End If
-
-
End Sub
-
Mary
Thanks so much it works like a charm. Would it be possible for you to try and solve one other small problem. At the moment I have this code -
Private Sub Form_AfterUpdate(Cancel As Integer)
-
Dim strWhere As String
-
Dim varKey As Variant
-
-
strWhere = "(CourtID=" & Me.CourtID & ") AND " & _
-
"(ScheduleDate=" & Format(Me.ScheduleDate, "\#m/d/yyyy\#") & ")"
-
varKey = DLookup("[CourtID] & Format([ScheduleDate],' \- d mmm yyyy')", _
-
"[Courts]", _
-
strWhere)
-
If Not IsNull(varKey) Then
-
Cancel = True
-
Call MsgBox(strWhere & vbCrLf & varKey)
-
Debug.Print strWhere & vbCrLf & varKey
-
End If
-
-
End Sub
-
On the bookingsubform. This is to try and stop the same court and scheduledate being repeated in different records. Any ideas what is wrong?
ATC
By this I mean court 1 on 01/03/2007, court 2 on 01/03/2007, court 1 on 02/03/2007 etc would be acceptable records.
But court 1 on 01/03/2007 and court 1 on 01/03/2007 would not be.
Thanks
ATC
By this I mean court 1 on 01/03/2007, court 2 on 01/03/2007, court 1 on 02/03/2007 etc would be acceptable records.
But court 1 on 01/03/2007 and court 1 on 01/03/2007 would not be.
Thanks
ATC
Try switching it to the Before Update event.
Mary
NeoPa 32,556
Expert Mod 16PB
By this I mean court 1 on 01/03/2007, court 2 on 01/03/2007, court 1 on 02/03/2007 etc would be acceptable records.
But court 1 on 01/03/2007 and court 1 on 01/03/2007 would not be.
Thanks
ATC
You haven't said what is going wrong with this but, as Mary says, there is no Cancel parameter in the AfterUpdate event.
The idea is that you check the data before updating and, optionally, cancel the update. It would not make too much sense to try this after the update has already been applied.
Thanks for your help. I
have now moved it to the before update event. When I try to input court 1, 01/03/2007 for example (not a duplicate). I get a message of "(CourtID=1) AND (ScheduleDate=#3/1/2007#) 1 - 1 Mar 2007".
I click OK and then have to press escape to exit the cell (cant click out of it). This takes the datasheet to what it was before. So i cant add new records or edit records.
Any ideas?
ATC
Thanks for your help. I
have now moved it to the before update event. When I try to input court 1, 01/03/2007 for example (not a duplicate). I get a message of "(CourtID=1) AND (ScheduleDate=#3/1/2007#) 1 - 1 Mar 2007".
I click OK and then have to press escape to exit the cell (cant click out of it). This takes the datasheet to what it was before. So i cant add new records or edit records.
Any ideas?
ATC
Try this... -
Private Sub ScheduleDate_AfterUpdate()
-
If Not IsNull(Me.CourtID) Then
-
CheckDate
-
End If
-
End Sub
-
-
Private Sub CourtID_AfterUpdate()
-
If Not IsNull(Me.ScheduleDate) Then
-
CheckDate
-
End If
-
End Sub
-
-
Function CheckDate()
-
Dim strWhere As String
-
Dim varKey As Variant
-
-
strWhere = "(CourtID=" & Me.CourtID & ") AND " & _
-
"(ScheduleDate=" & Format(Me.ScheduleDate, "\#m/d/yyyy\#") & ")"
-
varKey = DLookup("[CourtID] & Format([ScheduleDate],' \- d mmm yyyy')", _
-
"[Courts]", _
-
strWhere)
-
If Not IsNull(varKey) Then
-
Call MsgBox(strWhere & vbCrLf & varKey)
-
Debug.Print strWhere & vbCrLf & varKey
-
Me.ScheduleDate = Null
-
End If
-
-
End Function
-
Mary
Thanks for your continued help.
With this code is place (entering the data courtID of 1 and a date of 01/03/2007 - not a duplicate) I get the same message as before but when I click ok it keeps the courtID of the new record, deletes the schedule date and moves to courtID of a new record below.
I also get the same result if the data is a duplicate.
ATC
NeoPa 32,556
Expert Mod 16PB
John,
This is not really the thread to discuss this code.
I suggested that code in a completely different thread and half of it is simply to give debug information. It is not finished code. To work with someone else on this code is not good. How do you expect me to progress it with you if you get changes from outside sources.
If you want me to stop then please just let me know, otherwise I would appreciate being given the chance to deal with it without extraneous influences complicating matters even further (back in the original thread). Quite apart from anything else, the original thread will be no use as a reference if it stops dead in it's tracks half-done.
Ok sorry NeoPa i thought two heads may be better than one. I'll post in the orginal thread.
john
NeoPa 32,556
Expert Mod 16PB
Ok sorry NeoPa i thought two heads may be better than one. I'll post in the orginal thread.
john
Thank you.
( Stop Duplicates) in case anyone wants to come over.
It's probably best though, within such a tight debugging loop as this one, that the two of us focus together unless and until we find we can't sort it. Extra heads in a situation like this is more likely to distract than help.
LOL - Are you calling me a distraction Ade.
John
In this case he is quite correct as I was unaware of the other thread I was missing half the information. NeoPa will call me in if he needs any futher assistance although I doubt it. He's really quite good just don't tell him I said that.
Mary
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Grant |
last post by:
Hi
I have a database which logs the usage of rooms. Some booking are entered
well in advance, and some have stays of more than six months.
I would like to ensure that rooms which have been...
|
by: Andy |
last post by:
Hi folks
I teach. At school, four IT rooms are booked using a paper based outline
timetable. Completing it is easy but basic and impossible to ensure
completion of all fields (name, year...
|
by: markymark34 |
last post by:
Im messing around trying to learn Access and have hit a problem.
I have a table called tblbookings and i want to seach though it when trying to make a new booking to make sure the room isnt alrady...
|
by: ewan89 |
last post by:
I am creating a tennis court booking system. I am trying to create a
macro that will not allow the same court to be booked on the same date
at the same time.
I have the following tables and...
|
by: NDayave |
last post by:
How do,
I have a access 2000 booking database with Personal Details, Outing Details and the Bookings in three tables:
tblBookings -- -Autonumber, -Number, -Number, -Currency, -Yes/No, -Yes/No
...
|
by: iazahoor |
last post by:
I use Access 2000 and I'm trying to figure out what VBA code to use to
prevent double bookings. Any help on this matter will be greatly
appreciated. Regards Immy
|
by: anotherjoe |
last post by:
Question 1:
I have reservation database in access 2007 I have a cabin information table with:
cabinid (primarykey)
cabinname(text)
cabinlocation(text)
I have a customer table with:...
|
by: Keyboyx |
last post by:
I am creating a Dentist Booking form, for my A level project.
I have the following Tables:
Appointments
Appointment ID
Dentist ID
Patient ID
Appointment Date
Appointment Time
|
by: Wayne |
last post by:
I'm building a bookings database for trucks that among other things
captures the TruckName, LoadDate, LoadTime, UnloadDate and UnloadTime.
Is there a simple way to prevent double bookings for any...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |