Add record but check for existing record first

[Compuhelpmt]

I have an add customer button that inserts a new record which works fine:

Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

But I would like it to check a home_phone field first for an existing phone
number. If the number is already in the table, then a new record isn't
added, and the form jumps to that record. If the number isn't in the table,
then a new record is added with the new phone number "pasted" in.

 

[Guest]

from Rainbow01 Hong Kong

first assume phone no is your primary index key in your table

my suggestion:
make a form for input phone no. only
have 1 TextBox(PhoneNo) and 2 Button(Confirm and Close)

in Confirm_OnClick event:
1) check PhoneNo was input or not, if not, show MsgBox to user
2) you can use Dlookup function to check the PhoneNO is exist or not in table
if exist, open another form(assume this form call PhoneRecordForm) to
show that phoneno record
if not exist, that mean need to add new record, open PhoneRecordForm for
AddNew.

"Compuhelpmt" 來函：