Link to home
Start Free TrialLog in
Avatar of EmpKent
EmpKentFlag for Canada

asked on

Expand serial number ranges into a new table for duplicate validation

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
Avatar of dannywareham
dannywareham
Flag of United Kingdom of Great Britain and Northern Ireland image

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?
Avatar of EmpKent

ASKER

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.
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?
Avatar of EmpKent

ASKER

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
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
Avatar of EmpKent

ASKER

Pretty much. I also want to ensure that the range equals the number received but let's start with populating the missing numbers.
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

Avatar of EmpKent

ASKER

OK, how do I get this into the form and trigger it?

Sorry if this is a pretty basic question.
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
Avatar of EmpKent

ASKER

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

Avatar of EmpKent

ASKER

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

ASKER CERTIFIED SOLUTION
Avatar of dannywareham
dannywareham
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of EmpKent

ASKER

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

Avatar of EmpKent

ASKER

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.
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.
:-)