Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Apr 2004
    Location
    Scotland
    Posts
    24

    Angry Unanswered: Finding Duplicate Bookings from a form??

    NEWBIE TO THIS FORUM!!

    Hi all

    I am trying to create a query that will check the values entered into a forms combo boxes AGAINST a booking table and check to see if there are any duplicate entries.

    Easy and i've done this bit -

    The hard bit is that i need to be able to distinguish between mulitple hire times for one aircraft within a given date.

    ie an aircraft "G-BGIY" on [HireDate] can be booked between the hours of #09:00:00# and #10:00:00# but also between #12:00:00# and #13:00:00# - how do i change the following code to allow these two bookings to occur??

    --------Code is---------

    Private Sub ConfirmBooking_Click()
    Dim strSql As String
    Dim rstFound As DAO.Recordset

    strSql = "Select AcReg FROM BookingDetails where [AcReg] = '" & cboAcReg & "'" _
    & " AND ([HireDate] = #" & cboHireDate & "# " _
    & " AND [StartTime] >= #" & cboEndTime & "# AND [EndTime] <= #" & cboStartTime & "#) "

    Set rstFound = CurrentDb.OpenRecordset(strSql)

    If rstFound.RecordCount > 0 Then
    Call MsgBox("Sorry this aircraft is already booked, please choose another time", vbCritical, "Error")
    Else
    Call MsgBox("Your booking is confirmed", vbExclamation, "Booking Confirmed")
    DoCmd.GoToRecord , , acNewRec

    End If

    End Sub

    --------TABLES----------
    tbl BookingDetails
    AcReg
    HireDate
    StartTime
    EndTIme

    ------QUERY--------

    SELECT BookingDetails.MemberNo, BookingDetails.AcReg, BookingDetails.HireDate, BookingDetails.StartTime, BookingDetails.EndTime
    FROM BookingDetails;

    This is the query i want to use the given code to find any duplicates from - at the moment it seems to get stuck on the HireDate field and will not allow 2 entries for the same day.

    Thanks in advance!
    Cam

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Question Re: Finding Duplicate Bookings from a form??

    Originally posted by noddy417
    NEWBIE TO THIS FORUM!!

    Hi all

    I am trying to create a query that will check the values entered into a forms combo boxes AGAINST a booking table and check to see if there are any duplicate entries.

    Easy and i've done this bit -

    The hard bit is that i need to be able to distinguish between mulitple hire times for one aircraft within a given date.

    ie an aircraft "G-BGIY" on [HireDate] can be booked between the hours of #09:00:00# and #10:00:00# but also between #12:00:00# and #13:00:00# - how do i change the following code to allow these two bookings to occur??

    --------Code is---------

    Private Sub ConfirmBooking_Click()
    Dim strSql As String
    Dim rstFound As DAO.Recordset

    strSql = "Select AcReg FROM BookingDetails where [AcReg] = '" & cboAcReg & "'" _
    & " AND ([HireDate] = #" & cboHireDate & "# " _
    & " AND [StartTime] >= #" & cboEndTime & "# AND [EndTime] <= #" & cboStartTime & "#) "

    Set rstFound = CurrentDb.OpenRecordset(strSql)

    If rstFound.RecordCount > 0 Then
    Call MsgBox("Sorry this aircraft is already booked, please choose another time", vbCritical, "Error")
    Else
    Call MsgBox("Your booking is confirmed", vbExclamation, "Booking Confirmed")
    DoCmd.GoToRecord , , acNewRec

    End If

    End Sub

    --------TABLES----------
    tbl BookingDetails
    AcReg
    HireDate
    StartTime
    EndTIme

    ------QUERY--------

    SELECT BookingDetails.MemberNo, BookingDetails.AcReg, BookingDetails.HireDate, BookingDetails.StartTime, BookingDetails.EndTime
    FROM BookingDetails;

    This is the query i want to use the given code to find any duplicates from - at the moment it seems to get stuck on the HireDate field and will not allow 2 entries for the same day.

    Thanks in advance!
    Cam
    I can understand the different parts of your post but am having a difficult time trying to put them together.

    Is your procedure going to reference the query instead of the BookingDetails table?

    What does "getting stuck" refer to?

    Is your query, when run by itself, "getting stuck" on the HireDate field or when you use it in code?

    Are you just not able to add multiple days to the same table with different times? You should include one of the times or some sort of a booking number in the primary key if this is the case.

    Maybe I'm just not seeing the forest for the trees yet.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  3. #3
    Join Date
    Apr 2004
    Location
    Scotland
    Posts
    24

    Angry Duplicates

    basicmek

    sorry i've re-read my post and can saee that i'm not been clear.

    The code is to run indepandantly behind a button looking at the fields contained within the BookingDetails table.

    ------quote------

    Are you just not able to add multiple days to the same table with different times? You should include one of the times or some sort of a booking number in the primary key if this is the case.

    ------------

    Correct thats exactly what i'm trying to do - multiple entries on one day depending on time.

    The query and code work - however when the code runs it is not able to detect when there are overlaps on the booking times and so returns the "Your booking is confirmed" msgbox


    I have put the code to show that i'm trying to check to see if there is no overlap of booking time.

    The query is there to show that i have a query set up in case there is an easier way to do accompish what i'm trying to do.

    I'm just very confused and as you say the forest is clouding my view of the trees

    If you can help me fantastic, if you need me to be a little clearer np.


    Cam
    ps
    I can send you the prog if it'll help explain what i'm trying to do (i tried to attach it but it couldn't be unzipped! -hence i removed it - sorry)
    Last edited by noddy417; 04-02-04 at 06:38.

  4. #4
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Re: Duplicates

    Originally posted by noddy417
    basicmek

    sorry i've re-read my post and can saee that i'm not been clear.

    The code is to run indepandantly behind a button looking at the fields contained within the BookingDetails table.

    ------quote------

    Are you just not able to add multiple days to the same table with different times? You should include one of the times or some sort of a booking number in the primary key if this is the case.

    ------------

    Correct thats exactly what i'm trying to do - multiple entries on one day depending on time.

    The query and code work - however when the code runs it is not able to detect when there are overlaps on the booking times and so returns the "Your booking is confirmed" msgbox


    I have put the code to show that i'm trying to check to see if there is no overlap of booking time.

    The query is there to show that i have a query set up in case there is an easier way to do accompish what i'm trying to do.

    I'm just very confused and as you say the forest is clouding my view of the trees

    If you can help me fantastic, if you need me to be a little clearer np.


    Cam
    ps
    I can send you the prog if it'll help explain what i'm trying to do (i tried to attach it but it couldn't be unzipped! -hence i removed it - sorry)
    It seems to me that once you have the recordset rows that contain multiple bookings for the same day you can examine each of them against the new booking start and end times.

    In other words, you could use a looping structure and with each row in the recordset (rstFound in this case), check the new start and end times to see if they are anywhere in between the start and end times for the current row.

    If you want to use this approach, it's not that difficult to code. My brain can't think fast enough this morning to figure an easy way to use the query. There probably is though.

    I'll check back later to see what you came up with or if someone else chimed in.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  5. #5
    Join Date
    Feb 2004
    Location
    CT,USA
    Posts
    250
    Before the line:

    If rstFound.RecordCount > 0 Then

    Put:

    rstFound.moveLast


    That should fix your code.

  6. #6
    Join Date
    Apr 2004
    Location
    Scotland
    Posts
    24

    Unhappy

    That rstFound.MoveLast returned no current record - yet my booking table is populated with 6 records.

    i tried

    rstfound.movefirst
    do until rstfound.eof
    ------
    -----
    ----
    rstfound.movenext
    loop

    and that didn't work either!

    Any other ideas??

    Cam

  7. #7
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445
    Originally posted by noddy417
    That rstFound.MoveLast returned no current record - yet my booking table is populated with 6 records.

    i tried

    rstfound.movefirst
    do until rstfound.eof
    ------
    -----
    ----
    rstfound.movenext
    loop

    and that didn't work either!

    Any other ideas??

    Cam
    Why don't you just try to find the records that fit that aircraft and that date with your recordset?

    Then you can examine each record returned for a conflict with the current booking.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  8. #8
    Join Date
    Apr 2004
    Location
    Scotland
    Posts
    24
    Could you expand on your last post please - i've been trying to get this code/query to work for ages and it ain't going in any more!!

    I can't upload a zip file of my form for you to see - PM me if you think it'll help you understand what i'm doing and i'l send you it!
    Thanks again

  9. #9
    Join Date
    Feb 2004
    Location
    CT,USA
    Posts
    250
    Try trouble shooting this way(After trying the next post by basicmek):

    1) add a debug.print strSql prior command in 2) below
    2) put a break a command "Set rstFound = CurrentDb.OpenRecordset(strSql)"
    2b) run the code at the break copy the strSQL (may need to stop code)
    3) paste your strSql into a new query in SQL view mode, fix any command syntax to build your query
    4) run the query

    Do you get hits? error messages? can you see what is missing in design veiw perhaps?

    HTH
    Last edited by poliarci; 04-02-04 at 18:36.

  10. #10
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by noddy417
    Could you expand on your last post please - i've been trying to get this code/query to work for ages and it ain't going in any more!!

    I've added a sample form for you to have alook at!
    First, I didn't see the form.

    Second. OK, if I understand you correctly, you are trying to keep from booking the same aircraft during the same time period. If there is an overlap, then that won't work. Ok so far?

    If you open a recordset like you were doing, but get all the bookings for that particular day, if your recordcount is 0 you can go ahead and book. And, yes, poliarci was right with a DAO recordset you will need to move last to get the accurate recordcount. You will also have to move first after that if your recordcount returns more than 0 records.

    You still need to include the StartTime and EndTime in the recordset so you can compare to them but you don't want to restrict the criterea initially.

    If you return more that 0 records then start a loop.

    Using x as a counter for instance...

    rsFound.MoveFirst

    For x = 1 to rsFound.Recordcount
    If the current StartTime is greater than rsFound.Fields("StartTime") and less than rsFound.Fields("EndTime") OR the current EndTime is Greater than....you get the picture.
    Process the overlap message and exit the procedure
    Exit Sub
    Else
    rsFound.MoveNext 'Move to the next recordset record.
    End If
    Next

    If you get this far, then you can book the aircraft for the current times entered.

    You also need to check for rsFound.EOF or just loop using it like you were doing.

    I hope this is readable enough. Formatting sucks. I'm in a hurry or I'd try to get you the exact syntax but this is the gist of what I'm talking about.

    Hope this gets you in the ball park. If not, and you could post the db or Email it to me, I'm sure we can get it straight.

    Good luck.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  11. #11
    Join Date
    Apr 2004
    Location
    Scotland
    Posts
    24

    Unhappy where have i gone wrong??

    Ok update - here is the code as suggested, when i run this i get an error number 3265 Item not found....

    Why?

    Is it the placement of my loop or have i jsut written this incorrectly?

    Thanks again
    Cam

    ps What is the max size for an attachment to these forums?
    http://www.angelfire.com/alt2/noddy417/DB/test.zip for my file
    -------------------------------------------------------------------------------
    Private Sub ConfirmBooking_Click()

    Dim strSql As String
    Dim rstFound As DAO.Recordset

    strSql = "Select AcReg FROM BookingDetails where [AcReg] = '" & cboAcReg & "'" _
    & " AND ([HireDate] = #" & cboHireDate & "# " _
    & " AND [StartTime] >= #" & cboStartTime & "# AND [EndTime] <= #" & cboEndTime & "#) "

    Set rstFound = CurrentDb.OpenRecordset(strSql)

    If rstFound.RecordCount > 0 Then
    rstFound.MoveFirst

    Do Until rstFound.EOF
    For X = 1 To rstFound.RecordCount

    If rstFound.Fields("StartTime") >= "#" & cboStartTime & "#" Or rstFound.Fields("EndTIme") >= "#" & cboEndTime & "#" Then <<<<<DEBUG HIGHLITE IS HERE

    Call MsgBox("Sorry this aircraft is already booked, please choose another time", vbCritical, "Error") 'Process the overlap message and exit the procedure
    Exit Sub

    Else
    rstFound.MoveNext 'Move to the next recordset record.
    End If

    Loop

    Else
    Call MsgBox("Your booking is confirmed", vbExclamation, "Booking Confirmed")
    DoCmd.GoToRecord , , acNewRec
    End If

    End Sub
    ------------------------------------------
    Last edited by noddy417; 04-04-04 at 12:06.

  12. #12
    Join Date
    Feb 2004
    Location
    CT,USA
    Posts
    250

    Re: where have i gone wrong??

    Originally posted by noddy417
    Ok update - here is the code as suggested, when i run this i get an error number 3265 Item not found....

    If rstFound.Fields("StartTime") >= "#" & cboStartTime & "#" Or rstFound.Fields("EndTIme") >= "#" & cboEndTime & "#" Then <<<<<DEBUG HIGHLITE IS HERE

    ------------------------------------------
    Perhaps, one of two things is required to test:
    1) Make the command as:
    If (rstFound.Fields("StartTime") >= "#" & cboStartTime & "#") Or (rstFound.Fields("EndTIme") >= "#" & cboEndTime & "#") Then

    [Just add the extra parenth.]

    2) You may have your combo boxes bound to the index and not the litteral string. When you get the debug highlight place cursor over each variable-- a yellow flag should tell you the value after a second or two, or go to view debug window and inspect the variables there.

    What do you see for the cbo's?

    Pls, upload file.

  13. #13
    Join Date
    Apr 2004
    Location
    Scotland
    Posts
    24

    Re: where have i gone wrong??

    Pls, upload file.
    Here you go i think i've managed to get it uploaded at last
    Attached Files Attached Files
    Last edited by noddy417; 04-05-04 at 15:21.

  14. #14
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Re: where have i gone wrong??

    Originally posted by noddy417
    Here you go i think i've managed to get it uploaded at last
    I finally got it.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  15. #15
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Finding Duplicate Bookings from a form??

    Originally posted by noddy417
    NEWBIE TO THIS FORUM!!

    Hi all

    I am trying to create a query that will check the values entered into a forms combo boxes AGAINST a booking table and check to see if there are any duplicate entries.

    Easy and i've done this bit -

    The hard bit is that i need to be able to distinguish between mulitple hire times for one aircraft within a given date.

    ie an aircraft "G-BGIY" on [HireDate] can be booked between the hours of #09:00:00# and #10:00:00# but also between #12:00:00# and #13:00:00# - how do i change the following code to allow these two bookings to occur??

    --------Code is---------

    Private Sub ConfirmBooking_Click()
    Dim strSql As String
    Dim rstFound As DAO.Recordset

    strSql = "Select AcReg FROM BookingDetails where [AcReg] = '" & cboAcReg & "'" _
    & " AND ([HireDate] = #" & cboHireDate & "# " _
    & " AND [StartTime] >= #" & cboEndTime & "# AND [EndTime] <= #" & cboStartTime & "#) "

    Set rstFound = CurrentDb.OpenRecordset(strSql)

    If rstFound.RecordCount > 0 Then
    Call MsgBox("Sorry this aircraft is already booked, please choose another time", vbCritical, "Error")
    Else
    Call MsgBox("Your booking is confirmed", vbExclamation, "Booking Confirmed")
    DoCmd.GoToRecord , , acNewRec

    End If

    End Sub

    --------TABLES----------
    tbl BookingDetails
    AcReg
    HireDate
    StartTime
    EndTIme

    ------QUERY--------

    SELECT BookingDetails.MemberNo, BookingDetails.AcReg, BookingDetails.HireDate, BookingDetails.StartTime, BookingDetails.EndTime
    FROM BookingDetails;

    This is the query i want to use the given code to find any duplicates from - at the moment it seems to get stuck on the HireDate field and will not allow 2 entries for the same day.

    Thanks in advance!
    Cam
    I sent your Db back with the changes included. Here's the change to the SQL statement. It seems like it should be simpler than this but here it is. I just included the Start and End times so I could look at their values while I was testing. It seemed to work for me.

    strSql = "Select AcReg, StartTime, EndTime FROM BookingDetails where [AcReg] = '" & cboAcReg & "'" _
    & " AND [HireDate] = #" & cboHireDate & "# " _
    & "AND (([StartTime] <= #" & cboStartTime & "# And [EndTime] >= #" & cboStartTime & "#)" & _
    "Or ([StartTime] <= #" & cboEndTime & "# And [EndTime] >= #" & cboEndTime & "#))"

    Ya gotta love the formatting.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •