create sequencing numbers in a field

[Guest]

I have an inventory database. We are assigned blocks of numbers from the
university to use as IDs on assets we purchase. They will assign us 100
numbers (approx) at a time that will be a sequential block, but when we run
out of available numbers and request more, we could be thousands of numbers
down the road from the last one we used.

What I would like is for a user to open a form and enter the first number in
the block and the last number in the block given to us by the university
(let's say 28001 through 29000). I would then like to create a command
button that would add those numbers that are in the range entered by the user
to a table (let's say tblNumbers).

Can that be done and if so, how would I do that?

Thanks in advance for the help.

 

[John Vinson]

I have an inventory database. We are assigned blocks of numbers from the
university to use as IDs on assets we purchase. They will assign us 100
numbers (approx) at a time that will be a sequential block, but when we run
out of available numbers and request more, we could be thousands of numbers
down the road from the last one we used.

What I would like is for a user to open a form and enter the first number in
the block and the last number in the block given to us by the university
(let's say 28001 through 29000). I would then like to create a command
button that would add those numbers that are in the range entered by the user
to a table (let's say tblNumbers).

Can that be done and if so, how would I do that?

Easily. Create a table tblNumbers, with one Long Integer field N, and
fill it with all numbers from 0 through the largest number you'll ever
need - be generous, even a million-row table is quite managable. Put a
unique Index on the field.

A Query with criteria

= [Forms]![YourForm]![txtStart] AND <= [Forms]![YourForm]![txtEnd]

will give you all the numbers in that block. You can then base a Combo
Box on this query to allow the user to select one from the block (sort
the query ascending so they assign the numbers in order).

If you're storing the selected numbers in another table, use a
"Unmatched Query Wizard" query to give you only those numbers in the
block which do NOT exist in the target table.

John W. Vinson[MVP]

 

[Allan Murphy]

Donna

Possible solution, you will have to make changes to suit your needs.

Create a table called tbl_asset_numbers

Create a form with two unbound text boxes, for this exercise call them
first_number and last_number.

Add a command button and call it generate_numbers

On the Click event of this button add this code

This code will generate a record entry in the tbl_asset_numbers for the
range entered on the form.

Private Sub generate_numbers_Click()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim asset_counter As Long

Set db = CurrentDb()
Set rst = db.OpenRecordset("tbl_asset_numbers")

DoCmd.RunSQL "DELETE tbl_asset_numbers.* FROM tbl_asset_numbers"

Set rst = db.OpenRecordset("tbl_asset_numbers")

With rst
For asset_counter = Me!first_number To Me!last_number

rst.AddNew

rst("asset_number") = asset_counter

rst.Update

Next asset_counter

End With

End Sub

Note: I have not checked that the last number is after the first number etc.

 

[Guest]

Thanks Allan,

I did what you suggested but I'm getting the following

Compile Error user defined type not defined & the db as DAO.Database is what
is highlighted. Any suggestions?

 

[Allan Murphy]

Donna

Open your code and check the references by
1. Selecting Tools then References

2. Check that the following are ticked.
Microsoft DAO 3.6 Object Library
Microsoft Visual Basic for Applications Extensibility 5.3