How to avoid duplicates before saving in an access table

[mister_T]

Good Day,

I have a Save button in my form to save new record to my table. I would like to have codes in cmdSave to check if the value that I type in LName and FName (unbound fields respectively) already exist in tblEmplyee table then it will display a message informing the user that the record already exist.

You help are greatly appreciated.

 

[jdraw]

You could use a DCount function and check if the count of the field combination is >0.
If DCount(....) > 0 then the record exists.

You should have an unique index on the combination of fields, then Access will trap the duplicate, you can include code to check for error code (I think 3022) then inform the user with a precise message.

 

[mister_T]

Hi jdraw,
I used DLookup to check Duplicates in my table before saving and it worked OK then it showed a message (example: SMITH already exist!). I would like to include also the the last name [LName] in my codes and msgbox. Is it possible in DLookup to check two fields (Text) or its better to use DCount.? I'm very much new in access.

 

[mister_T]

ScanCount = DLookup("[FName] & [LName]", "[tblEmployee]", "[EmpID] = Forms!frmAllStaff![EmpID]")

MsgBox (ScanCount) & " already exist!", vbInformation, "Verify Duplicate Record"

The code above works OK but don't know how put space between FName and LName in Msgbox. help

 

[gemma-the-husky]

out of interest, are you using an unbound form or a bound form?

 

[jdraw]

If dcount("*","Employee","FName = 'Jim' and LName = 'Jones'") > 0 Then
msgbox "That employee already exists"
end if

Alternatively,
dcount("*","Employee","Empid = 2")

Do you have a unique index on FName + LName to prevent duplicates?
Do you have a PK on the tblEmployee?

 

[mister_T]

to gemma-the-husky, all my textbox and combobox are unbound.

to jdrwa, my EmpID field is indexed (autoNumber). What is PK?

What I'm trying to achieve is to have a space between the FName and LName on the popup msgbox. In my MsgBox it shows as JOESMITH already exist!, I want to show it as JOE SMITH already exist!.

 

[gemma-the-husky]

misterT, assuming you are just starting out in Access - it would be much better to use a bound form. You still get the same issue with duplicates, but you will need a lot less code to manage your data in general.

eg - What code do you have on your "Save" button. How are you appending the record to the table?

Indeed you can address this problem just by adding a unique index to your table based on lastname, firstname in which case access will prevent you adding a duplicate automatically. Note that lastname, firstname for a unique index may not be the best though. eg What if there are 2 different John Smith's?.

There needs to be a very good reason to use unbound forms, so why did you decide to use an unbound form?