If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help with dates -
I re write this question as I think i failed to provide enought information
for my previous helpers to help me completely (that and the fact it's done my head in) Thank you by the Way those guys. o.k Here goes . I have a table Called "tEvent" which stores the start date and an end date of a booking plus whom has the booking, the fields are named [Start] , [End] & [LocationId] - I have am trying to check dates as i select them from a calender routine filling in a field on my form , i would like to check this date to ensure it is not already booked on any date in "tEvent" between existing "Start" and "Finish" dates then either provide an error message or proceed to the the bookings end date and again checking its validity. i'm a newbie so please give it to me easy and simple.the form i'm using is called [Booking Application] and it contains two fields with calender buttons returning the chosen dates. HELP !!!! PLEASE !!!! |
#2
|
|||
|
|||
Help with dates -
Hi Wobbles
I am assuming that your form has the following names for the controls: [Start] calStart [End] calEnd User txtUser LocationID cboLocationID (this is a combo box - that looks up the locations from a separate table) If you select the form in design view & open the property window & select the Event tab, you should see an event called "Before Update". If you then click on the ellipsis (...) & choose code builder the VBA Editor will open. You should see two lines of code like this: Private Sub Form_BeforeUpdate(Cancel As Integer) End Sub Copy & paste the following code between the xxxxxxxxxxxxxxxxxxxxx's xxxxxxxxxxxxxxxxxxxxxxxxxxxx Dim cnn As ADODB.Connection Dim rs As ADODB.Recordset Dim strSQL As String 'create new instances of the connection to the DB and the recordset for the table tEvent Set cnn = New ADODB.Connection Set cnn = CurrentProject.Connection Set rs = New ADODB.Recordset 'string expression to select all fields from the table tEvent but limited to the location specified _ in the Location combo box and ordered by the Start Date strSQL = "SELECT * FROM tEvent WHERE [LocationID] = " & Me.cboLocationID & " ORDER BY [Start] ASC" With rs 'open the recordset based upon the string expression above .ActiveConnection = cnn .CursorLocation = adUseServer .CursorType = adOpenKeyset .LockType = adLockReadOnly .Open strSQL, , , , adCmdText 'start st the first record .MoveFirst 'loop through all the records returned, and check to see if there are any overlaps in dates _ for the specified location 'if there is an overlap, then generate a msg for the user and cancel the update Do 'there are three possibilities for an overlap '(1) the new start date is before or equal to the pre-existing end date _ and is also after or equal to the pre-existing Start Date '(2) the new end date is between (or equals) the pre-existing end date & start date '(3) the new start date is before or equals the pre-existing start date and the _ end date is is after or the same as the pre-existing end date If Me.calStart.Value = ![End] And Me.calStart.Value = ![Start] Then MsgBox "sorry, this booking overlaps with a pre-existing booking.", vbOKOnly + vbExclamation, "Overlap" Cancel = True End If If Me.calEnd.Value = ![Start] And Me.calEnd.Value = ![End] Then MsgBox "sorry, this booking overlaps with a pre-existing booking.", vbOKOnly + vbExclamation, "Overlap" Cancel = True End If If Me.calStart.Value = ![Start] And Me.calEnd.Value = ![End] Then MsgBox "sorry, this booking overlaps with a pre-existing booking.", vbOKOnly + vbExclamation, "Overlap" Cancel = True End If 'go to the next record & continue looping unless have reached EOF .MoveNext Loop Until .EOF End With 'having done all teh checks, just clean up If rs.State adStateClosed Then rs.Close End If If cnn.State adStateClosed Then cnn.Close End If Set rs = Nothing Set cnn = Nothing xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx You may have to amend the control names in the code to suit the names that you have used for the various controls. Basically, what this code does is before a new record is saved, (the Before Update event happens before a record is saved - so it can be cancelled easily), it does a lookup in the underlying table for the selected Location (cboLocationID) and then runs a series of checks against each of the records that have been returned (these are previous records that have already been saved - not the new one). If there is an overlap, thena message box is generated advising the user and the update is cancelled (Cancel = True) That Do ... Loop is just there to loop through all the records that have been returned. The final bit down the bottom of the code is just there to close the recordset & the connection & release resources. I know I went into a lot of detail, but you wanted it step by step. If you need any extra help, just yell. Hope this helps. Kind regards Ross Petersen "Wobbles" wrote: I re write this question as I think i failed to provide enought information for my previous helpers to help me completely (that and the fact it's done my head in) Thank you by the Way those guys. o.k Here goes . I have a table Called "tEvent" which stores the start date and an end date of a booking plus whom has the booking, the fields are named [Start] , [End] & [LocationId] - I have am trying to check dates as i select them from a calender routine filling in a field on my form , i would like to check this date to ensure it is not already booked on any date in "tEvent" between existing "Start" and "Finish" dates then either provide an error message or proceed to the the bookings end date and again checking its validity. i'm a newbie so please give it to me easy and simple.the form i'm using is called [Booking Application] and it contains two fields with calender buttons returning the chosen dates. HELP !!!! PLEASE !!!! |
#3
|
|||
|
|||
Help with dates -
your logic to identify an overlap makes sense, Ross; suggest you tighten up
the Loop code to make it more efficient. once a single overlap is identified, there's no need to continue checking those particular dates by running subsequent If statements, or checking additional records in the recordset. how about modifying the code inside your Do...Loop a bit, as If Me.calStart.Value = ![End] And _ Me.calStart.Value = ![Start] Then MsgBox "sorry, this booking overlaps with a pre-existing " _ & "booking.", vbOKOnly + vbExclamation, "Overlap" Cancel = True Exit Do ElseIf Me.calEnd.Value = ![Start] And _ Me.calEnd.Value = ![End] Then MsgBox "sorry, this booking overlaps with a pre-existing " _ & "booking.", vbOKOnly + vbExclamation, "Overlap" Cancel = True Exit Do ElseIf Me.calStart.Value = ![Start] And _ Me.calEnd.Value = ![End] Then MsgBox "sorry, this booking overlaps with a pre-existing " _ & "booking.", vbOKOnly + vbExclamation, "Overlap" Cancel = True Exit Do End If .MoveNext hth "Ross" wrote in message ... Hi Wobbles I am assuming that your form has the following names for the controls: [Start] calStart [End] calEnd User txtUser LocationID cboLocationID (this is a combo box - that looks up the locations from a separate table) If you select the form in design view & open the property window & select the Event tab, you should see an event called "Before Update". If you then click on the ellipsis (...) & choose code builder the VBA Editor will open. You should see two lines of code like this: Private Sub Form_BeforeUpdate(Cancel As Integer) End Sub Copy & paste the following code between the xxxxxxxxxxxxxxxxxxxxx's xxxxxxxxxxxxxxxxxxxxxxxxxxxx Dim cnn As ADODB.Connection Dim rs As ADODB.Recordset Dim strSQL As String 'create new instances of the connection to the DB and the recordset for the table tEvent Set cnn = New ADODB.Connection Set cnn = CurrentProject.Connection Set rs = New ADODB.Recordset 'string expression to select all fields from the table tEvent but limited to the location specified _ in the Location combo box and ordered by the Start Date strSQL = "SELECT * FROM tEvent WHERE [LocationID] = " & Me.cboLocationID & " ORDER BY [Start] ASC" With rs 'open the recordset based upon the string expression above .ActiveConnection = cnn .CursorLocation = adUseServer .CursorType = adOpenKeyset .LockType = adLockReadOnly .Open strSQL, , , , adCmdText 'start st the first record .MoveFirst 'loop through all the records returned, and check to see if there are any overlaps in dates _ for the specified location 'if there is an overlap, then generate a msg for the user and cancel the update Do 'there are three possibilities for an overlap '(1) the new start date is before or equal to the pre-existing end date _ and is also after or equal to the pre-existing Start Date '(2) the new end date is between (or equals) the pre-existing end date & start date '(3) the new start date is before or equals the pre-existing start date and the _ end date is is after or the same as the pre-existing end date If Me.calStart.Value = ![End] And Me.calStart.Value = ![Start] Then MsgBox "sorry, this booking overlaps with a pre-existing booking.", vbOKOnly + vbExclamation, "Overlap" Cancel = True End If If Me.calEnd.Value = ![Start] And Me.calEnd.Value = ![End] Then MsgBox "sorry, this booking overlaps with a pre-existing booking.", vbOKOnly + vbExclamation, "Overlap" Cancel = True End If If Me.calStart.Value = ![Start] And Me.calEnd.Value = ![End] Then MsgBox "sorry, this booking overlaps with a pre-existing booking.", vbOKOnly + vbExclamation, "Overlap" Cancel = True End If 'go to the next record & continue looping unless have reached EOF .MoveNext Loop Until .EOF End With 'having done all teh checks, just clean up If rs.State adStateClosed Then rs.Close End If If cnn.State adStateClosed Then cnn.Close End If Set rs = Nothing Set cnn = Nothing xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx You may have to amend the control names in the code to suit the names that you have used for the various controls. Basically, what this code does is before a new record is saved, (the Before Update event happens before a record is saved - so it can be cancelled easily), it does a lookup in the underlying table for the selected Location (cboLocationID) and then runs a series of checks against each of the records that have been returned (these are previous records that have already been saved - not the new one). If there is an overlap, thena message box is generated advising the user and the update is cancelled (Cancel = True) That Do ... Loop is just there to loop through all the records that have been returned. The final bit down the bottom of the code is just there to close the recordset & the connection & release resources. I know I went into a lot of detail, but you wanted it step by step. If you need any extra help, just yell. Hope this helps. Kind regards Ross Petersen "Wobbles" wrote: I re write this question as I think i failed to provide enought information for my previous helpers to help me completely (that and the fact it's done my head in) Thank you by the Way those guys. o.k Here goes . I have a table Called "tEvent" which stores the start date and an end date of a booking plus whom has the booking, the fields are named [Start] , [End] & [LocationId] - I have am trying to check dates as i select them from a calender routine filling in a field on my form , i would like to check this date to ensure it is not already booked on any date in "tEvent" between existing "Start" and "Finish" dates then either provide an error message or proceed to the the bookings end date and again checking its validity. i'm a newbie so please give it to me easy and simple.the form i'm using is called [Booking Application] and it contains two fields with calender buttons returning the chosen dates. HELP !!!! PLEASE !!!! |
#4
|
|||
|
|||
Help with dates -
Thanks Tina
Yes you are quite right! I just did that code very quickly as I was in a bit of a hurry at the time. I was taking my wife & her mum out soon after that. Wobbles, if you have a look at Tinas modifications - each If/Then/End If block now has an Exit Do statement. What that does is that if there is an overlap identified with a pre-existing record then the update is cancelled, so there is no need to go through all the other pre-existing records. If you have quite a few records for a given location, then that will save time in running this code & also there will not be the potential to have more than one Message Box generated if there is more than one overlap. Thanks Tina, appreciate your help. Kind regards Ross Petersen "tina" wrote: your logic to identify an overlap makes sense, Ross; suggest you tighten up the Loop code to make it more efficient. once a single overlap is identified, there's no need to continue checking those particular dates by running subsequent If statements, or checking additional records in the recordset. how about modifying the code inside your Do...Loop a bit, as If Me.calStart.Value = ![End] And _ Me.calStart.Value = ![Start] Then MsgBox "sorry, this booking overlaps with a pre-existing " _ & "booking.", vbOKOnly + vbExclamation, "Overlap" Cancel = True Exit Do ElseIf Me.calEnd.Value = ![Start] And _ Me.calEnd.Value = ![End] Then MsgBox "sorry, this booking overlaps with a pre-existing " _ & "booking.", vbOKOnly + vbExclamation, "Overlap" Cancel = True Exit Do ElseIf Me.calStart.Value = ![Start] And _ Me.calEnd.Value = ![End] Then MsgBox "sorry, this booking overlaps with a pre-existing " _ & "booking.", vbOKOnly + vbExclamation, "Overlap" Cancel = True Exit Do End If .MoveNext hth "Ross" wrote in message ... Hi Wobbles I am assuming that your form has the following names for the controls: [Start] calStart [End] calEnd User txtUser LocationID cboLocationID (this is a combo box - that looks up the locations from a separate table) If you select the form in design view & open the property window & select the Event tab, you should see an event called "Before Update". If you then click on the ellipsis (...) & choose code builder the VBA Editor will open. You should see two lines of code like this: Private Sub Form_BeforeUpdate(Cancel As Integer) End Sub Copy & paste the following code between the xxxxxxxxxxxxxxxxxxxxx's xxxxxxxxxxxxxxxxxxxxxxxxxxxx Dim cnn As ADODB.Connection Dim rs As ADODB.Recordset Dim strSQL As String 'create new instances of the connection to the DB and the recordset for the table tEvent Set cnn = New ADODB.Connection Set cnn = CurrentProject.Connection Set rs = New ADODB.Recordset 'string expression to select all fields from the table tEvent but limited to the location specified _ in the Location combo box and ordered by the Start Date strSQL = "SELECT * FROM tEvent WHERE [LocationID] = " & Me.cboLocationID & " ORDER BY [Start] ASC" With rs 'open the recordset based upon the string expression above .ActiveConnection = cnn .CursorLocation = adUseServer .CursorType = adOpenKeyset .LockType = adLockReadOnly .Open strSQL, , , , adCmdText 'start st the first record .MoveFirst 'loop through all the records returned, and check to see if there are any overlaps in dates _ for the specified location 'if there is an overlap, then generate a msg for the user and cancel the update Do 'there are three possibilities for an overlap '(1) the new start date is before or equal to the pre-existing end date _ and is also after or equal to the pre-existing Start Date '(2) the new end date is between (or equals) the pre-existing end date & start date '(3) the new start date is before or equals the pre-existing start date and the _ end date is is after or the same as the pre-existing end date If Me.calStart.Value = ![End] And Me.calStart.Value = ![Start] Then MsgBox "sorry, this booking overlaps with a pre-existing booking.", vbOKOnly + vbExclamation, "Overlap" Cancel = True End If If Me.calEnd.Value = ![Start] And Me.calEnd.Value = ![End] Then MsgBox "sorry, this booking overlaps with a pre-existing booking.", vbOKOnly + vbExclamation, "Overlap" Cancel = True End If If Me.calStart.Value = ![Start] And Me.calEnd.Value = ![End] Then MsgBox "sorry, this booking overlaps with a pre-existing booking.", vbOKOnly + vbExclamation, "Overlap" Cancel = True End If 'go to the next record & continue looping unless have reached EOF .MoveNext Loop Until .EOF End With 'having done all teh checks, just clean up If rs.State adStateClosed Then rs.Close End If If cnn.State adStateClosed Then cnn.Close End If Set rs = Nothing Set cnn = Nothing xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx You may have to amend the control names in the code to suit the names that you have used for the various controls. Basically, what this code does is before a new record is saved, (the Before Update event happens before a record is saved - so it can be cancelled easily), it does a lookup in the underlying table for the selected Location (cboLocationID) and then runs a series of checks against each of the records that have been returned (these are previous records that have already been saved - not the new one). If there is an overlap, thena message box is generated advising the user and the update is cancelled (Cancel = True) That Do ... Loop is just there to loop through all the records that have been returned. The final bit down the bottom of the code is just there to close the recordset & the connection & release resources. I know I went into a lot of detail, but you wanted it step by step. If you need any extra help, just yell. Hope this helps. Kind regards Ross Petersen "Wobbles" wrote: I re write this question as I think i failed to provide enought information for my previous helpers to help me completely (that and the fact it's done my head in) Thank you by the Way those guys. o.k Here goes . I have a table Called "tEvent" which stores the start date and an end date of a booking plus whom has the booking, the fields are named [Start] , [End] & [LocationId] - I have am trying to check dates as i select them from a calender routine filling in a field on my form , i would like to check this date to ensure it is not already booked on any date in "tEvent" between existing "Start" and "Finish" dates then either provide an error message or proceed to the the bookings end date and again checking its validity. i'm a newbie so please give it to me easy and simple.the form i'm using is called [Booking Application] and it contains two fields with calender buttons returning the chosen dates. HELP !!!! PLEASE !!!! |
#5
|
|||
|
|||
Help with dates -
no problem, Ross, i've had folks help me out the same way plenty of times.
"Ross" wrote in message news Thanks Tina Yes you are quite right! I just did that code very quickly as I was in a bit of a hurry at the time. I was taking my wife & her mum out soon after that. Wobbles, if you have a look at Tinas modifications - each If/Then/End If block now has an Exit Do statement. What that does is that if there is an overlap identified with a pre-existing record then the update is cancelled, so there is no need to go through all the other pre-existing records. If you have quite a few records for a given location, then that will save time in running this code & also there will not be the potential to have more than one Message Box generated if there is more than one overlap. Thanks Tina, appreciate your help. Kind regards Ross Petersen "tina" wrote: your logic to identify an overlap makes sense, Ross; suggest you tighten up the Loop code to make it more efficient. once a single overlap is identified, there's no need to continue checking those particular dates by running subsequent If statements, or checking additional records in the recordset. how about modifying the code inside your Do...Loop a bit, as If Me.calStart.Value = ![End] And _ Me.calStart.Value = ![Start] Then MsgBox "sorry, this booking overlaps with a pre-existing " _ & "booking.", vbOKOnly + vbExclamation, "Overlap" Cancel = True Exit Do ElseIf Me.calEnd.Value = ![Start] And _ Me.calEnd.Value = ![End] Then MsgBox "sorry, this booking overlaps with a pre-existing " _ & "booking.", vbOKOnly + vbExclamation, "Overlap" Cancel = True Exit Do ElseIf Me.calStart.Value = ![Start] And _ Me.calEnd.Value = ![End] Then MsgBox "sorry, this booking overlaps with a pre-existing " _ & "booking.", vbOKOnly + vbExclamation, "Overlap" Cancel = True Exit Do End If .MoveNext hth "Ross" wrote in message ... Hi Wobbles I am assuming that your form has the following names for the controls: [Start] calStart [End] calEnd User txtUser LocationID cboLocationID (this is a combo box - that looks up the locations from a separate table) If you select the form in design view & open the property window & select the Event tab, you should see an event called "Before Update". If you then click on the ellipsis (...) & choose code builder the VBA Editor will open. You should see two lines of code like this: Private Sub Form_BeforeUpdate(Cancel As Integer) End Sub Copy & paste the following code between the xxxxxxxxxxxxxxxxxxxxx's xxxxxxxxxxxxxxxxxxxxxxxxxxxx Dim cnn As ADODB.Connection Dim rs As ADODB.Recordset Dim strSQL As String 'create new instances of the connection to the DB and the recordset for the table tEvent Set cnn = New ADODB.Connection Set cnn = CurrentProject.Connection Set rs = New ADODB.Recordset 'string expression to select all fields from the table tEvent but limited to the location specified _ in the Location combo box and ordered by the Start Date strSQL = "SELECT * FROM tEvent WHERE [LocationID] = " & Me.cboLocationID & " ORDER BY [Start] ASC" With rs 'open the recordset based upon the string expression above .ActiveConnection = cnn .CursorLocation = adUseServer .CursorType = adOpenKeyset .LockType = adLockReadOnly .Open strSQL, , , , adCmdText 'start st the first record .MoveFirst 'loop through all the records returned, and check to see if there are any overlaps in dates _ for the specified location 'if there is an overlap, then generate a msg for the user and cancel the update Do 'there are three possibilities for an overlap '(1) the new start date is before or equal to the pre-existing end date _ and is also after or equal to the pre-existing Start Date '(2) the new end date is between (or equals) the pre-existing end date & start date '(3) the new start date is before or equals the pre-existing start date and the _ end date is is after or the same as the pre-existing end date If Me.calStart.Value = ![End] And Me.calStart.Value = ![Start] Then MsgBox "sorry, this booking overlaps with a pre-existing booking.", vbOKOnly + vbExclamation, "Overlap" Cancel = True End If If Me.calEnd.Value = ![Start] And Me.calEnd.Value = ![End] Then MsgBox "sorry, this booking overlaps with a pre-existing booking.", vbOKOnly + vbExclamation, "Overlap" Cancel = True End If If Me.calStart.Value = ![Start] And Me.calEnd.Value = ![End] Then MsgBox "sorry, this booking overlaps with a pre-existing booking.", vbOKOnly + vbExclamation, "Overlap" Cancel = True End If 'go to the next record & continue looping unless have reached EOF .MoveNext Loop Until .EOF End With 'having done all teh checks, just clean up If rs.State adStateClosed Then rs.Close End If If cnn.State adStateClosed Then cnn.Close End If Set rs = Nothing Set cnn = Nothing xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx You may have to amend the control names in the code to suit the names that you have used for the various controls. Basically, what this code does is before a new record is saved, (the Before Update event happens before a record is saved - so it can be cancelled easily), it does a lookup in the underlying table for the selected Location (cboLocationID) and then runs a series of checks against each of the records that have been returned (these are previous records that have already been saved - not the new one). If there is an overlap, thena message box is generated advising the user and the update is cancelled (Cancel = True) That Do ... Loop is just there to loop through all the records that have been returned. The final bit down the bottom of the code is just there to close the recordset & the connection & release resources. I know I went into a lot of detail, but you wanted it step by step. If you need any extra help, just yell. Hope this helps. Kind regards Ross Petersen "Wobbles" wrote: I re write this question as I think i failed to provide enought information for my previous helpers to help me completely (that and the fact it's done my head in) Thank you by the Way those guys. o.k Here goes . I have a table Called "tEvent" which stores the start date and an end date of a booking plus whom has the booking, the fields are named [Start] , [End] & [LocationId] - I have am trying to check dates as i select them from a calender routine filling in a field on my form , i would like to check this date to ensure it is not already booked on any date in "tEvent" between existing "Start" and "Finish" dates then either provide an error message or proceed to the the bookings end date and again checking its validity. i'm a newbie so please give it to me easy and simple.the form i'm using is called [Booking Application] and it contains two fields with calender buttons returning the chosen dates. HELP !!!! PLEASE !!!! |
#6
|
|||
|
|||
Help with dates -
Thanks Ross and Tina, I am away the fourthcoming week so i will have to wait
until the end of the month to put you wonderful solutions into action , i have every confidence they are going to solve my problems, Thanks once again ! "tina" wrote: no problem, Ross, i've had folks help me out the same way plenty of times. "Ross" wrote in message news Thanks Tina Yes you are quite right! I just did that code very quickly as I was in a bit of a hurry at the time. I was taking my wife & her mum out soon after that. Wobbles, if you have a look at Tinas modifications - each If/Then/End If block now has an Exit Do statement. What that does is that if there is an overlap identified with a pre-existing record then the update is cancelled, so there is no need to go through all the other pre-existing records. If you have quite a few records for a given location, then that will save time in running this code & also there will not be the potential to have more than one Message Box generated if there is more than one overlap. Thanks Tina, appreciate your help. Kind regards Ross Petersen "tina" wrote: your logic to identify an overlap makes sense, Ross; suggest you tighten up the Loop code to make it more efficient. once a single overlap is identified, there's no need to continue checking those particular dates by running subsequent If statements, or checking additional records in the recordset. how about modifying the code inside your Do...Loop a bit, as If Me.calStart.Value = ![End] And _ Me.calStart.Value = ![Start] Then MsgBox "sorry, this booking overlaps with a pre-existing " _ & "booking.", vbOKOnly + vbExclamation, "Overlap" Cancel = True Exit Do ElseIf Me.calEnd.Value = ![Start] And _ Me.calEnd.Value = ![End] Then MsgBox "sorry, this booking overlaps with a pre-existing " _ & "booking.", vbOKOnly + vbExclamation, "Overlap" Cancel = True Exit Do ElseIf Me.calStart.Value = ![Start] And _ Me.calEnd.Value = ![End] Then MsgBox "sorry, this booking overlaps with a pre-existing " _ & "booking.", vbOKOnly + vbExclamation, "Overlap" Cancel = True Exit Do End If .MoveNext hth "Ross" wrote in message ... Hi Wobbles I am assuming that your form has the following names for the controls: [Start] calStart [End] calEnd User txtUser LocationID cboLocationID (this is a combo box - that looks up the locations from a separate table) If you select the form in design view & open the property window & select the Event tab, you should see an event called "Before Update". If you then click on the ellipsis (...) & choose code builder the VBA Editor will open. You should see two lines of code like this: Private Sub Form_BeforeUpdate(Cancel As Integer) End Sub Copy & paste the following code between the xxxxxxxxxxxxxxxxxxxxx's xxxxxxxxxxxxxxxxxxxxxxxxxxxx Dim cnn As ADODB.Connection Dim rs As ADODB.Recordset Dim strSQL As String 'create new instances of the connection to the DB and the recordset for the table tEvent Set cnn = New ADODB.Connection Set cnn = CurrentProject.Connection Set rs = New ADODB.Recordset 'string expression to select all fields from the table tEvent but limited to the location specified _ in the Location combo box and ordered by the Start Date strSQL = "SELECT * FROM tEvent WHERE [LocationID] = " & Me.cboLocationID & " ORDER BY [Start] ASC" With rs 'open the recordset based upon the string expression above .ActiveConnection = cnn .CursorLocation = adUseServer .CursorType = adOpenKeyset .LockType = adLockReadOnly .Open strSQL, , , , adCmdText 'start st the first record .MoveFirst 'loop through all the records returned, and check to see if there are any overlaps in dates _ for the specified location 'if there is an overlap, then generate a msg for the user and cancel the update Do 'there are three possibilities for an overlap '(1) the new start date is before or equal to the pre-existing end date _ and is also after or equal to the pre-existing Start Date '(2) the new end date is between (or equals) the pre-existing end date & start date '(3) the new start date is before or equals the pre-existing start date and the _ end date is is after or the same as the pre-existing end date If Me.calStart.Value = ![End] And Me.calStart.Value = ![Start] Then MsgBox "sorry, this booking overlaps with a pre-existing booking.", vbOKOnly + vbExclamation, "Overlap" Cancel = True End If If Me.calEnd.Value = ![Start] And Me.calEnd.Value = ![End] Then MsgBox "sorry, this booking overlaps with a pre-existing booking.", vbOKOnly + vbExclamation, "Overlap" Cancel = True End If If Me.calStart.Value = ![Start] And Me.calEnd.Value = ![End] Then MsgBox "sorry, this booking overlaps with a pre-existing booking.", vbOKOnly + vbExclamation, "Overlap" Cancel = True End If 'go to the next record & continue looping unless have reached EOF .MoveNext Loop Until .EOF End With 'having done all teh checks, just clean up If rs.State adStateClosed Then rs.Close End If If cnn.State adStateClosed Then cnn.Close End If Set rs = Nothing Set cnn = Nothing xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx You may have to amend the control names in the code to suit the names that you have used for the various controls. Basically, what this code does is before a new record is saved, (the Before Update event happens before a record is saved - so it can be cancelled easily), it does a lookup in the underlying table for the selected Location (cboLocationID) and then runs a series of checks against each of the records that have been returned (these are previous records that have already been saved - not the new one). If there is an overlap, thena message box is generated advising the user and the update is cancelled (Cancel = True) That Do ... Loop is just there to loop through all the records that have been returned. The final bit down the bottom of the code is just there to close the recordset & the connection & release resources. I know I went into a lot of detail, but you wanted it step by step. If you need any extra help, just yell. Hope this helps. Kind regards Ross Petersen "Wobbles" wrote: I re write this question as I think i failed to provide enought information for my previous helpers to help me completely (that and the fact it's done my head in) Thank you by the Way those guys. o.k Here goes . I have a table Called "tEvent" which stores the start date and an end date of a booking plus whom has the booking, the fields are named [Start] , [End] & [LocationId] - I have am trying to check dates as i select them from a calender routine filling in a field on my form , i would like to check this date to ensure it is not already booked on any date in "tEvent" between existing "Start" and "Finish" dates then either provide an error message or proceed to the the bookings end date and again checking its validity. i'm a newbie so please give it to me easy and simple.the form i'm using is called [Booking Application] and it contains two fields with calender buttons returning the chosen dates. HELP !!!! PLEASE !!!! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
chart with dates on the x-axe | chriske | Charts and Charting | 1 | September 23rd, 2005 02:10 PM |
Missing dates? | Allen Browne | Running & Setting Up Queries | 6 | August 5th, 2005 02:34 AM |
One parameter for various columns of dates | Wade Taylor | Running & Setting Up Queries | 3 | July 8th, 2005 10:07 PM |
converting dates into numbers | kikilein | General Discussion | 2 | October 20th, 2004 07:33 PM |
2 Que's regarding dates (matching and subtracting) | jacob farino | General Discussion | 1 | October 11th, 2004 05:11 AM |