Cancel a new record

[Karen]

OK, I'm in a subform and I am in the process of adding a new record. As I tab out of a lastname textbox, I check the database to see if the name may be a duplicate. If there are possible duplicates, I open another form as a popup with the possible duplicates listed. If the user chooses one of the names in the list, I want to return to the subform, cancel/delete my current addrecord and add the name of the existing person.

When I get to the following code

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70

I get the following error

"The Command or action Select Record isn't available now"

How can I cancel a record?

 

[Ted]

Hi Karen,

It sounds like your record (and form for that matter) is
losing focus when going to the new form. One way to
handle this that comes to mind would be to capture the
record's field values to variables in your code and
cancel the record before even calling the second form.
Then, depending on what is chosen in the second form, you
could either add the user's original input or data from
the second form to a new record in your subform. It may
be easier to just add the new record than to locate the
original record that was being edited. There may be
other ways to do it that would be simpler. If so,
hopefully someone else will post as well.

-Ted

-----Original Message-----
OK, I'm in a subform and I am in the process of adding a

new record. As I tab out of a lastname textbox, I check
the database to see if the name may be a duplicate. If
there are possible duplicates, I open another form as a
popup with the possible duplicates listed. If the user
chooses one of the names in the list, I want to return to
the subform, cancel/delete my current addrecord and add
the name of the existing person.

 

[Karen]

Ted,

That's a great idea. Also I think I may have found the command I
want---Me.Undo--still checking it out.

Karen

 

[Neil]

Hello Karen,

I have a few questions before I can suggest a solution to your problem

When are you checking for duplicate values - in the forms before update or
the textbox's before update event? I would recomend the textbox beforeupdate
event.

Also, what do you mean by "I check the database to see if the name may be a
duplicate". Does this mean if someone enters Smith, you check to see if
Smith has already been entered and if so, you use this name instead?

At first glance, I would recommend using a combobox to lookup all second
names and a user then has to pick one. If they enter a name that isn't in
the list Access can then add this name to the combobox drop-down list.
(there are many questions and replies on the google newsgroups as to how to
do this - just search for "NotInList").

Depending on how you answer the above would depend on the best way to
proceed.

Neil.

 

[Karen]

Hi Neil,

When are you checking for duplicate values - in the forms before update or
the textbox's before update event? I would recommend the textbox beforeupdate
event.

I am checking in the afterupdate event, I can try the beforeupdate event.
In either case, the record has not been saved at this point. Can you
educate me on the advantage of checking beforeupdate rather than
afterupdate?

Also, what do you mean by "I check the database to see if the name may be a
duplicate". Does this mean if someone enters Smith, you check to see if
Smith has already been entered and if so, you use this name instead?

Exactly. Actually, I present the user with all of the names which may be a
match based on soundex of the last name.

At first glance, I would recommend using a combobox to lookup all second
names and a user then has to pick one. If they enter a name that isn't in
the list Access can then add this name to the combobox drop-down list.
(there are many questions and replies on the google newsgroups as to how to
do this - just search for "NotInList").

I decided not to use this route because the combobox really has to display
the full name for the user to recognize a match and somehow that just seemed
like it would be confusing.

Thanks for your suggestions.

Karen

 

[Neil]

Karen,

Using the before update to validate a field/value means that if you dont
want to save the changes, you can set the Cancel variable passed in to =
True and the event will automatically cancel (this applies to any control
including forms). For e.g.

Pivate Sub MyTextBox_BeforeUpdate(Cancel As Integer)

' Check a condition
If Me.MyTextBox = "Hello" Then
' Cancel the update
Cancel = True
' Undo the text box
Me.MyTextBox.Undo
End If

End Sub

In the above, the name of the text box is MyTextBox. If the user enters
Hello then the update event gets cancelled. If you didnt include the undo
line then if you pressed enter it would seem like nothing is happening. You
could display a message saying "Do not enter Hello" or something like that
e.t.c. If you use Me.Undo, this will undo ALL changes on the form (to the
last successful save). Specify the control as well and this will undo
changes made to the control only.

Going on your description of what you are trying to do, I would first ask
the user if they want to use the name they have entered before displaying
the form. For e.g.

Pivate Sub MyTextBox_BeforeUpdate(Cancel As Integer)

Dim intResponse As Integer

' Code goes here to check if there is a match already


' Ask the user if they want to use the value they have entered
intResponse = MsgBox("The name you have entered may already exist. Would
you like to view all possible matches?" _
, vbYesNo + vbQuestion, "Display possible
matches?")
' Check the response
If intResponse = vbYes Then
' Cancel the update
Cancel = True
' Undo the users input
Me.MyTextBox.Undo

' Code goes here to open form and view results

End If

End Sub

Hope this gives you an idea of how you could solve your problem. I could
give you an example of how to find an existing match if you need it.

Neil.

Hi Neil,

When are you checking for duplicate values - in the forms before update or
the textbox's before update event? I would recommend the textbox beforeupdate
event.

I am checking in the afterupdate event, I can try the beforeupdate event.
In either case, the record has not been saved at this point. Can you
educate me on the advantage of checking beforeupdate rather than
afterupdate?

Also, what do you mean by "I check the database to see if the name may

be

a
duplicate". Does this mean if someone enters Smith, you check to see if
Smith has already been entered and if so, you use this name instead?

Exactly. Actually, I present the user with all of the names which may be a
match based on soundex of the last name.

At first glance, I would recommend using a combobox to lookup all second
names and a user then has to pick one. If they enter a name that isn't in
the list Access can then add this name to the combobox drop-down list.
(there are many questions and replies on the google newsgroups as to how to
do this - just search for "NotInList").

I decided not to use this route because the combobox really has to display
the full name for the user to recognize a match and somehow that just seemed
like it would be confusing.

Thanks for your suggestions.

Karen

Hello Karen,

I have a few questions before I can suggest a solution to your problem

When are you checking for duplicate values - in the forms before update or
the textbox's before update event? I would recomend the textbox beforeupdate
event.

Also, what do you mean by "I check the database to see if the name may

be

 

[Karen]

Neil,

Thanks so much, I'll try the beforeupdate.

I already have code which is checking the name well, thanks for the offer.

Karen