Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Feb 2002
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Duplicate in 3 fields (A2000)

    I am trying to prevent a user duplicating a record with 3 fields.
    Here's what the problem is.
    If in a restaraunt we try to double book a table, e.g

    Table "The ones with legs & chairs" (numeric)
    Date (Date Format)
    Time (Short Time Format)

    Ive set these as primary's with the following.
    Table-Indexed Yes(Duplicates Ok)
    Date - Indexed No
    Time - Indexed No
    Primary Table = tblRestarauntDetails
    This does the trick but pop's up the annoying Microsoft default error.

    I'm trying to trap this with after update on the forms BookingTime field

    Function ckDups()
    Dim reccount As Integer
    reccount = DCount("*", "tblrestarauntdetails", "tableno=" & Me.TableNo & "'and bookingdate=#" & Me.BookingDate & "# bookingtime=#" & Me.BookingTime & "#")
    If reccount > 0 Then
    MsgBox "This Table Is Already Booked"
    End If
    End Function

    I have moved the =ckdups to the after update of bookingtime.

    Syntax Error

    Can any-One Help

    Dave

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Duplicate in 3 fields (A2000)

    There seems to be a superfluous single quote just before and bookingdate= in the DLookup. However, your code would warn the user, but fail to cancel the update.

    There are several possible solutions.

    The best solution is probably to create a separate form to enter a new booking. This form should NOT be bound to the bookings table. ("Table" is a bit confusing in this context; I mean the database table here <img src=/S/smile.gif border=0 alt=smile width=15 height=15>)
    The form should contain a command button with caption "OK" or something like that. In the code behind this command button, you can check whether the booking is allowed. If so, add a new record to the table and save the values in it; otherwise warn the user.

    If you want one form to edit and add records, you can use the OnError event of the form to trap duplicates.
    Example:
    Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Select Case DataErr
    Case 3022
    ' Error 3022 = duplicate - warn user and undo new record
    MsgBox "Table already booked", vbCritical
    Me.Undo
    Response = acDataErrContinue
    Case Else
    ' Otherwise let Access handle the error
    Response = acDataErrDisplay
    End Select
    End Sub

    Instead of OnError, you might use the BeforeUpdate event of the form and set Cancel to True if there is a duplicate:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim reccount As Integer
    reccount = DCount("*", "tblrestarauntdetails", "tableno=" & Me.TableNo & "'and bookingdate=#" & _
    Me.BookingDate & "# bookingtime=#" & Me.BookingTime & "#")
    If reccount > 0 Then
    ' Warn the user
    MsgBox "This Table Is Already Booked"
    ' Cancel the update
    Cancel = True
    End If
    End Sub

    If this is not clear enough, don't hesitate to ask.

  3. #3
    Star Lounger
    Join Date
    Feb 2002
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate in 3 fields (A2000)

    Thanks Hans
    The Data Error has done the trick as follows

    Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Select Case DataErr
    Case 3022
    ' Error 3022 = duplicate - warn user and undo new record
    MsgBox "Table already booked", vbCritical
    Me.Undo
    Response = acDataErrContinue
    MsgBox "Pick Another Table"
    Me.Combo18.SetFocus



    Case Else
    ' Otherwise let Access handle the error
    'Response = acDataErrDisplay
    End Select

    End Sub

    Notice I have rem'd the " 'Response = acDataErrDisplay " section, Will this make much difference.

    Dave

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Duplicate in 3 fields (A2000)

    Hello Dave,

    Glad to be of help.
    Dim-ing ''Response = acDataErrDisplay' won't make a difference, since this is the default action. I just included it to be explicit.

    Just to be nitpicking: I would suggest combining the two MsgBox statements into one. Otherwise, the user has to click OK twice. For instance:
    MsgBox "Table already booked" & vbCrLf & "Pick Another Table", vbCritical

    Regards,
    Hans

  5. #5
    Star Lounger
    Join Date
    Feb 2002
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate in 3 fields (A2000)

    Hans
    Thanks
    This now works ok
    Dave

Posting Permissions

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