Generate Sequential Number in Append

  • Thread starter Thread starter Bill Sturdevant
  • Start date Start date
B

Bill Sturdevant

(I inadvertently posted this question in the Forms
Programming Forum, so please excuse the double post)

I am using the SQL statement below to insert a bunch of
records into a table. I want to generate an Index Number
for each record that is sequential. I cannot use an
autonumber field.

Generate_Next_Sequential_Index_Num() is a routine that
accesses a public variable and bumps it by 1

The problem is, Generate_Next_Sequential_Index_Num() only
gets called ONCE in the query and every inserted record
then gets the value returned from that single call.

What am I doing wrong?

INSERT INTO Tbl_myTable ( Index_Number, Portfolio_Name )
SELECT DISTINCT Generate_Next_Sequential_Index_Num() AS
Expr1, Temp_Input_File.Portfolio_Name
FROM Temp_Input_File;
 
Dear Bill:

Just how to do this depends on many things.

From a practical standpoint, there are two basic but conflicting
ideas:

- have a number assigned sequentially that will never change. This
requires that the value be saved in the table.

- always have sequential numbers. If a row is deleted, then all the
subsequent rows must be reassigned. This is best done without saving
values in the table.

Which do you need, sequential or permanent?

If permanent, why is it that autonumber doesn't work for you?

To fix you use of the function, pass a parameter to it and put some
column into that parameter. This will fool Access into thinking it
must recalculate the value every time it is called, and then it will
assign a new value by calling the function every time. With no
parameter, Access thinks there's no reason to call the function over
and over since it will always return the same thing. This is a way of
optimizing performance that causes just the trouble you're
experiencing.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
What you would need to do is have one INSERT statement for each record you
are inserting. You should create a loop function so that you can loop
through the recordset of your SELECT statement. Insert it into your table
with each pass through the loop and calling up your function each time as
well.

HTH,
Bill
 
Tom,

Your suggestion about the parameter works!

To explain what I am doing: I already have a record Key
set up as an autonumber. But then I also have a RecID
field with a human-friendly number in it. The problem was
that when I used the query to batch load some initial
records, I had to force the RecID to be within a certain
range so the users would know by sight that those records
had been batch imported.
 
Dear Bill:

The trick is now to know WHY this fixes it, so you'll know what to do
when the situation arises again.

It fools Access into thinking the results of the function depend on
something that may change, so it has to run the function every time to
make sure.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top