Time Interval Overlapping (MS Access 2003)

[NeoPa]

Zac Harvey:
So the Move methods should come after the if statement? (If rsDao.RecordCou nt = 0 Then)

Definitely not. The code checking RecordCount checks for 0 - not >0. In fact, the MoveLast and MoveFirst are probably unnecessary in this case. Read on.

Allen Browne explained the half of the story he dealt with. Let's see if we can give the complete picture for your perspective.

If rsDao.RecordCount > 0 Then

or even

If Not (rsDao.BOF And rsDao.EOF) Then

indicate that the recordset has records (although at this stage the count itself would be unreliable - See point #4 of Allen Browne's article you linked to). If an accurate reflection of the actual count is required (which I do not believe to be the case in this scenario) then Call rsDao.MoveLast is required. Of course, this code shouldn't be executed without first checking that the recordset is empty otherwise it will fail.

It seems then, that accurately determining the count in most recordsets (dbOpenTable is an exception) requires both techniques (Check records exist; Move to last). In this case though, I suspect you simply need the check, as the actual count is irrelevant. I would suggest, due to the confusing nature of what is required, that determining whether or not a recordset has any records should be done using the second of the two methods shown (If Not (rsDao.BOF And rsDao.EOF) Then) rather than the first. Disambiguation is definitely an aim to strive for when writing code unless you are interested in ensuring people don't understand it.

PS. ** Edit **
Having reread and realised I missed something in the code, I would say here that, for the purposes of disambiguation (still very important) I would recommend using the first approach when simply determining if records exist, and the second prior to determining the accurate count of the recordset. They both do fundamentally the same job of course, but the first seems clearer as an indication of what's required. At the end of the day though, how it reads to you the developer is the most important factor here, so make your own choices.

[Zac Harvey]

Ok here's my revised code. It seems to do the job. I know my code is likely not the best writen and ordered but as I mentioned origionaly I am still pretty new to Access programming.

'If form is dirty, before going to new record check current

Private Sub cmdSave_Click()

    If Me.Dirty Then
        Dim strWhere As String, strMsg As String
            With Me
            strWhere = "(([StartTime]<#%S#) AND " & _
                       "([EndTime]>#%E#) AND " & _
                       "([RoomID]=%R) AND " & _
                       "([BookingDate]=#%D#))"
            strWhere = Replace(strWhere, "%S", Format(.EndTime, "HH:mm:ss"))
            strWhere = Replace(strWhere, "%E", Format(.StartTime, "HH:mm:ss"))
            strWhere = Replace(strWhere, "%R", .RoomID)
            strWhere = Replace(strWhere, "%D", Format(.BookingDate, "mm/dd/yy"))
            
            
            Dim rsDao As DAO.Recordset
            Set rsDao = CurrentDb.OpenRecordset("SELECT * FROM [tblBookings] WHERE " & strWhere, dbOpenDynaset)
            
            
            'Move to last record to ensure that recordset has been populuated.
            'This is needed because we wish to access the recordcount property
            

            If rsDao.RecordCount = 0 Then
                'Booking is ok.
                DoCmd.Save
                DoCmd.GoToRecord , , acNewRec
                Exit Sub
            Else
                'Booking is not ok
                
                Do While Not rsDao.EOF 'Do until we reach the eof, (End Of File)
                    strMsg = strMsg & vbNewLine & "%D between [%S] and [%E] by '%B'"
                        strMsg = Replace(strMsg, "%D", Format(rsDao!BookingDate, "dd-mmm-yy"))
                        strMsg = Replace(strMsg, "%S", Format(rsDao![StartTime], "HH:mm"))
                        strMsg = Replace(strMsg, "%E", Format(rsDao![EndTime], "HH:mm"))
                        strMsg = Replace(strMsg, "%B", Format(rsDao!BookedBy, "HH:mm"))
                
                    rsDao.MoveNext
                Loop
            
            End If
            
            'Cancel entry
                Me.Undo
                
            'Inform user
                strMsg = "Sorry, the booking could not be made. The room is allready booked on:" & strMsg
                MsgBox strMsg
                
            'Cleanup
                Set rsDao = Nothing
                
                
            End With
    End If
End Sub

[TheSmileyCoder]

@ NeoPa
Thank you for correcting the mistakes I made.

@ Zac
Good to see you got it working. One thing I need to note is that:

Docmd.Save

will save your object (the form in this case) not the record. You can use:

docmd.RunCommand acCmdSaveRecord

to save the record.

[NeoPa]

TheSmileyCoder:
Thank you for correcting the mistakes I made.

They were few enough Smiley :-)

To be honest I had to read through the linked Allen Browne article to get the full understanding myself. It's not like they were obvious or simple errors, but rather understandings of where Access itself lets the developer down.

[Zac Harvey]

Thanks a lot guys, your help has been most appreciated. I shall give mention to the website and yourselves for all the help. I'm almost there, but I've still a few glitches to iron out. I'm hoping you can still find the time to take a look at these. They will follow on from this post.

[Zac Harvey]

Ok, so first problem is on my form which is used to ammend booking details after they have been made. There is a search facility on the form allowing the user to find bookings records easily. However when they enter a search string that doesn't exist the form is returned completely blank and can't be closed. I will attatch it so you can actually try it for yourself rather than show you by pictures.

[*EDIT*] I have solved the above problem. Turns out it was happening because I had set AllowAdditions to No, and as such when the search returned no records it couldn't show a blank one. So I've got round it by allowing additions and putting 'Cancel = true' in the BeforeInsert form event.

Note: I have actually uploaded my full database, so if you wanted to take a look at it as a whole and just tell me what you think in general that would also be a great help.

Cheers

[NeoPa]

I think we can ignore the extra question you've already answered Zac. I've moved the new one to its own thread (Date Validation) though. Please remember for future questions.

I'll also leave the posted db. Generally that's a great deal to ask, as it involves a lot of work for the benefit of a single member, but we can all choose whether or not to respond so I'll leave it in place in case anyone is interested.

[Zac Harvey]

Okay, no problem. I wasn't assuming you'd look. I didn't want you to inspect the code or anything just have a little try of it and see what you thought. But it's no worry. Thanks again Neo and Smiley.

[NeoPa]

No worries Zac. If I do get an idle moment I may give it a quick look. Those idle moments are in pretty short supply just now mind.