If statements not giving desired results for resources database

[BitesBoy]

Hi,
I've got a database (Access 2010, Windows 7) that reserves and issues resources. The problem arises for the macro that codes the reservations for the loan items. I can get the macro to display multiple reservations of the same resource (provided they are all on the same day). However, the price I pay for this is that if I make a reservation for today, while setting the return on a later date, the macro will not permit me to set the time in before the time out. While this makes sense for reservations on the same day, it doesn't necessarily for reservations that should be returned on another day.

This limitation is set through some if statements to test that the 'DueDate' is not before the 'Reservation From date', and that the Time the resources are checked in is not before the time they are checked out:

If Me.DueDate < Me.DateCheckedOut Then
    MsgBox "Due Date cannot be before the Reservation   From date", vbOKOnly + vbExclamation, "Date Error"
    Cancel = True
    Exit Function
End If
 
If Format(Me.TimeIn, "Short Time") < Format(Me.TimeOut, "Short Time") Then
   MsgBox "Time In cannot be before Time Out. Please amend times.", vbOKOnly + vbExclamation, "Time Error"
   Cancel = True
   Exit Function
End If

Provided the reservations do not transcend more than one day, the code works.

If the return date is the next day, the macros will not permit the return time before the issue time. This is clearly a problem.

I was partly able to get around this problem with a third block of code:

If Me.DueDate = Me.DateCheckedOut And Format(Me.TimeIn, "Short Time") < Format(Me.TimeOut, "Short Time") Then
    MsgBox "Time In cannot be before Time Out. Please  amend times.", vbOKOnly + vbExclamation, "Time Error"
    Cancel = True
    Exit Function
    End If

When I run this third block of code for an item being returned on a later day, I am able to get it to permit the time in before the time out. However, it will message "date clash" if I try to make more than one reservation on the same day, regardless whether the time in of the first reservation is before the time out of the next.

I have included the full code for the Reserve Form Macro below. I would really appreciate it if someone with more experience than I might be able to spot any glaring mistakes in the logic.

Many thanks,
BitesBoy

Option Compare Database
 
Private Sub Form_Current()
 
varDuration = DLookup("DefaultLoanDuration", "Equipment", "EquipmentID = '" & Nz(Me.EquipmentID, "") & "'")
Me.txtDuration = varDuration
 
End Sub
 
Public Function CheckValidReservationOld(Cancel As Integer)
 
Dim db As DAO.Database, rs As DAO.Recordset
Dim intnewrec As Integer
 
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT EquipmentID, DateCheckedOut, DueDate, IsReservation FROM Loan WHERE EquipmentID='" & Me.EquipmentID & "' AND (IsReservation=True OR DateCheckedIn Is Null) ORDER BY DateCheckedOut ASC")
 
If Me.DueDate < Me.DateCheckedOut Then
    MsgBox "Due Date cannot be before the Reservation From date", vbOKOnly + vbExclamation, "Warning"
    Cancel = True
    Exit Function
End If
 
If rs.RecordCount > 0 And rs.RecordCount <> -1 Then
    MsgBox "Reservations or loans exist", vbOKOnly
    'Item already has one or more reservations
    'Need to check if the new reservation clashes with any existing reservations or bookings
    rs.MoveFirst
    Do While Not rs.EOF
        If (Me.DateCheckedOut >= rs!DateCheckedOut And Me.DueDate <= rs!DueDate) Then
            MsgBox "Dates clash with an existing booking/reservation", vbOKOnly, "Reservation Exists"
            Cancel = True
            Me.DateCheckedOut.SetFocus
            Exit Function
        Else
            'Continue to save record and close window
            DoCmd.RunCommand acCmdSaveRecord
            DoCmd.Close
        End If
    Loop
Else
    'No reservations exist
    'Continue to save record and close window
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.Close
End If
 
End Function
 
 
'Public Function CheckValidReservation(Cancel As Integer)
'
'Dim db As DAO.Database, rs As DAO.Recordset
'Dim intnewrec As Integer
'
'Set db = CurrentDb()
'
'If Me.DueDate < Me.DateCheckedOut Then
'    MsgBox "Due Date cannot be before the Reservation From date", vbOKOnly + vbExclamation, "Date Error"
'    Cancel = True
'    Exit Function
'End If
'
'Set rs = db.OpenRecordset("SELECT EquipmentID, DateCheckedOut, DueDate, IsReservation FROM Loan WHERE EquipmentID='" & Me.EquipmentID & "' AND (IsReservation=True OR DateCheckedIn Is Null) AND (DateCheckedOut>=Date()) ORDER BY DateCheckedOut ASC")
'
'StartDate = Me.DateCheckedOut
'EndDate = Me.DueDate
'
'test = False
'
'If rs.RecordCount = 0 Then
'    'Currently no existing bookings or reservations
'    'Ok to save record and close
'    DoCmd.RunCommand acCmdSaveRecord
'    DoCmd.Close
'    Exit Function
'End If
'
'rs.MoveFirst
'
'Do Until rs.EOF
'
'If StartDate = rs!DateCheckedOut Or EndDate = rs!DueDate Then
'    test = True
'ElseIf StartDate >= rs!DateCheckedOut And StartDate <= rs!DueDate Then
'    test = True
'ElseIf EndDate <= rs!DueDate And EndDate >= rs!DateCheckedOut Then
'    test = True
'End If
'
'If test Then
'    rs.MoveLast
'    rs.MoveNext
'Else
'    rs.MoveNext
'End If
'
'Loop
'
'rs.Close
'
'If test = False Then
'    'Dates selected are ok
'    'Continue to save record and close window
'    DoCmd.RunCommand acCmdSaveRecord
'    MsgBox "Reservation Saved", vbOKOnly + vbInformation, "Reservation Saved"
'    DoCmd.Close
'Else
'    'Display error message to user
'    MsgBox "The dates you have selected clash with an existing booking/reservation. Please choose different dates.", vbOKOnly + vbExclamation, "Reservation Clash"
'    'Cancel
'    Cancel = True
'    Me.DateCheckedOut.SetFocus
'
'    Set rs = Nothing
'    Set db = Nothing
'
'    Exit Function
'End If
'
'Set rs = Nothing
'Set db = Nothing
'
'End Function
 
Public Function CheckValidReservation(Cancel As Integer)
 
Dim db As DAO.Database, rs As DAO.Recordset
Dim intnewrec As Integer
 
Set db = CurrentDb()
 
'If Me.DueDate < Me.DateCheckedOut Then
    'MsgBox "Due Date cannot be before the Reservation From date", vbOKOnly + vbExclamation, "Date Error"
    'Cancel = True
    'Exit Function
'End If
 
'If Format(Me.TimeIn, "Short Time") < Format(Me.TimeOut, "Short Time") Then
'   MsgBox "Time In cannot be before Time Out. Please amend times.", vbOKOnly + vbExclamation, "Time Error"
'   Cancel = True
'   Exit Function
'End If
 
'If Me.DueDate = Me.DateCheckedOut And Format(Me.TimeIn, "Short Time") < Format(Me.TimeOut, "Short Time") Then
'    MsgBox "Time In cannot be before Time Out. Please amend times.", vbOKOnly + vbExclamation, "Time Error"
'    Cancel = True
'    Exit Function
'End If
 
If Me.DueDate = Me.DateCheckedOut Then
   If Format(Me.TimeIn, "Short Time") < Format(Me.TimeOut, "Short Time") Then
    MsgBox "Time In cannot be before Time Out. Please amend times.", vbOKOnly + vbExclamation, "Time Error"
    Cancel = True
    Exit Function
    End If
End If
 
Set rs = db.OpenRecordset("SELECT EquipmentID, DateCheckedOut, DueDate, TimeOut, TimeIn, IsReservation FROM Loan WHERE EquipmentID='" & Me.EquipmentID & "' AND (IsReservation=True OR DateCheckedIn Is Null) AND (DateCheckedOut>=Date()) ORDER BY DateCheckedOut ASC")
 
StartDate = Me.DateCheckedOut
EndDate = Me.DueDate
TimeOut = Format(Me.TimeOut, "Short Time")
TimeIn = Format(Me.TimeIn, "Short Time")
 
test = False
 
If rs.RecordCount = 0 Then
    'Currently no existing bookings or reservations
    'Ok to save record and close
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.Close
    Exit Function
End If
 
rs.MoveFirst
 
Do Until rs.EOF
 
If StartDate = rs!DateCheckedOut And EndDate = rs!DueDate Then
    If Me.EquipmentID = "MLAP-SET01" Or Me.EquipmentID = "MLAP-SET02" Or Me.EquipmentID = "MTAB-SET01" Or Me.EquipmentID = "MTAB-SET02" Or Me.EquipmentID = "MTAB-SET03" Or Me.EquipmentID = "MTAB-676c" Or Me.EquipmentID = "MTAB-677c" Or Me.EquipmentID = "MTAB-678c" Or Me.EquipmentID = "MTAB-679c" Or Me.EquipmentID = "MTAB-680c" Or Me.EquipmentID = "MTAB-681c" Or Me.EquipmentID = "MTAB-682c" Or Me.EquipmentID = "MTAB-683c" Or Me.EquipmentID = "MTAB-684c" Or Me.EquipmentID = "MTAB-685c" Then
        'Perform additional time tests for netbook sets
        If IsNull(TimeOut) Or IsNull(TimeIn) Then
            MsgBox "Please enter time values", vbOKOnly
            Cancel = True
            Me.TimeOut.SetFocus
            rs.Close
            Set rs = Nothing
            Set db = Nothing
            Exit Function
        Else
            If TimeOut = rs!TimeOut Or TimeIn = rs!TimeIn Then
                test = True
            ElseIf TimeOut >= rs!TimeOut And TimeOut <= rs!TimeIn Then
                test = True
            ElseIf TimeIn <= rs!TimeIn And TimeIn >= rs!TimeOut Then
                test = True
            End If
        End If
    Else
        test = True
    End If
ElseIf StartDate = rs!DateCheckedOut Or EndDate = rs!DueDate Then
    test = True
ElseIf StartDate >= rs!DateCheckedOut And StartDate <= rs!DueDate Then
    test = True
ElseIf EndDate <= rs!DueDate And EndDate >= rs!DateCheckedOut Then
    test = True
ElseIf StartDate <= rs!DateCheckedOut And EndDate >= rs!DateCheckedOut Then
    test = True
End If
 
If test Then
    rs.MoveLast
    rs.MoveNext
Else
    rs.MoveNext
End If
 
Loop
 
rs.Close
 
If test = False Then
    'Dates selected are ok
    'Continue to save record and close window
    DoCmd.RunCommand acCmdSaveRecord
    MsgBox "Reservation Saved", vbOKOnly + vbInformation, "Reservation Saved"
    DoCmd.Close
Else
    'Display error message to user
    MsgBox "The dates you have selected clash with an existing booking/reservation. Please choose different dates.", vbOKOnly + vbExclamation, "Reservation Clash"
    'Cancel
    Cancel = True
    Me.DateCheckedOut.SetFocus
 
    Set rs = Nothing
    Set db = Nothing
 
    Exit Function
End If
 
Set rs = Nothing
Set db = Nothing
 
End Function
 
Private Sub Form_Load()
 
If Me.EquipmentID = "MLAP-SET01" Or Me.EquipmentID = "MLAP-SET02" Or Me.EquipmentID = "MTAB-SET01" Or Me.EquipmentID = "MTAB-SET02" Or Me.EquipmentID = "MTAB-SET03" Or Me.EquipmentID = "MTAB-676c" Or Me.EquipmentID = "MTAB-677c" Or Me.EquipmentID = "MTAB-678c" Or Me.EquipmentID = "MTAB-679c" Or Me.EquipmentID = "MTAB-680c" Or Me.EquipmentID = "MTAB-681c" Or Me.EquipmentID = "MTAB-682c" Or Me.EquipmentID = "MTAB-683c" Or Me.EquipmentID = "MTAB-684c" Or Me.EquipmentID = "MTAB-685c" Then
    Me.TimeIn.Visible = True
    Me.TimeOut.Visible = True
End If
 
End Sub
 
 

[NeoPa]

You may want to consider working with Date/Time values rather than separate dates and times. It's possible to do it the latter way, but more (and unnecessarily so) complicated (As you've already found of course).

[BitesBoy]

Thanks for taking the time to look and reply NeoPa. I am assuming you mean changing from Short Time to General Date. I have tried this and it makes no difference. If I can boil the problem into one short paragraph, it would be this. The database macro will let me reserve the same equipment multiple times within one day. It will also permit me to reserve one item over the duration of more than one day. It won't let me reserve the 2 concurrently. There a several macros, besides the reserve item, not least a book item and an an update reservation to click out and return the item on collection and return. TimeOut, DateCheckedOut, DueDate and TimeIn were in Short Time Format. I changed them all to General Date, but the result is the same. Is there a different date format I should use or due you mean I should write the code in the macro differently?

[NeoPa]

The format is entirely irrelevant :-(

If you store the date and the time of the points in time you're working with then you will find it easier to work with them. Instead of saying :

Is Date1 < Date2 AND Is Time1 < Time2 Then

you can do far simpler comparisons similar to :

Is DateTime1 < DateTime2 Then

That's pseudo-code of course, but do you see what I'm trying to say?

[BitesBoy]

Forgive me for being niaive here as I'm on a steep learning curve. Basically I used the find/replace aid to replace all references in the macros for Date and Time alone to DateTime. I then changed the control sources in the forms in design view to reflect the changes. When I ran the project I get the follwing error.
"Compile error

Method or data member not found"

When the debugger comes up indicates it doesn't recognise the

term Me.DateTimeIn.Visble:

Private Sub Form_Load ()
If Me.EquipmentID = "MLAP-SET01" Or Me.EquipmentID = "MLAP-SET02" Or Me.EquipmentID = "MTAB-SET01" Or Me.EquipmentID = "MTAB-SET02" Or Me.EquipmentID = "MTAB-SET03" Or Me.EquipmentID = "MTAB-676c" Or Me.EquipmentID = "MTAB-677c" Or Me.EquipmentID = "MTAB-678c" Or Me.EquipmentID = "MTAB-679c" Or Me.EquipmentID = "MTAB-680c" Or Me.EquipmentID = "MTAB-681c" Or Me.EquipmentID = "MTAB-682c" Or Me.EquipmentID = "MTAB-683c" Or Me.EquipmentID = "MTAB-684c" Or Me.EquipmentID = "MTAB-685c" Then
    Me.DateTimeIn.Visible = True
    Me.DateTimeOut.Visible = True
End If
 

What am I doing wrong please?

[jimatqsi]

BitesBoy,
You've tried to take a shortcut by mass replacing one thing with another. You should have gone painstakingly through the code to verify what you were doing. You changed the name of one thing in the code to some other thing but that thing does exist. You have no object on your form called DateTimeIn and DateTimeOut.

Here's what may get you to your quickest fix. Go back to your original code. You can combine a date and time into one DateTime variable simply by concatenating. Like this:

Dim dtOut as Date
dim dtIn as Date
dtOut = me.DateOut & " " & me.TimeOut
dtIn = me.DateIn & " " & me.TimeIn
if dtIn > dtOut then
 msgbox "error"
end if

You might also find the information here helpful if you really want to learn about handling dates and times. It's an old link but I think info is still all relevant:
http://support.microsoft.com/kb/210604

Jim

[NeoPa]

No problem for finding it confusing BitesBoy. I suspect you were more floundering than looking for a shortcut, but nevertheless Jim's post throws some light on the point I was trying to get across.

Basically, when working with timestamps (a point in time with a date and time element) it is better to hold all the information as a single value than trying to specify it with two separate values - to whit a date and a time.

How you interact with the user is up to you. They can enter it separately or as a single value, as long as any separate entry is followed automatically by joining them together for storing.

Jim's illustration makes it fairly clear why this makes sense.

[jimatqsi]

I'm sorry if I came across harshly, it was not my intent. I think we all want posters to feel eager to come back for more help and advice.

Let us know how this turned out, BitesBoy.

Jim

[BitesBoy]

No offense taken Jim. I can see why someone looking on the outside in, with limited prior knowledge, could not help but think I was taking the lazier route. But if the truth be told, NeoPa was on the money when he suspected I was floundering. If I was to give your site the full background as to how I ended up working on this database, I'd probably have to give you as much info as I submitted for the code in the one module I uploaded.

The short version is my knowledge of databases and Visual Basic goes back a decade. All my dB knowledge gained then was on Access 2003 and used purely to get a qualification. Life, either by work or private projects, offered me no further incentives to continue using that knowledge. That is until a few months ago when I inherited this database from someone who left our employ, who clearly knew how to create a fully relational, macro-enabled database. And yet for the creator's knowledge and skills, you will have gathered from my query that even the creator clearly did not have the definitive knowledge required to provide full functionality to the database.

So when I did take up the challenge I found that the back-end and front-end changes in Access 2010 compared to 2003 to be like night and day. Whether I like it or not, with no one else with the knowledge to maintain it I inherited the role of maintaining the database .

For all my shortcomings, through sheer doggedness and trial and error, I have managed to master a lot of the requirements necessary to maintain it. So thank you for your, and NeoPa's patience in baring with some of my rather naive response.

My rant is nearly over. I'm impressed by the speed of responses on this site, yours and NeoPa's. It is truly heartening that there are people in the community who are prepared to offer their advice freely. I also realise that this medium is not always the best way to convey and transmit thoughts and requests, because the nature of learning computing skills, whether applications or programming, is often learned best by seeing things in action. Perhaps screen recordings of the effects people are trying to convey might enhance this.

I look forward to implementing your coding changes, but will only be able to tackle this on Monday at work, where I have access to Access2010. At home I still only have the older technology.

Thanks again,
BitesBoy

[NeoPa]

That's all perfectly reasonable :-)

If you think it would help, I can put you in touch with someone who can help professionally with teaching and helping you directly in your project at the same time. She is a fellow Access MVP and specialises in such 'training on the job'.

Let me know if you're interested and I'll PM you her details. She's very experienced and will certainly be able to take you forward. I can't say what her rates are, but I suspect they're very reasonable - as long as you remember it's a professional service (Some people seem to believe they can get hours of professional service for the price of a pint of beer).

Check her (Crystal Long) out on :
Learn Access Playlist on YouTube

[BitesBoy]

I tried the new date time format advised by NeoPa and detailed by Jim. Unfortunately, it did not work. At first I ran it and it threw up an object issue me at the bottom:

 Me.TimeOut.Visible = True
        Me.TimeIn.Visible = True

I changed the code for these 2 lines back and it ran, but still didn't provide the desired affects. I wondered if you might be so kind to look over the code changes I made and comment if there is anything amiss?

Option Compare Database
 
Private Sub Form_Current()
 
varDuration = DLookup("DefaultLoanDuration", "Equipment", "EquipmentID = '" & Nz(Me.EquipmentID, "") & "'")
Me.txtDuration = varDuration
 
End Sub
 
Public Function CheckValidReservationOld(Cancel As Integer)
 
Dim db As DAO.Database, rs As DAO.Recordset
Dim intnewrec As Integer
Dim dtOut As Date
Dim dtIn As Date
Dim dtDue As Date
 
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT EquipmentID, DateCheckedOut, DueDate, IsReservation FROM Loan WHERE EquipmentID='" & Me.EquipmentID & "' AND (IsReservation=True OR DateCheckedIn Is Null) ORDER BY DateCheckedOut ASC")
 
dtOut = Me.DateCheckedOut & " " & Me.TimeOut
dtIn = Me.DateCheckedIn & " " & Me.TimeIn
dtDue = Me.DueDate & " " & Me.TimeIn
 
If dtIn < dtOut Then
    MsgBox "Date reserved to cannot be before the Reservation From date", vbOKOnly + vbExclamation, "Warning"
    Cancel = True
    Exit Function
End If
 
If dtDue < dtOut Then
    MsgBox "Due Date cannot be before the Reservation From date", vbOKOnly + vbExclamation, "Warning"
    Cancel = True
    Exit Function
End If
 
If rs.RecordCount > 0 And rs.RecordCount <> -1 Then
    MsgBox "Reservations or loans exist", vbOKOnly
    'Item already has one or more reservations
    'Need to check if the new reservation clashes with any existing reservations or bookings
    rs.MoveFirst
    Do While Not rs.EOF
 
        If dtOut >= rs!dtOut And dtDue <= rs!Due Then
            MsgBox "Dates clash with an existing booking/reservation", vbOKOnly, "Reservation Exists"
            Cancel = True
            dtOut.SetFocus
            Exit Function
        'If (Me.DateCheckedOut >= rs!DateCheckedOut And Me.DueDate <= rs!DueDate) Then
            'MsgBox "Dates clash with an existing booking/reservation", vbOKOnly, "Reservation Exists"
            'Cancel = True
            'Me.DateCheckedOut.SetFocus
            'Exit Function
        Else
            'Continue to save record and close window
            DoCmd.RunCommand acCmdSaveRecord
            DoCmd.Close
        End If
    Loop
Else
    'No reservations exist
    'Continue to save record and close window
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.Close
End If
 
End Function
 
 
'Public Function CheckValidReservation(Cancel As Integer)
'
'Dim db As DAO.Database, rs As DAO.Recordset
'Dim intnewrec As Integer
'
'Set db = CurrentDb()
'
'If Me.DueDate < Me.DateCheckedOut Then
'    MsgBox "Due Date cannot be before the Reservation From date", vbOKOnly + vbExclamation, "Date Error"
'    Cancel = True
'    Exit Function
'End If
'
'Set rs = db.OpenRecordset("SELECT EquipmentID, DateCheckedOut, DueDate, IsReservation FROM Loan WHERE EquipmentID='" & Me.EquipmentID & "' AND (IsReservation=True OR DateCheckedIn Is Null) AND (DateCheckedOut>=Date()) ORDER BY DateCheckedOut ASC")
'
'StartDate = Me.DateCheckedOut
'EndDate = Me.DueDate
'
'test = False
'
'If rs.RecordCount = 0 Then
'    'Currently no existing bookings or reservations
'    'Ok to save record and close
'    DoCmd.RunCommand acCmdSaveRecord
'    DoCmd.Close
'    Exit Function
'End If
'
'rs.MoveFirst
'
'Do Until rs.EOF
'
'If StartDate = rs!DateCheckedOut Or EndDate = rs!DueDate Then
'    test = True
'ElseIf StartDate >= rs!DateCheckedOut And StartDate <= rs!DueDate Then
'    test = True
'ElseIf EndDate <= rs!DueDate And EndDate >= rs!DateCheckedOut Then
'    test = True
'End If
'
'If test Then
'    rs.MoveLast
'    rs.MoveNext
'Else
'    rs.MoveNext
'End If
'
'Loop
'
'rs.Close
'
'If test = False Then
'    'Dates selected are ok
'    'Continue to save record and close window
'    DoCmd.RunCommand acCmdSaveRecord
'    MsgBox "Reservation Saved", vbOKOnly + vbInformation, "Reservation Saved"
'    DoCmd.Close
'Else
'    'Display error message to user
'    MsgBox "The dates you have selected clash with an existing booking/reservation. Please choose different dates.", vbOKOnly + vbExclamation, "Reservation Clash"
'    'Cancel
'    Cancel = True
'    Me.DateCheckedOut.SetFocus
'
'    Set rs = Nothing
'    Set db = Nothing
'
'    Exit Function
'End If
'
'Set rs = Nothing
'Set db = Nothing
'
'End Function
 
Public Function CheckValidReservation(Cancel As Integer)
 
Dim db As DAO.Database, rs As DAO.Recordset
Dim intnewrec As Integer
 
Set db = CurrentDb()
 
If dtDue < dtOut Then
    MsgBox "Due Date cannot be before the Reservation From date", vbOKOnly + vbExclamation, "Date Error"
    Cancel = True
    Exit Function
End If
 
If dtDue = dtOut And Format(dtIn, "Short Time") < Format(dtOut, "Short Time") Then
    MsgBox "Due Date cannot be before the Reservation From date", vbOKOnly + vbExclamation, "Time Error"
    Cancel = True
    Exit Function
End If
 
 
Set rs = db.OpenRecordset("SELECT EquipmentID, DateCheckedOut, DueDate, TimeOut, TimeIn, IsReservation FROM Loan WHERE EquipmentID='" & Me.EquipmentID & "' AND (IsReservation=True OR DateCheckedIn Is Null) AND (DateCheckedOut>=Date()) ORDER BY DateCheckedOut ASC")
 
StartDate = dtOut
EndDate = dtDue
TimeOut = Format(dtOut, "Short Time")
TimeIn = Format(dtIn, "Short Time")
 
test = False
 
If rs.RecordCount = 0 Then
    'Currently no existing bookings or reservations
    'Ok to save record and close
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.Close
    Exit Function
End If
 
rs.MoveFirst
 
Do Until rs.EOF
 
If StartDate = rs!dtOut And EndDate = rs!dtDue Then
    If Me.EquipmentID = "MLAP-SET01" Or Me.EquipmentID = "MLAP-SET02" Or Me.EquipmentID = "MTAB-SET01" Or Me.EquipmentID = "MTAB-SET02" Or Me.EquipmentID = "MTAB-SET03" Or Me.EquipmentID = "MTAB-676c" Or Me.EquipmentID = "MTAB-677c" Or Me.EquipmentID = "MTAB-678c" Or Me.EquipmentID = "MTAB-679c" Or Me.EquipmentID = "MTAB-680c" Or Me.EquipmentID = "MTAB-681c" Or Me.EquipmentID = "MTAB-682c" Or Me.EquipmentID = "MTAB-683c" Or Me.EquipmentID = "MTAB-684c" Or Me.EquipmentID = "MTAB-685c" Then
        'Perform additional time tests for netbook sets
        If IsNull(dtIn) Or IsNull(dtOut) Then
            MsgBox "Please enter time values", vbOKOnly + vbExclamation, "Enter Times"
 
            Cancel = True
            dtOut.SetFocus
            rs.Close
            Set rs = Nothing
            Set db = Nothing
            Exit Function
        Else
            If TimeOut = rs!dtOut Or TimeIn = rs!dtIn Then
                test = True
            ElseIf TimeOut >= rs!dtOut And TimeOut <= rs!dtIn Then
                test = True
            ElseIf TimeIn <= rs!dtIn And TimeIn >= rs!dtOut Then
                test = True
            End If
        End If
    Else
        test = True
    End If
ElseIf StartDate = rs!dtOut Or EndDate = rs!dtDue Then
    test = True
ElseIf StartDate >= rs!dtOut And StartDate <= rs!dtDue Then
    test = True
ElseIf EndDate <= rs!dtDue And EndDate >= rs!dtOut Then
    test = True
ElseIf StartDate <= rs!dtOut And EndDate >= rs!dtOut Then
    test = True
End If
 
If test Then
    rs.MoveLast
    rs.MoveNext
Else
    rs.MoveNext
End If
 
Loop
 
rs.Close
 
If test = False Then
    'Dates selected are ok
    'Continue to save record and close window
    DoCmd.RunCommand acCmdSaveRecord
    MsgBox "Reservation Saved", vbOKOnly + vbInformation, "Reservation Saved"
    DoCmd.Close
Else
    'Display error message to user
    MsgBox "The dates you have selected clash with an existing booking/reservation. Please choose different dates.", vbOKOnly + vbExclamation, "Reservation Clash"
    'Cancel
    Cancel = True
    dtOut.SetFocus
 
    Set rs = Nothing
    Set db = Nothing
 
    Exit Function
End If
 
Set rs = Nothing
Set db = Nothing
 
End Function
 
Private Sub Form_Load()
 
If Me.EquipmentID = "MLAP-SET01" Or Me.EquipmentID = "MLAP-SET02" Or Me.EquipmentID = "MTAB-SET01" Or Me.EquipmentID = "MTAB-SET02" Or Me.EquipmentID = "MTAB-SET03" Or Me.EquipmentID = "MTAB-676c" Or Me.EquipmentID = "MTAB-677c" Or Me.EquipmentID = "MTAB-678c" Or Me.EquipmentID = "MTAB-679c" Or Me.EquipmentID = "MTAB-680c" Or Me.EquipmentID = "MTAB-681c" Or Me.EquipmentID = "MTAB-682c" Or Me.EquipmentID = "MTAB-683c" Or Me.EquipmentID = "MTAB-684c" Or Me.EquipmentID = "MTAB-685c" Then
        Me.TimeOut.Visible = True
        Me.TimeIn.Visible = True

NeoPa, thanks for offering as a contact Crystal Long for possible on the job training. I like the sound of this, provided she knows how to offer advice on the Vba side and that she doesn't charge Bill Gates rates.

I will have a look at her play list tonight.

[jimatqsi]

Bitesboy, what is the problem? What is the error and how are you arriving at it? You click a button to make that happen?

I see that CheckValidReservationOld looks pretty good. But CheckValidReservation makes references to dtIn and dtOut without giving them any value. They are not the same variables as in CheckValidReservationOld because the "scope" of those variables is the subroutine they are defined in. So if the problem is occurring in CheckValidReservation you should start by copying your dtIn= and dtOut= code from CheckValidReservationOld.

Be clear about your current problem each time you post. We cannot know what you have done and what new result you are getting.

Jim

[NeoPa]

For help with code you need to be a lot clearer with what the problem is and generally need a fairly small amount of code. A code review of 250+ lines is not something many will get themselves into in their spare/volunteer time. If anyone does then they'd be looking for much clearer indications of what is what.

I say this, not to chasten or chastise, but to give you a better understanding of what you can reasonably expect help with, and what is likely to put people off from helping you. If you can identify where the problem is then people are generally prepared to spend some time helping you pinpoint it.

As for the Crystal thing - I'll PM you on that separately.