Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Closed Thread
 
Thread Tools Search this Thread Display Modes
  #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?

Thanx
Kam
  #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
Default

Kam
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.



Sal
  #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
Default

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.
Code:
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.
   Else
      'Don't add it to the table.  Do something else.
   End If
Else
   '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
Default

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.

tbl_Serial
-----------
Serial_ID
Serial_No
Job_ID

tbl_Jobs
-----------
Job_ID
Job_Ref#
Customer_Name
Date


Thanks for you help.

Kam
Closed Thread


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 01:32 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.