Msgbox prompt for existing record

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
Click to expand...

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
 
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
Click to expand...

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
Click to expand...

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...
 
But you can Undo the control's change:
Me!bkng_number.Undo
Cancel = True

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

Gijs Beukenoot said:
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
Click to expand...

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
Click to expand...
 
Back
Top