Re: Append query with addtional fields

Tech-Archive recommends: Fix windows errors by optimizing your registry

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


"Opal" <tmwelton@xxxxxxxxxx> wrote in message
news:1193588665.430129.104900@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Oct 28, 8:21 am, "tina" <nos...@xxxxxxxxxxx> wrote:
what is the name of the table you're appending TO?

hth

"Opal" <tmwel...@xxxxxxxxxx> wrote in message

news:1193526629.880384.227390@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx



On Oct 27, 4:25 pm, "tina" <nos...@xxxxxxxxxxx> wrote:
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

"Opal" <tmwel...@xxxxxxxxxx> wrote in message

news:1193510174.939130.204230@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

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 -

- Show quoted text -

ConcernComparetbl



.

Relevant Pages

  • Re: Append query with addtional fields
    ... incremented value within the query records. ... Public lng As Long ... Public bln As Boolean ... there are certain risks in using public variables: if you run the Append ...
    (microsoft.public.access.queries)
  • Re: Append query with addtional fields
    ... incremented value within the query records. ... if by chance the lng variable resets, ... error in the Append. ... I can't quite figure out how to increment the numbers in the ...
    (microsoft.public.access.queries)
  • Re: Append query with addtional fields
    ... incremented value within the query records. ... if by chance the lng variable resets, ... error in the Append. ... I can't quite figure out how to increment the numbers in the ...
    (microsoft.public.access.queries)
  • Re: Append query with addtional fields
    ... run the query, stepping through the code to see how many times the function ... isIncrement = lng ... Option Explicit ...
    (microsoft.public.access.queries)
  • Re: Date Recurrence 2
    ... Doug, this is not a simple question, and there are lots of possible ... Create a query using both tables. ... Dim lng As Long ...
    (microsoft.public.access.queries)