We help IT Professionals succeed at work.
Get Started

VB Coding issues for data validation in  Access form

jsawicki
jsawicki asked
on
656 Views
Last Modified: 2012-08-10
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:
This problem has been solved!
Unlock 1 Answer and 6 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE