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 |
#11
|
|||
|
|||
Time checking problem
so do u suggest renaming the boxes?
if so how could the new code look - i really don't know which is bound to the box name and which is bound to the field in the code as stated below.. Dim strWhere As String Dim varResult As Variant Dim strMsg As String Const strcJetDateTime = "\#mm\/dd\/yyyy hh\:nn\:ss\#" If ((Me.TimeIn = Me.TimeIn.OldValue) And (Me.TimeOut = Me.TimeOut.OldValue) And (Me.RoomID = Me.RoomID.OldValue)) Or IsNull(Me.TimeIn) Or IsNull(Me.TimeOut) Or IsNull(Me.RoomID) Then 'do nothing Else strWhere = "(TimeIn " & Format(Me.TimeOut, strcJetDateTime) & ") AND " & Format(Me.TimeIn, strcJetDateTime) & " TimeOut) AND (RoomID = " & Me.RoomID & ")" Debug.Print strWhere varResult = DLookup("ID", "TBL_Main", strWhere) If Not IsNull(varResult) Then strMsg = "Clashes with booking # " & varResult & vbCrLf & "Continue anyway?" If MsgBox(strMsg, vbYesNo + vbDefaultButton2, "Double-booked") vbYes Then Cancel = True End If End If End If Thanks. Neil "Douglas J Steele" wrote in message ... FWIW, I always rename all controls on my forms. I'd have the textboxes named txtTimeIn and txtTimeOut, just to ensure that there's no confusion between the controls and the fields. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Neil M" wrote in message ... I found a small error in the coding and changed it but now when runs it erroes on this line; If ((Me.TimeIn = Me.TimeIn.OldValue) And (Me.TimeOut = Me.TimeOut.OldValue) And (Me.RoomID = Me.RoomID.OldValue)) Or IsNull(Me.TimeIn) Or IsNull(Me.TimeOut) Or IsNull(Me.RoomID) Then The first Me.TimeOut is highlighted on the error. I have check the box names and they are named TimeIn and TimeOut so it should be working? Regards, Neil "Neil M" wrote in message ... right here is the code for BeforeUpdate: Private Sub Form_BeforeUpdate(Cancel As Integer) Dim strWhere As String Dim varResult As Variant Dim strMsg As String Const strcJetDateTime = "\#mm\/dd\/yyyy hh\:nn\:ss\#" If ((Me.TimeIn = Me.TimeIn.OldValue) And _ (Me.TimeOut = Me.TimeOut.OldValue) And _ (Me.RoomID = Me.RoomID.OldValue)) Or IsNull(Me.TimeIn) _ Or IsNull(Me.EndDateTime) Or IsNull(Me.RoomID) Then 'do nothing Else strWhere = "(TimeIn " & Format(Me.TimeOut, strcJetDateTime) & ") AND " & Format(Me.TimeIn, strcJetDateTime) & " TimeOut) AND (RoomID = " & Me.RoomID & ")" Debug.Print strWhere varResult = DLookup("ID", "TBL_Main", strWhere) If Not IsNull(varResult) Then strMsg = "Clashes with booking # " & varResult & vbCrLf & "Continue anyway?" If MsgBox(strMsg, vbYesNo + vbDefaultButton2, "Double-booked") vbYes Then Cancel = True End If End If End If End Sub The TimeIn and TimeOut are both bound to their fields and are the boxes are named the same. Both are Time/Date but format is just Short Time so no they do not contain Date as far as I know as they use a input mask so the user inputs xx:xx They are also no events other than the once listed above (BeforeUpdate), should these be linked to the BeforeUpdate on the actual field or the form? Neil "Douglas J Steele" wrote in message ... I'm not sure you've really answered Allen's question. While you said that TimeIn and TimeOut are both Date/Time fields, do they contain both the date and time, or do they only contain the time? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Neil M" wrote in message ... Yes I have two fields - "TimeIn" and "TimeOut" are both Date/Time (Short Time format) I still get the error stating that the time in question is duplicated. Although it hasn't. The correct time is shown on the error displayed. Now I also get the debug window come up with the "Method or Data not found" and the following is highlighted (OldValue) in; If ((Me.TimeIn = Me.TimeIn.OldValue) And Any suggestions? Thanks, Neil "Allen Browne" wrote in message ... Add the line: Debug.Print strWhere just before the "varResult = " line. After it runs, open the Immediate Window (Ctrl+G) to see how it interpreted it. Use that in the WHERE clause of a query to see what's going on. Does the booking number that it reports make any sense? Does your table have 2 fields (named StartDateTime and EndDateTime in the example) that contain both the date and time? Are they Date/Time fields? -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Allen Browne" wrote in message ... Use the BeforeUpdate event of the *form* where you enter this data to provide the warning if there is an overlap. The basic concept is that 2 events overlap if: - A begins before B ends, AND - B begins before A ends. The form's BeforeUpdate event procedure will be something like this aircode: Private Sub Form_BeforeUpdate(Cancel As Integer) Dim strWhere As String Dim varResult As Variant Dim strMsg As String Const strcJetDateTime = "\#mm\/dd\/yyyy hh\:nn\:ss\#" If ((Me.StartDateTime = Me.StartDateTime.OldValue) AND _ (Me.EndDateTime = Me.EndDateTime.OldValue) AND _ (Me.RoomID = Me.RoomID.OldValue)) OR IsNull(Me.StartDateTime) _ OR IsNull(Me.EndDateTime) OR IsNull(Me.RoomID) Then 'do nothing Else strWhere = "(StartDateTime " & Format(Me.EndDateTime, strcJetDateTime) & _ ") AND " & Format(Me.StartDateTime, strcJetDateTime) & _ " EndDateTime) AND (RoomID = " & Me.RoomID & ") varResult = DLookup("BookingID", "tblBooking", strWhere) If Not IsNull(varResult) Then strMsg = "Clashes with booking # " & varResult & vbCrLf & "Continue anyway?" If MsgBox(strMsg, vbYesNo+vbDefaultButton2, "Double-booked") vbYes Then Cancel = True End If End If End If End Sub If you need to cope with open-ended bookings, or need to crosscheck all bookings against each other, see: Clashing Events/Appointments at: http://allenbrowne.com/appevent.html -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Neil M" wrote in message ... Hi all (yet again!) I have a problem with the way my database will have the check the following. Room Date Time In and Time Out I want to make sure that the inputter doesn't overlap any times on the same day for the room (there are 5 rooms) So say I have booked one room on 1/1/05 for 8:00am 9:00am, and I want to input a new record later on for the same day and room for 9:30am, I want the program to tell me that it has already been booked for that time. It can allow me to continue and book as at this stage I just want it to check the time for me? I would appreciate any suggestions or help as I do not really have a clue how this can be done? (my database knowledge is better than normal but not quite advanced!) Regards, Neil |
#12
|
|||
|
|||
Time checking problem
Realistically, you're using the textboxes everywhere in your code, not the
recordset fields. BTW, you've got a Debug.Print strWhere in your code. What does strWhere look like? (If you're not familiar with Debug.Print, use Ctrl-G to go to the Immediate Window. Copy a line or two to the clipboard, and paste it back here) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Neil M" wrote in message ... so do u suggest renaming the boxes? if so how could the new code look - i really don't know which is bound to the box name and which is bound to the field in the code as stated below.. Dim strWhere As String Dim varResult As Variant Dim strMsg As String Const strcJetDateTime = "\#mm\/dd\/yyyy hh\:nn\:ss\#" If ((Me.TimeIn = Me.TimeIn.OldValue) And (Me.TimeOut = Me.TimeOut.OldValue) And (Me.RoomID = Me.RoomID.OldValue)) Or IsNull(Me.TimeIn) Or IsNull(Me.TimeOut) Or IsNull(Me.RoomID) Then 'do nothing Else strWhere = "(TimeIn " & Format(Me.TimeOut, strcJetDateTime) & ") AND " & Format(Me.TimeIn, strcJetDateTime) & " TimeOut) AND (RoomID = " & Me.RoomID & ")" Debug.Print strWhere varResult = DLookup("ID", "TBL_Main", strWhere) If Not IsNull(varResult) Then strMsg = "Clashes with booking # " & varResult & vbCrLf & "Continue anyway?" If MsgBox(strMsg, vbYesNo + vbDefaultButton2, "Double-booked") vbYes Then Cancel = True End If End If End If Thanks. Neil "Douglas J Steele" wrote in message ... FWIW, I always rename all controls on my forms. I'd have the textboxes named txtTimeIn and txtTimeOut, just to ensure that there's no confusion between the controls and the fields. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Neil M" wrote in message ... I found a small error in the coding and changed it but now when runs it erroes on this line; If ((Me.TimeIn = Me.TimeIn.OldValue) And (Me.TimeOut = Me.TimeOut.OldValue) And (Me.RoomID = Me.RoomID.OldValue)) Or IsNull(Me.TimeIn) Or IsNull(Me.TimeOut) Or IsNull(Me.RoomID) Then The first Me.TimeOut is highlighted on the error. I have check the box names and they are named TimeIn and TimeOut so it should be working? Regards, Neil "Neil M" wrote in message ... right here is the code for BeforeUpdate: Private Sub Form_BeforeUpdate(Cancel As Integer) Dim strWhere As String Dim varResult As Variant Dim strMsg As String Const strcJetDateTime = "\#mm\/dd\/yyyy hh\:nn\:ss\#" If ((Me.TimeIn = Me.TimeIn.OldValue) And _ (Me.TimeOut = Me.TimeOut.OldValue) And _ (Me.RoomID = Me.RoomID.OldValue)) Or IsNull(Me.TimeIn) _ Or IsNull(Me.EndDateTime) Or IsNull(Me.RoomID) Then 'do nothing Else strWhere = "(TimeIn " & Format(Me.TimeOut, strcJetDateTime) & ") AND " & Format(Me.TimeIn, strcJetDateTime) & " TimeOut) AND (RoomID = " & Me.RoomID & ")" Debug.Print strWhere varResult = DLookup("ID", "TBL_Main", strWhere) If Not IsNull(varResult) Then strMsg = "Clashes with booking # " & varResult & vbCrLf & "Continue anyway?" If MsgBox(strMsg, vbYesNo + vbDefaultButton2, "Double-booked") vbYes Then Cancel = True End If End If End If End Sub The TimeIn and TimeOut are both bound to their fields and are the boxes are named the same. Both are Time/Date but format is just Short Time so no they do not contain Date as far as I know as they use a input mask so the user inputs xx:xx They are also no events other than the once listed above (BeforeUpdate), should these be linked to the BeforeUpdate on the actual field or the form? Neil "Douglas J Steele" wrote in message ... I'm not sure you've really answered Allen's question. While you said that TimeIn and TimeOut are both Date/Time fields, do they contain both the date and time, or do they only contain the time? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Neil M" wrote in message ... Yes I have two fields - "TimeIn" and "TimeOut" are both Date/Time (Short Time format) I still get the error stating that the time in question is duplicated. Although it hasn't. The correct time is shown on the error displayed. Now I also get the debug window come up with the "Method or Data not found" and the following is highlighted (OldValue) in; If ((Me.TimeIn = Me.TimeIn.OldValue) And Any suggestions? Thanks, Neil "Allen Browne" wrote in message ... Add the line: Debug.Print strWhere just before the "varResult = " line. After it runs, open the Immediate Window (Ctrl+G) to see how it interpreted it. Use that in the WHERE clause of a query to see what's going on. Does the booking number that it reports make any sense? Does your table have 2 fields (named StartDateTime and EndDateTime in the example) that contain both the date and time? Are they Date/Time fields? -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Allen Browne" wrote in message ... Use the BeforeUpdate event of the *form* where you enter this data to provide the warning if there is an overlap. The basic concept is that 2 events overlap if: - A begins before B ends, AND - B begins before A ends. The form's BeforeUpdate event procedure will be something like this aircode: Private Sub Form_BeforeUpdate(Cancel As Integer) Dim strWhere As String Dim varResult As Variant Dim strMsg As String Const strcJetDateTime = "\#mm\/dd\/yyyy hh\:nn\:ss\#" If ((Me.StartDateTime = Me.StartDateTime.OldValue) AND _ (Me.EndDateTime = Me.EndDateTime.OldValue) AND _ (Me.RoomID = Me.RoomID.OldValue)) OR IsNull(Me.StartDateTime) _ OR IsNull(Me.EndDateTime) OR IsNull(Me.RoomID) Then 'do nothing Else strWhere = "(StartDateTime " & Format(Me.EndDateTime, strcJetDateTime) & _ ") AND " & Format(Me.StartDateTime, strcJetDateTime) & _ " EndDateTime) AND (RoomID = " & Me.RoomID & ") varResult = DLookup("BookingID", "tblBooking", strWhere) If Not IsNull(varResult) Then strMsg = "Clashes with booking # " & varResult & vbCrLf & "Continue anyway?" If MsgBox(strMsg, vbYesNo+vbDefaultButton2, "Double-booked") vbYes Then Cancel = True End If End If End If End Sub If you need to cope with open-ended bookings, or need to crosscheck all bookings against each other, see: Clashing Events/Appointments at: http://allenbrowne.com/appevent.html -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Neil M" wrote in message ... Hi all (yet again!) I have a problem with the way my database will have the check the following. Room Date Time In and Time Out I want to make sure that the inputter doesn't overlap any times on the same day for the room (there are 5 rooms) So say I have booked one room on 1/1/05 for 8:00am 9:00am, and I want to input a new record later on for the same day and room for 9:30am, I want the program to tell me that it has already been booked for that time. It can allow me to continue and book as at this stage I just want it to check the time for me? I would appreciate any suggestions or help as I do not really have a clue how this can be done? (my database knowledge is better than normal but not quite advanced!) Regards, Neil |
#13
|
|||
|
|||
Time checking problem
Neil, is this database designed to work for just one day? Would two
appointments for 9am be a duplicate? Or do you have another date field here somewhere? -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Neil M" wrote in message ... right here is the code for BeforeUpdate: Private Sub Form_BeforeUpdate(Cancel As Integer) Dim strWhere As String Dim varResult As Variant Dim strMsg As String Const strcJetDateTime = "\#mm\/dd\/yyyy hh\:nn\:ss\#" If ((Me.TimeIn = Me.TimeIn.OldValue) And _ (Me.TimeOut = Me.TimeOut.OldValue) And _ (Me.RoomID = Me.RoomID.OldValue)) Or IsNull(Me.TimeIn) _ Or IsNull(Me.EndDateTime) Or IsNull(Me.RoomID) Then 'do nothing Else strWhere = "(TimeIn " & Format(Me.TimeOut, strcJetDateTime) & ") AND " & Format(Me.TimeIn, strcJetDateTime) & " TimeOut) AND (RoomID = " & Me.RoomID & ")" Debug.Print strWhere varResult = DLookup("ID", "TBL_Main", strWhere) If Not IsNull(varResult) Then strMsg = "Clashes with booking # " & varResult & vbCrLf & "Continue anyway?" If MsgBox(strMsg, vbYesNo + vbDefaultButton2, "Double-booked") vbYes Then Cancel = True End If End If End If End Sub The TimeIn and TimeOut are both bound to their fields and are the boxes are named the same. Both are Time/Date but format is just Short Time so no they do not contain Date as far as I know as they use a input mask so the user inputs xx:xx They are also no events other than the once listed above (BeforeUpdate), should these be linked to the BeforeUpdate on the actual field or the form? Neil "Douglas J Steele" wrote in message ... I'm not sure you've really answered Allen's question. While you said that TimeIn and TimeOut are both Date/Time fields, do they contain both the date and time, or do they only contain the time? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Neil M" wrote in message ... Yes I have two fields - "TimeIn" and "TimeOut" are both Date/Time (Short Time format) I still get the error stating that the time in question is duplicated. Although it hasn't. The correct time is shown on the error displayed. Now I also get the debug window come up with the "Method or Data not found" and the following is highlighted (OldValue) in; If ((Me.TimeIn = Me.TimeIn.OldValue) And Any suggestions? Thanks, Neil "Allen Browne" wrote in message ... Add the line: Debug.Print strWhere just before the "varResult = " line. After it runs, open the Immediate Window (Ctrl+G) to see how it interpreted it. Use that in the WHERE clause of a query to see what's going on. Does the booking number that it reports make any sense? Does your table have 2 fields (named StartDateTime and EndDateTime in the example) that contain both the date and time? Are they Date/Time fields? -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Allen Browne" wrote in message ... Use the BeforeUpdate event of the *form* where you enter this data to provide the warning if there is an overlap. The basic concept is that 2 events overlap if: - A begins before B ends, AND - B begins before A ends. The form's BeforeUpdate event procedure will be something like this aircode: Private Sub Form_BeforeUpdate(Cancel As Integer) Dim strWhere As String Dim varResult As Variant Dim strMsg As String Const strcJetDateTime = "\#mm\/dd\/yyyy hh\:nn\:ss\#" If ((Me.StartDateTime = Me.StartDateTime.OldValue) AND _ (Me.EndDateTime = Me.EndDateTime.OldValue) AND _ (Me.RoomID = Me.RoomID.OldValue)) OR IsNull(Me.StartDateTime) _ OR IsNull(Me.EndDateTime) OR IsNull(Me.RoomID) Then 'do nothing Else strWhere = "(StartDateTime " & Format(Me.EndDateTime, strcJetDateTime) & _ ") AND " & Format(Me.StartDateTime, strcJetDateTime) & _ " EndDateTime) AND (RoomID = " & Me.RoomID & ") varResult = DLookup("BookingID", "tblBooking", strWhere) If Not IsNull(varResult) Then strMsg = "Clashes with booking # " & varResult & vbCrLf & "Continue anyway?" If MsgBox(strMsg, vbYesNo+vbDefaultButton2, "Double-booked") vbYes Then Cancel = True End If End If End If End Sub If you need to cope with open-ended bookings, or need to crosscheck all bookings against each other, see: Clashing Events/Appointments at: http://allenbrowne.com/appevent.html -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Neil M" wrote in message ... Hi all (yet again!) I have a problem with the way my database will have the check the following. Room Date Time In and Time Out I want to make sure that the inputter doesn't overlap any times on the same day for the room (there are 5 rooms) So say I have booked one room on 1/1/05 for 8:00am 9:00am, and I want to input a new record later on for the same day and room for 9:30am, I want the program to tell me that it has already been booked for that time. It can allow me to continue and book as at this stage I just want it to check the time for me? I would appreciate any suggestions or help as I do not really have a clue how this can be done? (my database knowledge is better than normal but not quite advanced!) Regards, Neil |
#14
|
|||
|
|||
Time checking problem
yes its one day.
so the user selects the date at the top and the room and there is a subform below that displays/allows input for the times the Staff wants to book the room in and out. Yes two appointments say for 9am would be duplicated but the test is for it not to be duplicated on the SAME day. Whereas you may have 9am for an appointment on the 1st nov and one also on the 2nd nov which should not bring up the error. Hope this helps General Structu (MAIN) DATE ---- ROOM ---- STAFF / TIME IN / TIME OUT (MAINSUB) "Allen Browne" wrote in message ... Neil, is this database designed to work for just one day? Would two appointments for 9am be a duplicate? Or do you have another date field here somewhere? -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Neil M" wrote in message ... right here is the code for BeforeUpdate: Private Sub Form_BeforeUpdate(Cancel As Integer) Dim strWhere As String Dim varResult As Variant Dim strMsg As String Const strcJetDateTime = "\#mm\/dd\/yyyy hh\:nn\:ss\#" If ((Me.TimeIn = Me.TimeIn.OldValue) And _ (Me.TimeOut = Me.TimeOut.OldValue) And _ (Me.RoomID = Me.RoomID.OldValue)) Or IsNull(Me.TimeIn) _ Or IsNull(Me.EndDateTime) Or IsNull(Me.RoomID) Then 'do nothing Else strWhere = "(TimeIn " & Format(Me.TimeOut, strcJetDateTime) & ") AND " & Format(Me.TimeIn, strcJetDateTime) & " TimeOut) AND (RoomID = " & Me.RoomID & ")" Debug.Print strWhere varResult = DLookup("ID", "TBL_Main", strWhere) If Not IsNull(varResult) Then strMsg = "Clashes with booking # " & varResult & vbCrLf & "Continue anyway?" If MsgBox(strMsg, vbYesNo + vbDefaultButton2, "Double-booked") vbYes Then Cancel = True End If End If End If End Sub The TimeIn and TimeOut are both bound to their fields and are the boxes are named the same. Both are Time/Date but format is just Short Time so no they do not contain Date as far as I know as they use a input mask so the user inputs xx:xx They are also no events other than the once listed above (BeforeUpdate), should these be linked to the BeforeUpdate on the actual field or the form? Neil "Douglas J Steele" wrote in message ... I'm not sure you've really answered Allen's question. While you said that TimeIn and TimeOut are both Date/Time fields, do they contain both the date and time, or do they only contain the time? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Neil M" wrote in message ... Yes I have two fields - "TimeIn" and "TimeOut" are both Date/Time (Short Time format) I still get the error stating that the time in question is duplicated. Although it hasn't. The correct time is shown on the error displayed. Now I also get the debug window come up with the "Method or Data not found" and the following is highlighted (OldValue) in; If ((Me.TimeIn = Me.TimeIn.OldValue) And Any suggestions? Thanks, Neil "Allen Browne" wrote in message ... Add the line: Debug.Print strWhere just before the "varResult = " line. After it runs, open the Immediate Window (Ctrl+G) to see how it interpreted it. Use that in the WHERE clause of a query to see what's going on. Does the booking number that it reports make any sense? Does your table have 2 fields (named StartDateTime and EndDateTime in the example) that contain both the date and time? Are they Date/Time fields? -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Allen Browne" wrote in message ... Use the BeforeUpdate event of the *form* where you enter this data to provide the warning if there is an overlap. The basic concept is that 2 events overlap if: - A begins before B ends, AND - B begins before A ends. The form's BeforeUpdate event procedure will be something like this aircode: Private Sub Form_BeforeUpdate(Cancel As Integer) Dim strWhere As String Dim varResult As Variant Dim strMsg As String Const strcJetDateTime = "\#mm\/dd\/yyyy hh\:nn\:ss\#" If ((Me.StartDateTime = Me.StartDateTime.OldValue) AND _ (Me.EndDateTime = Me.EndDateTime.OldValue) AND _ (Me.RoomID = Me.RoomID.OldValue)) OR IsNull(Me.StartDateTime) _ OR IsNull(Me.EndDateTime) OR IsNull(Me.RoomID) Then 'do nothing Else strWhere = "(StartDateTime " & Format(Me.EndDateTime, strcJetDateTime) & _ ") AND " & Format(Me.StartDateTime, strcJetDateTime) & _ " EndDateTime) AND (RoomID = " & Me.RoomID & ") varResult = DLookup("BookingID", "tblBooking", strWhere) If Not IsNull(varResult) Then strMsg = "Clashes with booking # " & varResult & vbCrLf & "Continue anyway?" If MsgBox(strMsg, vbYesNo+vbDefaultButton2, "Double-booked") vbYes Then Cancel = True End If End If End If End Sub If you need to cope with open-ended bookings, or need to crosscheck all bookings against each other, see: Clashing Events/Appointments at: http://allenbrowne.com/appevent.html -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Neil M" wrote in message ... Hi all (yet again!) I have a problem with the way my database will have the check the following. Room Date Time In and Time Out I want to make sure that the inputter doesn't overlap any times on the same day for the room (there are 5 rooms) So say I have booked one room on 1/1/05 for 8:00am 9:00am, and I want to input a new record later on for the same day and room for 9:30am, I want the program to tell me that it has already been booked for that time. It can allow me to continue and book as at this stage I just want it to check the time for me? I would appreciate any suggestions or help as I do not really have a clue how this can be done? (my database knowledge is better than normal but not quite advanced!) Regards, Neil |
#15
|
|||
|
|||
Time checking problem
So how do you plan to incorporate the date into the strWhere string which
looks up the table for duplicates? -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Neil M" wrote in message ... yes its one day. so the user selects the date at the top and the room and there is a subform below that displays/allows input for the times the Staff wants to book the room in and out. Yes two appointments say for 9am would be duplicated but the test is for it not to be duplicated on the SAME day. Whereas you may have 9am for an appointment on the 1st nov and one also on the 2nd nov which should not bring up the error. Hope this helps General Structu (MAIN) DATE ---- ROOM ---- STAFF / TIME IN / TIME OUT (MAINSUB) "Allen Browne" wrote in message ... Neil, is this database designed to work for just one day? Would two appointments for 9am be a duplicate? Or do you have another date field here somewhere? "Neil M" wrote in message ... right here is the code for BeforeUpdate: Private Sub Form_BeforeUpdate(Cancel As Integer) Dim strWhere As String Dim varResult As Variant Dim strMsg As String Const strcJetDateTime = "\#mm\/dd\/yyyy hh\:nn\:ss\#" If ((Me.TimeIn = Me.TimeIn.OldValue) And _ (Me.TimeOut = Me.TimeOut.OldValue) And _ (Me.RoomID = Me.RoomID.OldValue)) Or IsNull(Me.TimeIn) _ Or IsNull(Me.EndDateTime) Or IsNull(Me.RoomID) Then 'do nothing Else strWhere = "(TimeIn " & Format(Me.TimeOut, strcJetDateTime) & ") AND " & Format(Me.TimeIn, strcJetDateTime) & " TimeOut) AND (RoomID = " & Me.RoomID & ")" Debug.Print strWhere varResult = DLookup("ID", "TBL_Main", strWhere) If Not IsNull(varResult) Then strMsg = "Clashes with booking # " & varResult & vbCrLf & "Continue anyway?" If MsgBox(strMsg, vbYesNo + vbDefaultButton2, "Double-booked") vbYes Then Cancel = True End If End If End If End Sub The TimeIn and TimeOut are both bound to their fields and are the boxes are named the same. Both are Time/Date but format is just Short Time so no they do not contain Date as far as I know as they use a input mask so the user inputs xx:xx They are also no events other than the once listed above (BeforeUpdate), should these be linked to the BeforeUpdate on the actual field or the form? Neil "Douglas J Steele" wrote in message ... I'm not sure you've really answered Allen's question. While you said that TimeIn and TimeOut are both Date/Time fields, do they contain both the date and time, or do they only contain the time? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Neil M" wrote in message ... Yes I have two fields - "TimeIn" and "TimeOut" are both Date/Time (Short Time format) I still get the error stating that the time in question is duplicated. Although it hasn't. The correct time is shown on the error displayed. Now I also get the debug window come up with the "Method or Data not found" and the following is highlighted (OldValue) in; If ((Me.TimeIn = Me.TimeIn.OldValue) And Any suggestions? Thanks, Neil "Allen Browne" wrote in message ... Add the line: Debug.Print strWhere just before the "varResult = " line. After it runs, open the Immediate Window (Ctrl+G) to see how it interpreted it. Use that in the WHERE clause of a query to see what's going on. Does the booking number that it reports make any sense? Does your table have 2 fields (named StartDateTime and EndDateTime in the example) that contain both the date and time? Are they Date/Time fields? -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Allen Browne" wrote in message ... Use the BeforeUpdate event of the *form* where you enter this data to provide the warning if there is an overlap. The basic concept is that 2 events overlap if: - A begins before B ends, AND - B begins before A ends. The form's BeforeUpdate event procedure will be something like this aircode: Private Sub Form_BeforeUpdate(Cancel As Integer) Dim strWhere As String Dim varResult As Variant Dim strMsg As String Const strcJetDateTime = "\#mm\/dd\/yyyy hh\:nn\:ss\#" If ((Me.StartDateTime = Me.StartDateTime.OldValue) AND _ (Me.EndDateTime = Me.EndDateTime.OldValue) AND _ (Me.RoomID = Me.RoomID.OldValue)) OR IsNull(Me.StartDateTime) _ OR IsNull(Me.EndDateTime) OR IsNull(Me.RoomID) Then 'do nothing Else strWhere = "(StartDateTime " & Format(Me.EndDateTime, strcJetDateTime) & _ ") AND " & Format(Me.StartDateTime, strcJetDateTime) & _ " EndDateTime) AND (RoomID = " & Me.RoomID & ") varResult = DLookup("BookingID", "tblBooking", strWhere) If Not IsNull(varResult) Then strMsg = "Clashes with booking # " & varResult & vbCrLf & "Continue anyway?" If MsgBox(strMsg, vbYesNo+vbDefaultButton2, "Double-booked") vbYes Then Cancel = True End If End If End If End Sub If you need to cope with open-ended bookings, or need to crosscheck all bookings against each other, see: Clashing Events/Appointments at: http://allenbrowne.com/appevent.html -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Neil M" wrote in message ... Hi all (yet again!) I have a problem with the way my database will have the check the following. Room Date Time In and Time Out I want to make sure that the inputter doesn't overlap any times on the same day for the room (there are 5 rooms) So say I have booked one room on 1/1/05 for 8:00am 9:00am, and I want to input a new record later on for the same day and room for 9:30am, I want the program to tell me that it has already been booked for that time. It can allow me to continue and book as at this stage I just want it to check the time for me? I would appreciate any suggestions or help as I do not really have a clue how this can be done? (my database knowledge is better than normal but not quite advanced!) |
#16
|
|||
|
|||
Time checking problem
i haven't gave it a 2nd thought - someone on the thread completed the code
for me i just wanted to get it working then bbreak it down to see how it worked so i could explain it to others on the forums. can you help any more please? "Allen Browne" wrote in message ... So how do you plan to incorporate the date into the strWhere string which looks up the table for duplicates? -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Neil M" wrote in message ... yes its one day. so the user selects the date at the top and the room and there is a subform below that displays/allows input for the times the Staff wants to book the room in and out. Yes two appointments say for 9am would be duplicated but the test is for it not to be duplicated on the SAME day. Whereas you may have 9am for an appointment on the 1st nov and one also on the 2nd nov which should not bring up the error. Hope this helps General Structu (MAIN) DATE ---- ROOM ---- STAFF / TIME IN / TIME OUT (MAINSUB) "Allen Browne" wrote in message ... Neil, is this database designed to work for just one day? Would two appointments for 9am be a duplicate? Or do you have another date field here somewhere? "Neil M" wrote in message ... right here is the code for BeforeUpdate: Private Sub Form_BeforeUpdate(Cancel As Integer) Dim strWhere As String Dim varResult As Variant Dim strMsg As String Const strcJetDateTime = "\#mm\/dd\/yyyy hh\:nn\:ss\#" If ((Me.TimeIn = Me.TimeIn.OldValue) And _ (Me.TimeOut = Me.TimeOut.OldValue) And _ (Me.RoomID = Me.RoomID.OldValue)) Or IsNull(Me.TimeIn) _ Or IsNull(Me.EndDateTime) Or IsNull(Me.RoomID) Then 'do nothing Else strWhere = "(TimeIn " & Format(Me.TimeOut, strcJetDateTime) & ") AND " & Format(Me.TimeIn, strcJetDateTime) & " TimeOut) AND (RoomID = " & Me.RoomID & ")" Debug.Print strWhere varResult = DLookup("ID", "TBL_Main", strWhere) If Not IsNull(varResult) Then strMsg = "Clashes with booking # " & varResult & vbCrLf & "Continue anyway?" If MsgBox(strMsg, vbYesNo + vbDefaultButton2, "Double-booked") vbYes Then Cancel = True End If End If End If End Sub The TimeIn and TimeOut are both bound to their fields and are the boxes are named the same. Both are Time/Date but format is just Short Time so no they do not contain Date as far as I know as they use a input mask so the user inputs xx:xx They are also no events other than the once listed above (BeforeUpdate), should these be linked to the BeforeUpdate on the actual field or the form? Neil "Douglas J Steele" wrote in message ... I'm not sure you've really answered Allen's question. While you said that TimeIn and TimeOut are both Date/Time fields, do they contain both the date and time, or do they only contain the time? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Neil M" wrote in message ... Yes I have two fields - "TimeIn" and "TimeOut" are both Date/Time (Short Time format) I still get the error stating that the time in question is duplicated. Although it hasn't. The correct time is shown on the error displayed. Now I also get the debug window come up with the "Method or Data not found" and the following is highlighted (OldValue) in; If ((Me.TimeIn = Me.TimeIn.OldValue) And Any suggestions? Thanks, Neil "Allen Browne" wrote in message ... Add the line: Debug.Print strWhere just before the "varResult = " line. After it runs, open the Immediate Window (Ctrl+G) to see how it interpreted it. Use that in the WHERE clause of a query to see what's going on. Does the booking number that it reports make any sense? Does your table have 2 fields (named StartDateTime and EndDateTime in the example) that contain both the date and time? Are they Date/Time fields? -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Allen Browne" wrote in message ... Use the BeforeUpdate event of the *form* where you enter this data to provide the warning if there is an overlap. The basic concept is that 2 events overlap if: - A begins before B ends, AND - B begins before A ends. The form's BeforeUpdate event procedure will be something like this aircode: Private Sub Form_BeforeUpdate(Cancel As Integer) Dim strWhere As String Dim varResult As Variant Dim strMsg As String Const strcJetDateTime = "\#mm\/dd\/yyyy hh\:nn\:ss\#" If ((Me.StartDateTime = Me.StartDateTime.OldValue) AND _ (Me.EndDateTime = Me.EndDateTime.OldValue) AND _ (Me.RoomID = Me.RoomID.OldValue)) OR IsNull(Me.StartDateTime) _ OR IsNull(Me.EndDateTime) OR IsNull(Me.RoomID) Then 'do nothing Else strWhere = "(StartDateTime " & Format(Me.EndDateTime, strcJetDateTime) & _ ") AND " & Format(Me.StartDateTime, strcJetDateTime) & _ " EndDateTime) AND (RoomID = " & Me.RoomID & ") varResult = DLookup("BookingID", "tblBooking", strWhere) If Not IsNull(varResult) Then strMsg = "Clashes with booking # " & varResult & vbCrLf & "Continue anyway?" If MsgBox(strMsg, vbYesNo+vbDefaultButton2, "Double-booked") vbYes Then Cancel = True End If End If End If End Sub If you need to cope with open-ended bookings, or need to crosscheck all bookings against each other, see: Clashing Events/Appointments at: http://allenbrowne.com/appevent.html -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Neil M" wrote in message ... Hi all (yet again!) I have a problem with the way my database will have the check the following. Room Date Time In and Time Out I want to make sure that the inputter doesn't overlap any times on the same day for the room (there are 5 rooms) So say I have booked one room on 1/1/05 for 8:00am 9:00am, and I want to input a new record later on for the same day and room for 9:30am, I want the program to tell me that it has already been booked for that time. It can allow me to continue and book as at this stage I just want it to check the time for me? I would appreciate any suggestions or help as I do not really have a clue how this can be done? (my database knowledge is better than normal but not quite advanced!) |
#17
|
|||
|
|||
Time checking problem
It looks like you have not given us enough information to help you here. The
solution will depend on which fields are in which tables, and what they are called. Presumably you will need to combine the date and time values somehow by adding the time value on to the date value. That's probably as far as I can take you here. Hopefully you will be able to use the concepts explained and apply them as your understanding grows. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Neil M" wrote in message ... i haven't gave it a 2nd thought - someone on the thread completed the code for me i just wanted to get it working then bbreak it down to see how it worked so i could explain it to others on the forums. can you help any more please? |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Time Zone suggestions for Outlook Calendar | Ben Knox | Calendar | 2 | October 20th, 2005 03:42 PM |
Problem Updating New Messages from NTTP News Server OE | Chad Harris | Outlook Express | 19 | February 7th, 2005 07:21 PM |
Continual Error 1321 Trying to Install Office 2003 | Chad Harris | General Discussions | 9 | June 11th, 2004 08:19 AM |
Outlook 2003 Terminal Server Time Zone issue | Robert Strom | Calendar | 2 | May 26th, 2004 10:50 PM |
time zone & apt times auto changing | Mary | Calendar | 6 | May 25th, 2004 05:44 AM |