Checking For Existing Value In Table

[LarryDK]

I am trying to see if a field on a form has the same value as a field in a particular table. The field in the table is indexed unique and Access provides messages that the field is a duplicate, but I need to trap the error and inform the user that they entered a duplicate value.

I had a routine which worked under Access 97 which ran a query and made a record in a work table if the value was in the master table, counted the number of rows and either displayed a message and exited or went on with the processing.

There is probably a better way to do this.

I appreciate any suggestions.

Larry

 

[TonyJollans]

Hi LarryDK,

There is usually a better way to do it but I'm not sure I fully follow what you are doing so rather than try I'll suggest something else.

If it worked in 97 and not later, you might need to change some DIMs in your code to be DAO.Recordset-or-QueryDef-or-Whatever. This is because the default is ADO and you must explicitly state DAO if you want to use it.

Enjoy,
Tony

 

[GKProgrammer]

How about just looping through the records in the table like this:

Dim rst as DAO.Recordset
Set rst = CurrentDb.OpenRecordset("tblYourTable&quot

With rst
.movefirst
While .EOF = False
If Me.YourContorlName = !YourFieldName then
MsgBox "Your Message here to declare duplicates"
....do whatever
End if
.Movenext
Wend
End With
set rst = nothing

Let me know if this helps.

Regards,
gkprogrammer

 

[LarryDK]

Thanks Guys!!

Tony, you were right.. My code was old for this version of Access, which I could have fixed, but gkprogrammer had a better way of doing it than I did, anyway, so I adopted his code and it works great.

I am only a sometimes Access VB programmer, so I really appreciate help by guys like you.

Thanks a lot!
Larry