How do I create and print serial numbers to use as labels.

[jbravo2]

Hi,
I created a tape library in Access 2003 where people will keep track of backup tapes. It would have been very easy for me to create serial numbers as the user enters each tape, but the requirements are that a serial number is printed first and put on tapes and then the information is entered in the database once the tapes are ready for storage.
The idea is that the user enters the number of labels it needs, and then the serial numbers are created and printed. I don’t know how to create serial numbers without having records already in place, but I have an idea. I created a form with a text box (txtLabels) where the user specifies the number of labels it needs. Then I created a table tblTapeSN with two fields, SequenceNo and chrDate. I’m thinking that I will need this table to keep track of the serial numbers, so every time a user enters a new request for labels I would be find what was the last serial number used and continue from there (I can probably use the Dmax function for that). Then I think I will need to create a loop with an inserts statement that inserts new records in a field, which in turns will create the serial numbers in the TableSN (the SequenceNo field in the tblTapeSN is set to 6 digit auto serial number). I was thinking about using the Date() function as the data to be inserted and store it in the chrDate field.
The problem I have is how to create the loop with the SQL statement and reference the txt box as number of records to be input. For the insert I think I may be able to use something like:
Sub Main()
Dim a, b as integer
a =0
e = number from the txtLabel
While a <= e
a - =1
e + =1
End while
Insert into tblTapeSN (chrDate)
Values (data() )
End Sub
There is also the issue of finding the last serial number used, and continue the serial numbers from there, and then printing the output of that specific group of s/n.
Any help will be greatly appreciated.

 

[SpentGeezer]

Do something like this:

dim Rd_Set
dim numSerial
dim numHowMany

if not isnull(txtLabels.value) then
    if isnumeric(txtLabels.value) then
        numHowmany = txtLabels.value
    else
        msgbox "Enter a number please",vbexclamtion,"Error"
        exit sub
    end if
else
    msgbox "Enter a number please",vbexclamtion,"Error"
    exit sub
end if



Set Rd_Set = currentdb.openrecordset("tblTapeSN")

numSerial =0
If not rd_set.recordcount > 0 then
    numSerial = 100000 ' start at 100000 makes it easier
else
    rd_set.movefirst
    Do Until rd_set.EOF
        if rd_set!SequenceNo > numserial then
            numserial = rd_set!sequenceNo
        end if
    loop
end if

numSerial = numserial + 1 (new serial number)
dim i
i=0
do until i= numHowMany
    rd_set.addnew
    rd_set!SequenceNo = numSerial
    rd_Set!chrDate = date
    numSerial = numserial + 1
    i=i+1
    rd_set.update
loop

 

[jbravo2]

Hi SpentGeezer, thanks for the fast reply.

I inserted your code in the 'On Click' event of a cmdEnter button, but nothing happened. I checked the underlaying table and is empty. I did not get any errors either so I'm not sure why is not working.

My setup is:
Table: tblTableSN
Fields: SequenceNo (autonumber, long integer)
chrDate (text) I had it as short date but I changed it. The changed did not affected the output of the code)

Form Name: frmLabels
txt.Labels (where I enter the amount of labels)
cmdEnter (to execute the code)

Do you know what could be wrong. I have been looking at your code and everything seems to be in order.

 

[jbravo2]

I noticed that in the first IF Statement, where it is checked that only numbers are entered, the second error message was not being displayed when I was leaving the text box in blank. Instead I was getting the generic MS Access message" "The value entered is invalid for this field...." That is because I had the text box format set to General Number. I deleted the format in order to get rid of the MS Access message, which worked, but now when I run the whole thing the database locks, like it is running an infinite loop.

Before the change of the format in the text box, the code ran without errors, and without any results. But now when I run the code, the db goes into a permanent busy status.

Any ideas?

 

[SpentGeezer]

Sorry mate, there should be a recordset.movenext in the Do until Recordset.EOF loop

     numSerial =0 If not rd_set.recordcount > 0 then     
     numSerial = 100000 ' start at 100000 makes it easier 
else     
    rd_set.movefirst     
    Do Until rd_set.EOF         
        if rd_set!SequenceNo > numserial then             
             numserial = rd_set!sequenceNo         
        end if                 
        rd_set.movenext '<-------------this bit
    loop 
end if

 

[jbravo2]

Hi SpentGeezer,

I added the recordset.movenext inside the Do Until Recordset.EOF loop. The tblTapeSN is getting populated now with serial numbers and today's date, which is perfect, but the count doesn't stops. It started at 100001 and was over 500000 before I had to stop it manually. Here is how the code looks like now.

Private Sub cmdEnter_Click()

Dim Rd_Set
Dim numSerial
Dim numHowMany

If Not IsNull(txtLabels.Value) Then
If IsNumeric(txtLabels.Value) Then
numHowMany = txtLabels.Value
Else
MsgBox "Enter a number please", vbexclamtion, "Error"
Exit Sub
End If
Else
MsgBox "Enter a number please", vbexclamtion, "Error"
Exit Sub
End If



Set Rd_Set = CurrentDb.OpenRecordset("tblTapeSN")

numSerial = 0
If Not Rd_Set.RecordCount > 0 Then
numSerial = 100000 ' start at 100000 makes it easier
Else

Rd_Set.MoveFirst
Do Until Rd_Set.EOF
If Rd_Set!SequenceNo > numSerial Then
numSerial = Rd_Set!SequenceNo
End If
Loop

Rd_Set.MoveNext
End If

numSerial = numSerial + 1 'new serial number
Dim i
i = 0
Do Until i = numHowMany
Rd_Set.AddNew
Rd_Set!SequenceNo = numSerial
Rd_Set!chrDate = Date
numSerial = numSerial + 1
i = i + 1

Rd_Set.Update
Loop

End Sub

 

[jbravo2]

It finally works!! I had to declare some of the variable as Long, because the Do Until loop was not seen the values as numbers. The DBA at work help with this.

Here is the code:


Private Sub cmdEnter_Click()

Dim Rd_Set
Dim numSerial As Long
Dim numHowMany As Long


If Not IsNull(txtLabels.Value) Then
If IsNumeric(txtLabels.Value) Then
numHowMany = CLng(txtLabels.Value) 'Converts the value to Long
Else
MsgBox "Enter a number please", vbexclamtion, "Error"
Exit Sub
End If
Else
MsgBox "Enter a number please", vbexclamtion, "Error"
Exit Sub
End If


Set Rd_Set = CurrentDb.OpenRecordset("tblTapeSN")

'To start at 100000 or after the last serial number used
numSerial = Nz(DMax("[SequenceNo]", "tblTapeSN") + 1, 100000)

Dim i As Long
i = 0
Do Until i = numHowMany
Rd_Set.AddNew
Rd_Set!SequenceNo = numSerial
Rd_Set!chrDate = Date
numSerial = numSerial + 1
i = i + 1
Rd_Set.Update
Loop

End Sub

 

[SpentGeezer]

What is this NZ?

Nz(DMax("[SequenceNo]", "tblTapeSN") + 1, 100000)

Nice!

 

[jbravo2]

The Nz function is very useful for returning blank space, zero, or any number when the value is Null, which I needed here to specify the starting serial number if there was none.

The syntax is Nz (variant, [valueifnull])

Thanks for your input. I had no idea on how to put this together, specially the part with the Do Loop, and you did. Thanks again.