checking to see if the record exists 1

[skierm]

How, when im in add mode, after i finish entering all my inofrmation can i check to see if some other record already exists and not add the record i just yped in.

 

[scriverb]

Yes, you can. It is best to do this right after you input the infor that makes up your unique index for the record. Like SS#, ClientID#, etc. So, in the AfterUpdate of the field/last field that comprise this unique identifier put this code with updates for your tables and field names:

Dim db as DAO.Database
Dim rs as DAO.Recordset
Set db = CurrentDB
Set rs = db.OpenRecordset("tblYourTable", dbOpenDynaset)
rs.FindFirst("[YourFieldNzme]= '" & Me![FormControlName] & "'"
If rs.NoMatch then
'No matching record in table CONTINUE with Add
else
'Match found - This add would be a duplicate
MsgBox "This record is a duplicate of one already in the table"

Me![FormControlName].SetFocus
End If
rs.close
db.close

This example examines the value in the unique control to the table and if a match is found then displays a warning message and returns control to the control on the form. If no match is found then the focus continues on to the next control and the record can be filled in and saved.

Please post back if you have any troubles with this code.



Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[skierm]

the line where it says dim db as dao.database, my computer is yelling at me compile error user defined type not defined

 

[scriverb]

You are probably using A2k or higher. This is DAO code. You must have a Library reference selected for DAO 3.6. Open a form in design view. Click on the Code button to enter the VBA code window. From the Tools menu select References. Here you should scroll down to the MS DAO 3.6 Object Library. Click the Checkbox to select it. Close everything up and try the code again.



Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[skierm]

now im having trouble with the rs.findfirst. what is the Me![FormControlName] and do i need all those brackets.

 

[scriverb]

The Me![FormControlName] refers to the control on your form that has the information that is unique about your record. ME is a shorthand for FORMS![frmYourFormName] with the name of your form within the square brackets. The square brackets in the ME above are not needed as long as your control name doesn't have any spaces in it.

Example:
Control Name: Client ID Me![Client ID]

Control Name: ClientID Me!ClientID

Both of the above examples are correct.





Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[skierm]

thank you very much. I got it working.

 

[scriverb]

My pleasure. Let me know if you experience any problems with the code.

One thing I would like to point out in the criteria for the rs.FindFirst. The code I povided is for a field that is text or non-numeric. Thus the reason for the single quotes around the form field value. If the criterias table field is numeric then the format should look like this:

Numeric field:
rs.FindFirst("[YourFieldNzme]= " & Me![FormControlName])

Please post back with any questions.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[MelissaKT]

Bob,
I know that this is an older post, just wanted to let you know that you have helped me yet again! Thank you!
Melissa

 

[scriverb]

My please MelissaKT. I was surprised to see this pop up again. It's been a while. I have been away from the forum quite a bit lately and surprised to see a Tek-tips email notification. Good luck.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]