i was afraid of that. my understanding of action queries is that the action
takes place in a transaction, where all record changes are committed at the
same time. so even though the custom function is called for each append
record, the highest value in the table does not actually change until all
the records in the query have been processed, so you're not going to get an
incremented value within the query records.
let's add a couple of global variables to the mix and see if that works.
Public lng As Long
Public bln As Boolean
Public Function isIncrement(ByVal var As Variant) As Long
If bln = False Then
lng = Nz(DMax("TxtIDNumber", _
"ConcernComparetbl"), 0)
bln = True
End If
lng = lng + 1
isIncrement = lng
End Function
add the public variables to the public module, directly below the statements
Option Compare Database
Option Explicit
there are certain risks in using public variables: if you run the Append
query more than once during the same session, and if any unhandled errors
have occurred in other code between one query run and the next, the
variables may be reset. if by chance the lng variable resets, but not the
bln variable, the increment will be incorrect and will probably cause an
error in the Append.
another approach would be to add a table with a single field and one record,
to store the highest number in the TxtIDNumber field of table
ConcernComparetbl, as
tblHighNum
Num (Number, Long Integer)
< add one record to the table, with a value of 0 (zero)>
then you could dispense with the public variables, and use the following
custom function, as
Public Function isIncrement(ByVal var As Variant) As Long
Dim lng as Long
lng = DLookup("Num", "tblHighNum") + 1
CurrentDb.Execute "UPDATE tblHighNum SET Num = " _
& lng
isIncrement = lng
End Function
note that if you're also adding records to table ConcernComparetbl *outside
of the Append query*, then you'll need to update the Num value in tblHighNum
before running the query.
hth
what is the name of the table you're appending TO?
hth
suggest you use a custom function, as
Expr1: isIncrement([MyField])
and put the following function in a standard module, as
Public Function isIncrement(ByVal var As Variant) As Long
isIncrement = DMax("TxtIDNumber", _
"ConcernComparetbl") + 1
End Function
it doesn't matter which field you use in the function argument, as long
as
it's a field in the table/query you're appending FROM. the actual field
value is not used in the function; but referring to a field forces the
function to be called for every record that is appended - rather than
once
for the whole append action, which is what was happening in your
query.
hth
I am trying to create an append query with two additional fields. The
first is a date field. That I do not have an issue with. The second
field I want to be an autonumber type field. I have
input the following expression into the query:
Expr1: DMax("[TxtIDNumber]+1","ConcernComparetbl")
The "ConcernComparetbl" is where the append query is adding the
records.
However, when I run the query, the number in the Expr1 is the same for
all records.
I can't quite figure out how to increment the numbers in the field.
Can anyone
assist?- Hide quoted text -
- Show quoted text -
I changed the module to:
isIncrement = Nz(DMax("TxtIDNumber", "ConcernComparetbl"), 0) + 1
and that got rid of the Invalid use of Null error, however, the number
for each record does not increment. I have
two records in the query and they are both numbered 1.- Hide quoted
text -
ConcernComparetbl- Hide quoted text -
- Show quoted text -
