Msgbox prompt for existing record

[Guest]

Hi there I would be extremely grateful if someone could point me in the right
direction - I am trying to display a message for when existing data is
entered: I keep getting error messages such as "you canceled the previous
operation"

Private Sub bkng_number_BeforeUpdate(Cancel As Integer)
Dim ians As Integer
If (IsNull(DLookup("[bkngnmbr]", "bookings", "[bkngnmbr] = " &
Me.bkng_number))) Then
ians = MsgBox("Booking number Already Exists! OK to Amend?," & vbCrLf & "OK
to Amend?," & "Cancel to enter New Booking Number", vbOKCancel)
Cancel = False
If ians = vbCancel Then
Me.Undo
Else
Me!bkngnmbr.Undo
'me!bkngnmbr.setfocus
Exit Sub
End If
End If

 

[Gijs Beukenoot]

From mel :

Hi there I would be extremely grateful if someone could point me in the right
direction - I am trying to display a message for when existing data is
entered: I keep getting error messages such as "you canceled the previous
operation"

Private Sub bkng_number_BeforeUpdate(Cancel As Integer)
Dim ians As Integer
If (IsNull(DLookup("[bkngnmbr]", "bookings", "[bkngnmbr] = " &
Me.bkng_number))) Then
ians = MsgBox("Booking number Already Exists! OK to Amend?," & vbCrLf & "OK
to Amend?," & "Cancel to enter New Booking Number", vbOKCancel)
Cancel = False
If ians = vbCancel Then
Me.Undo
Else
Me!bkngnmbr.Undo
'me!bkngnmbr.setfocus
Exit Sub
End If
End If

You can't .undo in the beforeupdate. Use the built-in Cancel from the
function :

if Nz(DLookup("[bkngnmbr]", "bookings", "[bkngnmbr] = " &
Me.bkng_number),"") <> "" Then
If MsgBox("Booking number Already Exists! OK to Amend?," & vbCrLf &
"OK to Amend?," & "Cancel to enter New Booking Number",
vbQuestion+vbOKCancel)
'Try again
Cancel = true
Me.bkngnmbr.setfocus
else
'Erm, this is from top of my head, might need to check the
parameters of findfirst
Dim rs as DAO.RecordSet
Set rs = me.recordsetclone
rs.findfirst "[bkngnmbr] = " & Me.bkng_number
me.bookmark = rs.bookmark
rs.close
set rs = nothing
endif
endif

 

[Gijs Beukenoot]

From Gijs Beukenoot :

From mel :

Hi there I would be extremely grateful if someone could point me in the
right direction - I am trying to display a message for when existing data
is entered: I keep getting error messages such as "you canceled the
previous operation"

Private Sub bkng_number_BeforeUpdate(Cancel As Integer)
Dim ians As Integer
If (IsNull(DLookup("[bkngnmbr]", "bookings", "[bkngnmbr] = " &
Me.bkng_number))) Then
ians = MsgBox("Booking number Already Exists! OK to Amend?," & vbCrLf & "OK
to Amend?," & "Cancel to enter New Booking Number", vbOKCancel)
Cancel = False
If ians = vbCancel Then
Me.Undo
Else
Me!bkngnmbr.Undo
'me!bkngnmbr.setfocus
Exit Sub
End If
End If

You can't .undo in the beforeupdate. Use the built-in Cancel from the
function :

if Nz(DLookup("[bkngnmbr]", "bookings", "[bkngnmbr] = " & Me.bkng_number),"")
<> "" Then
If MsgBox("Booking number Already Exists! OK to Amend?," & vbCrLf & "OK to
Amend?," & "Cancel to enter New Booking Number", vbQuestion+vbOKCancel)
'Try again
Cancel = true
Me.bkngnmbr.setfocus
else
'Erm, this is from top of my head, might need to check the parameters
of findfirst
Dim rs as DAO.RecordSet
Set rs = me.recordsetclone
rs.findfirst "[bkngnmbr] = " & Me.bkng_number
me.bookmark = rs.bookmark
rs.close
set rs = nothing
endif
endif

Hmm, I mixed up two comments. The else part is searching the recordset
and showing the entered record in the form. Not sure if you needed
that...

 

[Graham R Seach]

But you can Undo the control's change:
Me!bkng_number.Undo
Cancel = True

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

From mel :

Hi there I would be extremely grateful if someone could point me in the
right direction - I am trying to display a message for when existing data
is entered: I keep getting error messages such as "you canceled the
previous operation"

Private Sub bkng_number_BeforeUpdate(Cancel As Integer)
Dim ians As Integer
If (IsNull(DLookup("[bkngnmbr]", "bookings", "[bkngnmbr] = " &
Me.bkng_number))) Then
ians = MsgBox("Booking number Already Exists! OK to Amend?," & vbCrLf &
"OK to Amend?," & "Cancel to enter New Booking Number", vbOKCancel)
Cancel = False
If ians = vbCancel Then
Me.Undo
Else
Me!bkngnmbr.Undo
'me!bkngnmbr.setfocus
Exit Sub
End If
End If

You can't .undo in the beforeupdate. Use the built-in Cancel from the
function :

if Nz(DLookup("[bkngnmbr]", "bookings", "[bkngnmbr] = " &
Me.bkng_number),"") <> "" Then
If MsgBox("Booking number Already Exists! OK to Amend?," & vbCrLf & "OK
to Amend?," & "Cancel to enter New Booking Number", vbQuestion+vbOKCancel)
'Try again
Cancel = true
Me.bkngnmbr.setfocus
else
'Erm, this is from top of my head, might need to check the parameters
of findfirst
Dim rs as DAO.RecordSet
Set rs = me.recordsetclone
rs.findfirst "[bkngnmbr] = " & Me.bkng_number
me.bookmark = rs.bookmark
rs.close
set rs = nothing
endif
endif