Apply Sequence Number in Append Query

[Scott]

Greetings most excellent programmers. First post but love the tons of
messages that have helped so far. So thanks in advance!!!

I have a table with an ID (MyID) which is a sequential number as primary
key. I generate the number from a form with the followoing expression in the
"default" property MyID on the form set to =DMax("MyID","MyIDTable")+1. This
works great when creating new records from the form. I now need to in some
cases load records via Append query ( i have an import from flat file into
temp table, then do append query to move to primary table). When I try to
use this same method in append query to set MyID (via expression on query
field) , it will only insert the first record from append source and then
errors on unique record violation. It's as if the first record gets there
but the DMax("MyID","MyIDTable")+1 can't see the new record and can't
increment MyID on each append row insert?

Any ideas?

 

[bcap]

A query is logically an atomic set operation, not a procedural operation,
thus the expression to return the new ID only gets performed once for the
whole operation, hence the duplicate key.

You could try this:

First create a function in a module, such as this:

Function NextId(ByRef Junk As Variant) As Integer

Static intLastId As Integer

If intLastId = 0 Then
intLastId = DMax("ID", "Table3")
End If

intLastId = intLastId + 1
NextId = intLastId

End Function

In your query, append this to MyID:

NextId([some_field])

Where some_field is a field in your temporary table. It doesn't matter what
field.

 

[bcap]

Sorry, I should have said that "ID" and "Table3" were just the stuff that I
created to play with this. You should obviously replace with your own:

DMax("MyID","MyIDTable")

A query is logically an atomic set operation, not a procedural operation,
thus the expression to return the new ID only gets performed once for the
whole operation, hence the duplicate key.

You could try this:

First create a function in a module, such as this:

Function NextId(ByRef Junk As Variant) As Integer

Static intLastId As Integer

If intLastId = 0 Then
intLastId = DMax("ID", "Table3")
End If

intLastId = intLastId + 1
NextId = intLastId

End Function

In your query, append this to MyID:

NextId([some_field])

Where some_field is a field in your temporary table. It doesn't matter
what field.

Greetings most excellent programmers. First post but love the tons of
messages that have helped so far. So thanks in advance!!!

I have a table with an ID (MyID) which is a sequential number as primary
key. I generate the number from a form with the followoing expression in
the
"default" property MyID on the form set to =DMax("MyID","MyIDTable")+1.
This
works great when creating new records from the form. I now need to in
some
cases load records via Append query ( i have an import from flat file
into
temp table, then do append query to move to primary table). When I try
to
use this same method in append query to set MyID (via expression on
query
field) , it will only insert the first record from append source and then
errors on unique record violation. It's as if the first record gets
there
but the DMax("MyID","MyIDTable")+1 can't see the new record and can't
increment MyID on each append row insert?

Any ideas?