We help IT Professionals succeed at work.

VB Coding issues for data validation in  Access form

jsawicki
jsawicki asked
on
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
Comment
Watch Question

CERTIFIED EXPERT
Commented:
If sample from your previous question is valid, I see only one problem: field TLETS_SIM has space in name: TLETS _SIM. Try to remove it.

Author

Commented:
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.
CERTIFIED EXPERT
Top Expert 2010

Commented:
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.

Author

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

Author

Commented:
i am sorry pat, i read your name backwards....
CERTIFIED EXPERT

Commented:
You can add unique index to any field (or combination of fields) in a table:
indexOpen 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.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.