Update a reference field incrementally

[AllyOop]

I have an order table that I will be adding data to regularly.
The order number is the primary key, but is a text field.
I would like to create a unique reference field that is a number.

I have two options. I can create the number as an autonumber when appending
the new data, or I can run an update query after appending the data.
The only thing is I need to be sure that the number created will be unique
and will increase incrementally from the previously added order by
100(without skipping).

My preference is to do the update query, but I am not too savvy with vb and
am pretty sure that code will be required to set this up.
Any help on this would be greatly appreciated.

 

[John Vinson]

I have an order table that I will be adding data to regularly.
The order number is the primary key, but is a text field.
I would like to create a unique reference field that is a number.

I have two options. I can create the number as an autonumber when appending
the new data, or I can run an update query after appending the data.
The only thing is I need to be sure that the number created will be unique
and will increase incrementally from the previously added order by
100(without skipping).

My preference is to do the update query, but I am not too savvy with vb and
am pretty sure that code will be required to set this up.
Any help on this would be greatly appreciated.

I would suggest "none of the above".

Instead, use a little bit of VBA code on the Form in which you're
entering orders. If you want to see the new reference number on the
form as you're adding the record, use the Form's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtRefNo = NZ(DLookUp("[RefNo]", "[tblOrders]")) + 1
End Sub

If you have multiple users you might have duplicates being added;
there are getarounds to prevent this problem. But another way is to
put this code in the Form's BeforeUpdate event so the new number is
assigned an instant before the record is saved; this has the
disadvantage that the user can't see the order number while entering
the order.

John W. Vinson[MVP]

 

[AllyOop]

Thanks for your prompt response!
The only problem is that I will not be adding orders one at a time through a
form. I will be appending orders daily, probably 1000 or more orders at one
time.

I have an order table that I will be adding data to regularly.
The order number is the primary key, but is a text field.
I would like to create a unique reference field that is a number.

I have two options. I can create the number as an autonumber when appending
the new data, or I can run an update query after appending the data.
The only thing is I need to be sure that the number created will be unique
and will increase incrementally from the previously added order by
100(without skipping).

My preference is to do the update query, but I am not too savvy with vb and
am pretty sure that code will be required to set this up.
Any help on this would be greatly appreciated.

I would suggest "none of the above".

Instead, use a little bit of VBA code on the Form in which you're
entering orders. If you want to see the new reference number on the
form as you're adding the record, use the Form's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtRefNo = NZ(DLookUp("[RefNo]", "[tblOrders]")) + 1
End Sub

If you have multiple users you might have duplicates being added;
there are getarounds to prevent this problem. But another way is to
put this code in the Form's BeforeUpdate event so the new number is
assigned an instant before the record is saved; this has the
disadvantage that the user can't see the order number while entering
the order.

John W. Vinson[MVP]

 

[John Vinson]

Thanks for your prompt response!
The only problem is that I will not be adding orders one at a time through a
form. I will be appending orders daily, probably 1000 or more orders at one
time.

Ah. Yes that's different <g>...

One handy technique here is to have a utility table, named Num, with
one Long Integer field N. Prefill it with values from 0 through the
most orders you'll ever want to generate - be generous, it's a small
table even with a hundred thousand rows.

Use this table to provide a list of sequential numbers; if you're
adding information from some existing table or a Form, you can use

[N] + <some starting number>

as a calculated field, and a criterion of

< [Enter number of orders to populate]

as a criterion on N.

John W. Vinson[MVP]

 

[AllyOop]

Ok, that sounds doable. Am a little confused how to set up the update query.
Here is what I have so far.
The Orders table contains Order number (text), order date, ship date, and
Reference (number)
The reference field is the blank one that I am assigning the reference
number to.

I have created a second table with the reference numbers, starting with
1026, 1126, 1226, etc and a second field for the date that they are used.

I need the query to be able to run automatically regardless of how many
order there are each day.

Thanks!

 

[John Vinson]

Ok, that sounds doable. Am a little confused how to set up the update query.
Here is what I have so far.
The Orders table contains Order number (text), order date, ship date, and
Reference (number)
The reference field is the blank one that I am assigning the reference
number to.

I have created a second table with the reference numbers, starting with
1026, 1126, 1226, etc and a second field for the date that they are used.

I need the query to be able to run automatically regardless of how many
order there are each day.

Thanks!

sorry... leaving on a week-long trip tomorrow morning.

Please repost as a new thread - with an explanation of these numbers.
The numbers you cite are not sequential, and I don't understand at all
what it is that you want to update (the term "update" means to change
values in existing records, and it seems that you instead want to
create thousands of new records... you might want to explain why!)

John W. Vinson[MVP]