View Single Post
  #3 (permalink)  
Old October 2nd, 2003, 12:08 PM
SerranoG SerranoG is offline
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG

Not knowing the dynamics of how your bar coder works nor how the values get read by your Access database makes it hard to comment. I'm going to ignore how that number gets into your database and just concentrate on what to do after it's read by your Access database.

Suppose your routine reads the serial number, e.g. a string called strSerialNo (remember, unless you do math with something all IDs, even 100% numbered ones, are strings). On the AfterUpdate event of whatever reads your serial number you can simply count to see if that number already exists in the table.
Dim strSerialNo as String, lngSerialCnt as Long, varAnswer as Variant

'The following function somehow reads serial number from bar coder.
strSerialNo = strSomeFunction()
lngSerialCnt = DCount("[strSerialNo]", "tblMyTable", _
                      "[strSerialNo] = '" & strSerialNo & "'"

If lngSerialCnt > 0 Then
   'Serial number already is in the table.  Ask if OK to add this.
   varAnswer = MsgBox "This stock is already in the table.  " & _
               "Include anyway?", vbQuestion + vbYesNo, "Duplicate!"
   If varAnswer = vbYes Then
      'Do stuff here, e.g. add it to the table.
      'Don't add it to the table.  Do something else.
   End If
   'Serial Number is new.  Add to table automatically.
   'Do stuff here, e.g. add data to the table.
End If

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division