Go to duplicate records on entry into form

M

mel

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!
 
A

Alex Dybenko

try:
Me.RecordsetClone.FindFirst "bkngnmbr = " & Varbkngnmbr
if not Me.RecordsetClone.nomatch then
me.bookmark=Me.RecordsetClone.bookmark
end if
 
M

mel

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



David C. Holley said:
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!
 
D

David C. Holley

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!
 
D

David C. Holley

Omitted from the post (hard to believe I know), but the code I posted is
designed to display a form with the record details.
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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top