You will save yourself MUCH, MUCH, MUCH headache making it the primary
key and instructing the users as opposed to the other way around. Trust me.
Second to that, how are you creating the BOOKING NUMBER? Is it generated
from one of the GDS's or other CRS and then manually entered? (And yes,
I have extensive travel experience.)
Third, here's a solution that I put together for a scenario similar to
yours to bypass the error message. The code then displays an advisory
form with the details to display the previously entered record.
In the form where the number is entered...
Private Sub Form_Error(DataErr As Integer, Response As Integer)
'Err #3022 - Duplicate key record in table (key has already been used)
'Code below displays the Inquiry information if the user attempted to
'add reservation information that is already in the database. This insures
'that all refund/credit information is tied back to one inquiry.
'Err #3314 - Code indicates that one of the fields which cannot be NULL
'is Null
Select Case DataErr
Case 3022
Response = True
Call ReservationInDatabase
GoTo Form_Error_Exit
Case 3314
Response = True
GoTo Form_Error_Exit
Case Else
End Select
Form_Error_Exit:
End Sub
In the form that displays the previously entered record...
Private Sub Close_Click()
On Error GoTo Err_Close_Click
DoCmd.Close A_FORM, "Record Advisory"
'SendKeys is bad, bad, bad...however at the time it was the only
'way to make it work. There should be a more graceful way of
'implementing this that doesn't use SendKeys however I'm not able to
'test it as I only have the code from the DB, not the DB itself
'My thought is that if you call ReservationInDatabase from the
'BeforeUpdate or AfterUpdate event of the BOOKING NUMBER field that
'it should work. In that case you won't need the FORM_ERROR event.
SendKeys "{Escape}"
If Isloaded("Reservation Capture") = True Then
[Forms]![Reservation Capture]![CRSNo].SetFocus
End If
Exit_Close_Click:
Exit Sub
Err_Close_Click:
ErrorMsg = "Close Click:" & Chr$(10) & Chr$(13) & Error$
MsgBox ErrorMsg, , "Record Advisory"
Resume Exit_Close_Click
End Sub
Private Sub Form_Load()
Dim LineReturn As String
Dim MsgText As String
On Error GoTo Err_Form_Load
LineReturn = Chr$(10) & Chr$(13)
MsgText = "The reservation number(s) entered have already been" & LineReturn
MsgText = MsgText & "entered into the database and attached to an
inquiry." & LineReturn
MsgText = MsgText & "Please review the inquiry and remarks for
additional" & LineReturn
MsgText = MsgText & "information pertaining to this reservation." &
LineReturn
MsgText = MsgText & "All information neccessary to locate the inquiry" &
LineReturn
MsgText = MsgText & "is displayed on this form." & LineReturn & LineReturn
MsgText = MsgText & "The information which you entered will NOT be saved."
MsgBox MsgText
Exit_Form_Load:
Exit Sub
Err_Form_Load:
ErrorMsg = "Form Load:" & Chr$(10) & Chr$(13) & Error$
MsgBox ErrorMsg, , "Record Advisory"
Resume Exit_Form_Load
End Sub
In a free standing module...
Sub ReservationInDatabase()
Dim CRSNoInput As String
On Error GoTo Err_ReservationInDatabase
If Isloaded("Reservation Capture") = True Then
CRSNoInput = [Forms]![Reservation Capture]![CRSNo]
CRSNoInput = CStr(CRSNoInput)
DoCmd.OpenForm "Record Advisory", , , "[CRSNo] =
[Screen]![ActiveForm]![CRSNo]"
GoTo Exit_ReservationInDatabase
End If
If Isloaded("Invoice Capture") = True And IsNull([Forms]![Invoice
Capture]![CRSNo]) = False Then
CRSNoInput = [Forms]![Invoice Capture]![CRSNo]
If DCount("CRSNo", "Reservation Info", "[CRSNo] = [Forms]![Invoice
Capture]![CRSNo]") <> 0 Then
CRSNoInput = CStr(CRSNoInput)
DoCmd.OpenForm "Record Advisory", , , "[CRSNo] =
[Screen]![ActiveForm]![CRSNo]"
End If
GoTo Exit_ReservationInDatabase
End If
Exit_ReservationInDatabase:
Exit Sub
Err_ReservationInDatabase:
MsgBox Error$
Resume Exit_ReservationInDatabase
End Sub
David H
P.S. Side humor - at one point the company that I worked for received a
complaint from a guest because the system generated PNR number just
happened form a certain phrase including the letters K-C-U-F. Needless
to say, the system was modified to skip a list of defined PNRs that were
deamed as undesireable.
Hi David
No it is not the primary key but yes it could be, seeing as there will never
be the same booking number. however, I can't have the messages display such
as "....can't save record....it will create
duplicates....relationships........" as I am designing a database for users
with little or no computer skills - hence why I simplified the message to
"booking number exists!....." Also I need that existing booking to display
so they can make changes if necessary.
I have changed my code but its still not bringing up the "existing" record
when I attempt a duplicate, any ideas? .......... [it displays the message
though]
Private Sub bkngnmbr_BeforeUpdate(Cancel As Integer)
Dim Varbkngnmbr As Variant
If Me.NewRecord Then
Varbkngnmbr = (DLookup("[bkngnmbr]", "[bookings]", "[bkngnmbr] = '" &
Me.bkngnmbr & "'"))
If Not IsNull(Varbkngnmbr) Then
If MsgBox("Booking number Already Exists!" & " OK to Amend?," & "Cancel to
enter New Booking", vbOKquestion + vbOKCancel, "BOOKING NUMBER EXISTS!") =
vbYes Then
Cancel = True
Me.RecordsetClone.FindFirst "bkngnmbr = " & Varbkngnmbr
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End If
End If
End If
End Sub
:
Are you using the BOOKING NUMBER field as the primary key?
I would really appreciate your help on this as I have been working on it for
weeks! I have a reservations system and I want to be able to detect existing
bookings if they are already there. Then I want to be able to bring up that
booking..... I have tried so many different things...... at the moment my
error message comes up, but does not go to the existing booking:
Private Sub bkngnmbr_BeforeUpdate(Cancel As Integer)
Dim Varbkngnmbr As Variant
If Me.NewRecord Then
Varbkngnmbr = (DLookup("[bkngnmbr]", "[bookings]", "[bkngnmbr] = '" &
Me.bkngnmbr & "'"))
If Not IsNull(Varbkngnmbr) Then
If MsgBox("Booking number Already Exists!" & " OK to Amend?," & "Cancel to
enter New Booking", vbOKquestion + vbOKCancel, "BOOKING NUMBER EXISTS!") =
vbYes Then
Cancel = True
Me.Recordset.FindFirst "bkngnmbr = " & Varbkngnmbr
'MOVE TO THE RECORD
End If
End If
End If
End Sub
eagerly awaiting anyone's helpful response!
