Solved

Expand serial number ranges into a new table for duplicate validation

Posted on 2008-10-11
16
465 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now