asked on

VB Coding issues for data validation in Access form

I have created a database to track inventory and the coding i obtained to ensure there are no duplicates in the table seemed to work initially and then for some reason change and I don't understand why. Below is the code that is applied to even before form update. Initially the data after the > was just 1 and it worked, but when i imported a new table, it began stating there was a duplicate when there wasn't so someone helped me modify to > (1 + me.newrecord), which worked. I have several fields i need to apply this to so i began testing them one at a time. although it appeared one record would allow it to submit when there was a duplicate, overall it worked well. When i uncommented the 3rd one(TLETS_SIM), it keeps flagging the msgbox even though there is not a duplicate. I do not understand why this is occurring. Is it because i have too many listed? Is there a way to combine them all into one action?

If you need me to post a sample database, let me know.

'Searches XT2_Inventory table to ensure air sim number is not already listed. If so, then it is flagged.
If DCount("Air_SIM", "XT2_Inventory", "Air_SIM=" & Chr(34) & Me.Air_SIM & Chr(34)) > (1 + Me.NewRecord) Then
MsgBox "Duplicate Air SIM on Regional Inventory", vbExclamation, "Duplicate Error"
Cancel = True

End If

' Searches XT2_Inventory table to ensure cell sim number is not already listed. If so, then it is flagged.

If DCount("Cell_SIM", "XT2_Inventory", "Cell_SIM=" & Chr(34) & Me.Cell_SIM & Chr(34)) > (1 + Me.NewRecord) Then
MsgBox "Duplicate Cell SIM on Regional Inventory", vbExclamation, "Duplicate Error"
Cancel = True

End If

' Searches XT2_Inventory table to ensure tlets sim number is not already listed. If so, then it is flagged.
If DCount("TLETS_SIM", "XT2_Inventory", "TLETS_SIM=" & Chr(34) & Me.TLETS_SIM & Chr(34)) > (1 + Me.NewRecord) Then
MsgBox "Duplicate TLETS SIM on Inventory", vbExclamation, "Duplicate Error"
Cancel = True

End If

ASKER CERTIFIED SOLUTION

als315

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jsawicki

ASKER

Perfect, thank you. Someone else is managing this aspect so he is making many changes that i can't keep track of and since he isn't experienced with access or coding, he doesn't understand how to systematically make changes. He is doing a sub project with similar data whereas i am doing the main database so i know how to make these changes without impacting code.

Thanks for your help and referencing the previous example since you provide me this code.

Patrick Matthews

To enforce a "no duplicate value" rule, I would enforce it in the database layer. Just put a unique index on that column. That way, you are protected against duplicate entries in all scenarios: forms, direct entry in the table, and code.

jsawicki

ASKER

Matt, Can you please expand on how to do that. If you want me to repost so you get points let me know.

jsawicki

ASKER

i am sorry pat, i read your name backwards....

als315

You can add unique index to any field (or combination of fields) in a table:

Open table in design mode and press Indexes button on ribbon. Set index type as unique and, if null values should be allowed, set Ignore Nulls to Yes.