Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 495
  • Last Modified:

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
0
EmpKent
Asked:
EmpKent
  • 8
  • 8
1 Solution
 
dannywarehamCommented:
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
 
EmpKentAuthor Commented:
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
 
dannywarehamCommented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
EmpKentAuthor Commented:
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
 
dannywarehamCommented:
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
 
EmpKentAuthor Commented:
Pretty much. I also want to ensure that the range equals the number received but let's start with populating the missing numbers.
0
 
dannywarehamCommented:
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
 
EmpKentAuthor Commented:
OK, how do I get this into the form and trigger it?

Sorry if this is a pretty basic question.
0
 
dannywarehamCommented:
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
 
EmpKentAuthor Commented:
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
 
dannywarehamCommented:
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
 
EmpKentAuthor Commented:
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
 
dannywarehamCommented:
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
 
EmpKentAuthor Commented:
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
 
EmpKentAuthor Commented:
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
 
dannywarehamCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 8
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now