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")
"bcap" <(E-Mail Removed)> wrote in message
news:48898a05$0$2518$(E-Mail Removed)...
>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.
>
> "Scott" <(E-Mail Removed)> wrote in message
> news:95A48849-9C25-4AC6-BFEE-(E-Mail Removed)...
>> 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?
>
>
|
Similar Threads
