AutoNumber Problem

[isv_2004]

I need help! (Sorry for being long winded)

I have a Form “Shipping” with an AutoNumber TextBox [ICS_NO] that needs to be incremented by 1 on function NewRecord and cannot waste any numbers.
NOTE: (I am responsible for a block of 50 numbers assigned to me by Management of which I must utilize EVERY number, and cannot skip any numbers).

If the user decides to abort/cancel the new record, I have the Me.Undo command handle it in Form_BeforeUpdate; but the TextBox [ICS_NO] continues with the next number.

So I need to ReSeed the [ICS_NO] with the Query “Reseed_ICS”

SQL: for “Reseed_ICS” Query
ALTER TABLE Shipping ALTER COLUMN ICS_NO COUNTER(605914,1);

OK, now I need the above statement to set the COUNTER to the LAST number that TextBox [ICS_NO] has in From “Shipping”

How can I insert the Last [ICS_NO] value from my Form “Shipping” into the SQL statement of Query “Reseed_ICS” ??

As you can see, I am using the AutoNumber field for a specific purpose, I have been told not to do this due to MS Access problems handling the AutoNumber control.

If anyone knows of a better way to achieve my objective, please offer advice.

Thanks in advance

 

[mohammadagul]

Hi,

Instead of using the Auto Number put the following code in the Default value of your textbox properties. Also make the textbox field type to number in your table.

=DMax("[ManualInvoiceNo]","tblInvoices")+1

Manualinvoiceno is the name of your field yoiu want to increment
tblinvoices is the name of your table wehre this field resides.

now try to use the SQL Statement

I am sure thsi will help you.

 

[isv_2004]

Still need help!

mohammadagul,

Thanks for the help, but I still would like to know how to place a variable in the SQL statement from the VB code?

in BeforeUpdate
I need the value from =DMax("[ICS_NO ]","Shipping") assign it to a Variable such as intICS, and place in the SQL statement: Then run the SQL statement if they cancel the "NewRecord"

GOOD
SQL: for “Reseed_ICS” Query
ALTER TABLE Shipping ALTER COLUMN ICS_NO COUNTER(intICS,1);

Thanks again.