Solved

Expand serial number ranges into a new table for duplicate validation

Posted on 2008-10-11
16
478 Views
Last Modified: 2011-09-20
Hello,

I am trying to build an Access 2003 database to be able to scan serial number ranges in and have Access expand those into a new table with each serial in the range. The new table has the serial as the primary key, which should prevent against duplicates.

I would also like to have the initial input validate that the quantity received is equal to the number of serials in the range.

I have not used Access in several years but I have got the tables and form built. I just need to know how to automatically do the validation. Perhaps also something to alert the user to a duplicate serial.

Thanks,

Kent
0
Comment
Question by:EmpKent
  • 8
  • 8
16 Comments
 
LVL 26

Expert Comment

by:dannywareham
ID: 22695089
I'm not sure what you mean.
Are you importing these serial numbers in bulk from Excel (for example)?
And you want to validate what?
0
 
LVL 7

Author Comment

by:EmpKent
ID: 22695139
I have products coming in with serial ranges barcoded on the case. I want to be able to scan the Serial From number and the Serial To number into my form as well as the reported quantity in the case. I have that much done already.

Then I want Access to take the range, calculate how many serials are in it and verify that it matches the quantity received. Following that, I want it to populate another table with each serial in that range to ensure that there are no duplicates from the manufacturer.

I may output the list of serials at some point but this is all I need right now.
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 22695148
OK. So what makes a "range"?
Is it that your serial is (for example):   xxx-xxxx-yyyyy
And you want to see how many are in teh first xxx-xxxx bit?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Author Comment

by:EmpKent
ID: 22695161
Nope, I will get a box with 24 units in it and the label will say: (with barcodes as well)

Serial From: 10001
Serial To:     10024

I want Access to verify that there are in fact 24 serials in that range and then to populate another table with:

10001
10002
10003
10004
10005
10006
10007
10008
10009
10010
10011
10012
10013
10014
10015
10016
10017
10018
10019
10020
10021
10022
10023
10024
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 22695168
OK. So you scan in the two barcode and you want Access to populate all the missing numbers?

If that's right, it can be done quite simply.
;-)
Let me know and I'll show
0
 
LVL 7

Author Comment

by:EmpKent
ID: 22695179
Pretty much. I also want to ensure that the range equals the number received but let's start with populating the missing numbers.
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 22695193
OK. I'm gonna assume that you will store the start and end numbers in two form fields.

You will need to change the names of your fields, table and column names:


Dim myStart as Long
Dim myEnd as Long
Dim mySQL as String
Dim i as Long
 
myStart = nz(me.txtStartfieldonform,0)
myEnd = nz(me.txtEndfieldonform,0)
 
If myStart = 0 or myEnd = 0 then
  msgbox "You must have a start and end value",vbokonly, "Error"
  Exit sub
End if
 
For i = myStart to myEnd
  mySQL = "INSERT INTO tblNameofTable (nameoffield) SELECT " & i & ";"
  currentdb.execute mySQL
Next i

Open in new window

0
 
LVL 7

Author Comment

by:EmpKent
ID: 22695218
OK, how do I get this into the form and trigger it?

Sorry if this is a pretty basic question.
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 22695224
Not a problem.
On your form, create a command button.
Go into its properties and selectthe On_Click event.
This will open your code window for that event.

paste it in and change the form controlsnames etc
0
 
LVL 7

Author Comment

by:EmpKent
ID: 22695366
Cool. I got that working. however, when I enter a range with a duplicate serial, it just overwrites the previous (or does not write because it is already there) but I would like some form of error so the user knows that this should go to corrective action. Basically, that would mean that the manufacturer had already sent us an item with that serial.

Also, is it possible to get it to verify that the number of serials equals the quantity received?

Thanks,

Kent
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 22696697
You will have to lookup up the values before you run the INSERT query to see if there's a serial already there.

Also, for quantity, I'm not sure what you mean..

(Untested)
Dim myStart as Long
Dim myEnd as Long
Dim mySQL as String
Dim i as Long
 
myStart = nz(me.txtStartfieldonform,0)
myEnd = nz(me.txtEndfieldonform,0)
 
If myStart = 0 or myEnd = 0 then
  msgbox "You must have a start and end value",vbokonly, "Error"
  Exit sub
End if
 
For i = myStart to myEnd
  if nz(dlookup("nameoffield","tblNameofTable","nameoffield =" & i),0) = i then
 'there's aready a value
Else
'not a value - run query
  mySQL = "INSERT INTO tblNameofTable (nameoffield) SELECT " & i & ";"
  currentdb.execute mySQL
End if
Next i

Open in new window

0
 
LVL 7

Author Comment

by:EmpKent
ID: 22722261
Danny,

Just two more questions and I will award points. Sorry to drag this on. I include current code in the snippet. The commented out portion always returned a MsgBox but I will figure that out.

1. Serial lookup for duplicates is not working although I am no longer getting errors (after much googling). I enter duplicate serials with different PalletIDs and ones that already exist are left alone but the others get written. It is not even overwriting the duplicates, it just ignores new input. Have I done something to mess up your suggested code?

2. The form that I am entering the data into continues to display the entered data and does not commit it to the table unless I move to the next record. I would like to be able to have this advance automatically and display blank feilds for the next pallet. This would be an addendum to the Submit_Enter or click subroutine, I would imagine.

Thanks again for your patience.

Kent

Private Sub Submit_Enter()
 
Dim myStart As Long
Dim myEnd As Long
Dim Pallet_ID As String
Dim mySQL As String
Dim i As Long
 
myStart = Nz(Me.Serial_From, 0)
myEnd = Nz(Me.Serial_To, 0)
Pallet_ID = Nz(Me.Pallet_ID, 0)
 
If myStart = 0 Or myEnd = 0 Then
  MsgBox "You must have a start and end value", vbOKOnly, "Error"
Exit Sub
End If
 
'If Pallet_ID = 0 Then
'  MsgBox "You must enter a pallet LP", vbOKOnly, "Error"
'Exit Sub
'End If
 
For i = myStart To myEnd
 If Nz(DLookup("Serial", "Serial", "Serial=" & i), 0) = i Then 'There is aready a serial
 Else
 'not a value - run query
 mySQL = "INSERT INTO Serial (Serial, PalletID) SELECT " & "" & i & " , '" & Pallet_ID & "';"
 CurrentDb.Execute mySQL
End If
Next i
 
End Sub

Open in new window

0
 
LVL 26

Accepted Solution

by:
dannywareham earned 500 total points
ID: 22722475
OK. 3 things:

1. Your Pallet_ID always = 0 - so this must be because either a) your form control is empty or b) your form control has the same name as the source. Change the control to txtPalet_ID and try again
Notice howmy code variables all start with "my". That's to ensure that your variable, control and controlsources are all differently named.

2. The code doesn't tell the database to update an existing ID. We'll need to write a query to do that (see code below)

3. Your form won't refresh unless you tell it to. Add Me.Form.Requery   and   Me.Form.Refresh   to the end of your code.


'Code change:
'This assumes that you want to change teh pallet id to the new one.
 
For i = myStart To myEnd
 If Nz(DLookup("Serial", "Serial", "Serial=" & i), 0) = i Then 'There is aready a serial
'we need to update existing
mySQL = "UPDATE Serial SET PalletID =" & PalletID & " WHERE Serial=" & i
Currentdb.Execute mySQL
 
 Else
 'not a value - run query
 mySQL = "INSERT INTO Serial (Serial, PalletID) SELECT " & "" & i & " , '" & Pallet_ID & "';"
 CurrentDb.Execute mySQL
End If
Next i
 
 
'Are you Ok changing field names etc?

Open in new window

0
 
LVL 7

Author Comment

by:EmpKent
ID: 22725007
Here is my final code.  I still could not get the pallet error working properly and when I Enter through after the Serial_To (basically through the command button) it pops up a new record but also gives me the "You must have a start and end value" message before I have tried to enter anything.

I am guessing that I have the form processing too early and will try to fix that.

Thank you very much for your quick responses and excellent solutions. I am sure I will be back with further questions.

Kent

Private Sub Submit_Enter()
 
Dim myStart As Long
Dim myEnd As Long
Dim myPallet_ID As String
Dim mySQL As String
Dim i As Long
 
myStart = Nz(Me.Serial_From, 0)
myEnd = Nz(Me.Serial_To, 0)
myPallet_ID = Nz(Me.Pallet_ID, 0)
 
If myStart = 0 Or myEnd = 0 Then
  MsgBox "You must have a start and end value", vbOKOnly, "Error"
Exit Sub
End If
 
'If myPallet_ID = 0 Then
'  MsgBox "You must enter a pallet LP", vbOKOnly, "Error"
'Exit Sub
'End If
 
For i = myStart To myEnd
If Nz(DLookup("Serial", "Serial", "Serial=" & i), 0) = i Then
  MsgBox "There is aready a serial", vbOKOnly, "Error"
Exit Sub
 
Else
 'not a value - run query
 mySQL = "INSERT INTO Serial (Serial, PalletID) SELECT " & "" & i & " , '" & myPallet_ID & "';"
 CurrentDb.Execute mySQL
 End If
Next i
DoCmd.RunCommand acCmdSaveRecord
DoCmd.GoToRecord , , acNewRec
End Sub

Open in new window

0
 
LVL 7

Author Closing Comment

by:EmpKent
ID: 31505345
Good job. Thanks for being patient with me. I do not generally get tasked with this sort of solution building but it was an emergency. I now have a functional tool and will continue improving it over the coming days.
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 22726615
As I said before, your variable in the code is called "Pallet_ID"
Your control is clearly referenced as "Pallet_ID"
I'm guessing that the field in the table is called "Pallet_ID" too.

You need to change this so they're ALL different.
Call the field in the table "Pallet_ID"
Call the form control/textbox "txtPallet_ID"
Call the variable "myPallet_ID"

Also, if you put "STOP" above your IF statement, you can use F8 to step through teh code.
Hover your mouse over "Me.Pallet_ID" (or whatever it's renamed to) and you'll see the value being passed.

Good luck - you know where I am if you need me.
:-)
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question