Check for Record

[bunji]

Hi Everyone

i would like to check a record exits before a new entry is saved. However i would like to check on three criterias and i am not sure on how to write the if statement.

IF Dcount "CompanyName" & "FirstName" & "Last Name" from "tblcustomer" is the same as "frmCompanyName" & "frmFirstName" & "frmLastName" > 0

Then

Msgbox "This Customer Already Exists"
Cancel = True
frmcompanyname.setfocus

End IF

Could someone please translate it into the correct code?

Many Thanks

 

[MStef]

I suggest you to make one INDEX on these three field, Unique = Yes.

 

[bunji]

Can you have 3 fields all linked together as unique because if i just set one field this wont verify as you can have many people from the same company or the same name but from different companys.

I have got round this by setting the company name first name and last name as the primary key but if someone entered a second entry with the same details, it gives the default error message that Access produces. What i would like to do is just say "That person already exists in the database". I think trying to capture the default error message and produce my one is a bit beyond me so does anyone have any ideas

 

[MStef]

Hello bunji!
Look at DemoIndexA2000.mdb
(MStef alias Štef).

 

[bunji]

Hi that works thanks