Insert, Update, Open to record LastModified

[Guest]

(My post of 30 minutes ago has not appeared on the list. I know it's a long
way from Australia to the US . . .).
I have form open, after data entered, I am running an Inset Into query to
create a copy of the current record and then I want to run an Update query to
update the newly created copy. Then I am opening a selected form (depending
on selected check box, which form opens) at the record last modified by the
Update query.

The problem is how to identify the newly created record to tell the Update
query which record to update.
The newly created copy needs a new ID (JobNo), which will automatically be
created for a new record. I won't know what this JobNo is to tell the Update
query to find it. The only other distinguishing field I have is the
LastModified field which has a time-date stamp.
Could someone please help with the syntax for telling the Update query which
record to update via the new JobNo or the LastModifide field. I have read up
on this but it is confusing, does the Update query automatically know to
update the newly created copy by itself? The syntax for the LastModified
using DMax is too complicated for me to make sense of, although I can use it
to open the new form to the record LastModified, but this same syntax won't
work in the query.

 

[rgayle]

try geting the max(id), of course if this is a multiuser, then you may
end up getting the wrong id.

 

[Guest]

Thanks for the suggestion. Could please help with the syntax for the max(id).
(This db is not a multi-user).

I have sSQL = "UPDATE tblRecords" & _
sSQL = sSQL & " SET Start = " & Me.txtActualStart & ",End = " &
Me.txtActualEnd & ", AssDate = " & Me.txtRepDate & ", Rating = 1, Updated =
False, LastModified = Now(), " & _
sSQL = sSQL & " WHEREtblRecords.JobNo = **

What do I put for ** is it just max(id) or what delimiters would it need?

If max(id) doesn't work, what other things might I try. Is there a different
way to do this?

 

[onedaywhen]

The problem is how to identify the newly created record to tell the Update
query which record to update.
The newly created copy needs a new ID (JobNo), which will automatically be
created for a new record.

Is it an autonumber (a.k.a. IDENTITY)?

http://support.microsoft.com/default.aspx?scid=kb;en-us;232144
INFO: Jet OLE DB Provider Version 4.0 Supports SELECT @@Identity
Jamie.

--