p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access (http://p2p.wrox.com/forumdisplay.php?f=18)
-   -   checking for duplicates (http://p2p.wrox.com/showthread.php?t=4529)

jammykam October 2nd, 2003 07:29 AM

checking for duplicates
I have a database of serial numbers which are added to on a regular basis. At the moment there is about 30,000 and growing (consisting of ID, Serial#, JobID). These numbers are scanned in using a barcode reader directly into the access through forms which are in table view. Problem is that stock is often recirculated back to us so we need to know when this has happened.

I thought of simply setting the field to allow no duplicates but sometimes the stock will be kept anyway and therefore duplicates will exist. Is there any way I could pop up a message box maybe to alert the user that this number already exists in the database and then let them make their own mind out (they are semi-compenent users if such a thing exists!).

Any thoughts?


sal October 2nd, 2003 08:38 AM

this may not be as simple as you would like it to be. Are you able to code at all?
You may need to use VB/VBA to accomplish this, and even then, you may have some issues with the scanner not working properly.


SerranoG October 2nd, 2003 12:08 PM

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

jammykam October 2nd, 2003 08:06 PM

Thanks for your replies. I can code in VBScript and have picked up a little VB/VBA in the past few weeks, but mainly stuff thats largely similar.

Greg, the bar code reader simple picks up the number as if you were typing it yourself and then goes to the next input box. So in essence it is just a string.

I'm gonna give this code a go. This was my initial feeling and looks like it should work. I was just hoping there was a smarter way of doing it. I was just a little worried about speed of running this routine because when these numbers are scanned in they are piled in batches. Sometimes there are 10,000 units per job (these are mobile phones for import/export by the way) and the databse will start getting very big after a while. Suppose could split it down further by having a different database for each different type of phone.

Anyone know how many records it takes to mess up the performance of Access (assuming following table structure) or des that depend on the speed, memory etc of the computer you are running it on.



Thanks for you help.


All times are GMT -4. The time now is 11:36 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.