Expand serial number ranges into a new table for duplicate validation

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?

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

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
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:

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

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

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:

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
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:

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
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:

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.