Serial Number (As in Excel)

[Phudsen]

Hi,

In our library we create barcodes for books. I have created a nice application for typing the barcode and printing it. Recently we needed to add 1000 numbers to the database to barcode new books. Typing 1000 numbers which are actually a series takes time. So, I created the series in Excel then imported them to Access as a table.

I have the following:
Table Name: BarCodes
Fields:
BarCodeNumber (no duplicates)
LibraryName (the name is the default value)

I want to create a form which has two fields.
Field one: Starting From
Fiel two: Add

So we can put in the starting from: 10005590
And in the field Add we put: 999

Then press OK. When pressing OK the form adds 1000 records to the table. The records will stop at 10006589. Next time we want to add barcodes, we put in the starting 10006589 and type 100 in the add field if we want to add 100 numbers only.

Thanks a lot
Paulin

 

[Phudsen]

Hi,
What I tried in the meantime is:

1- I created a table of serial number
2- I created a spreadsheet in excel added 50000 which is much more than our collection
3- I imported the file into Access
4- Named the file as BarCode Store
5- I created a new table and Called it Barcodes
6- I created a form with three fields
Field one: txtFrom
Field two: txtAdd
Field three: txtTo
In txtFrom I put the last number we created
In txtAdd I put the number barcodes I need (say 150)
In txtTo I put: =[txtFrom]+[txtAdd]
7- I created an insert query in which I put a between codition. The condition reads txtFrom and txtTo. Then it picks up the numbers from the store then Adds them to the table.

It is working perfectly

I just wanted to know if it is easy in VBA?

Thanks

 

[willir]

Phudsen / Paulin

As I understand it, you want to create sequential records in your barcode table whenever you have more books to tag.

Do you want the end user to determine the starting position? For example, your highest bar code numer is 1234567, but you want to start at 1234600 and not 1234568.

Okay, using your initial idea, if the end user sets the initial value then you do need two fields on the form, otherwise the only value the end user needs to enter is the number of records to add and let Access determine the next serial number to start from. I am going to assume you want Access to determine the first number in the series.

So you have an unbound form.

txtStart
Purpose of text box on form: Current maximum number of bar codes in the "system"
ControlSource: =DMax("BarCodeNumber", "BarCodes")

txtIncrement
Purpose of text box on form: Input field for the number of records to increment

cmdAction
Purpose of command button on form: User clicks the button to add the records.

The following code should work if my assumptions are correct if you add the following code to the OnClick event for the command button.

If Me.txtIncrement Then

    Set rst = CurrentDb.OpenRecordset("BarCodes")
    
    With rst
    
        'set start and end positions
        'assume there is at least one valid record
        intStart = Nz(Me.txtStart, 0) + 1
        intEnd = intStart + Me.txtIncrement - 1
        
        'set Library name - assuming valid name on 1st record
        .MoveFirst
        strLibrary = !LibraryName
    
        For intX = intStart To intEnd
            .AddNew
                !BarCodeNumber = intX
                !LibraryName = strLibrary
            .Update
        Next intX
        
    End With
    
    rst.Close
    Set rst = Nothing
    
    Me.txtIncrement = 0
    Me.txtStart.Requery
    
End If

Note that I reset the increment text control to 0 and requery the next start position afterwards. If the end user double clicks on the command button, nothing will happen on the second click because the increment value will be use, and the first test in the above code is to ensure the code only executes if the value is greater than 0.

Richard