Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old October 2nd, 2003, 07:29 AM
Registered User
Join Date: Oct 2003
Location: , Surrey, United Kingdom.
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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?

  #2 (permalink)  
Old October 2nd, 2003, 08:38 AM
sal sal is offline
Friend of Wrox
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts

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.

  #3 (permalink)  
Old October 2nd, 2003, 12:08 PM
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
  #4 (permalink)  
Old October 2nd, 2003, 08:06 PM
Registered User
Join Date: Oct 2003
Location: , Surrey, United Kingdom.
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts

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.


Similar Threads
Thread Thread Starter Forum Replies Last Post
Checking for Duplicates timeware ASP.NET 2.0 Professional 2 July 20th, 2006 08:41 AM
Yes to duplicates MMJiggy62 Beginning VB 6 3 July 14th, 2006 11:51 AM
Checking for duplicates toedipper2 SQL Server 2000 3 June 7th, 2006 03:50 PM
Duplicates in XSLT d_sathish XSLT 1 November 9th, 2005 11:45 AM
Duplicates ashley_y Access 11 August 14th, 2003 03:41 PM

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