• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 623
  • Last Modified:

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
0
jsawicki
Asked:
jsawicki
  • 3
  • 2
1 Solution
 
als315Commented:
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.
0
 
jsawickiAuthor 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.
0
 
Patrick MatthewsCommented:
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.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
jsawickiAuthor Commented:
Matt, Can you please expand on how to do that.  If you want me to repost so you get points let me know.
0
 
jsawickiAuthor Commented:
i am sorry pat, i read your name backwards....
0
 
als315Commented:
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.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now