Need Autonumber on new record 1

[dolfin13]

I have a form that creates new records in which the ID needs to be inserted into another table. This form was working fine until we started using replication. I took the easy way out and used the Max() function to get the newest entry. My question is, is there a quick way to get this Autonumber that gets created from my INSERT INTO statement? Or is my best answer to change up this routine to use .AddNew so that I can use the .LastModified property?

Thanks in advance for the help!

Kevin



CODE SNIPPET:

With rs
If Not rs.EOF Then
rs.MoveFirst
Do While Not rs.EOF
OwnId = rs("OwnerNew")
CompId = rs("CompanyAbbr")
db.Execute ("Insert INTO CSP_T_TeamActivityLog (ActivitySchedType, ActivityStatus, OwnerNew, Begin, [End-Due Date], Location) VALUES ('" & cboMeeting & "', '" & cboTargeted & "', '" & OwnId & "', #" & txtDate & "#, #" & txtDate & "#, '" & CompId & "')")
OpId = rs("ID")
strSQL2 = "SELECT Max(ID) AS ActAuto FROM CSP_T_TeamActivityLog;"
Set rs2 = db.OpenRecordset(strSQL2)
maxnum = rs2("ActAuto")
db.Execute ("Insert INTO CSP_T_TaskDetail (ActivityID, OpportunityNew) VALUES ('" & maxnum & "', '" & OpId & "')")
rs2.Close
rs.MoveNext
Loop
End If
End With

 

[PWise]

add this to your code

[COLOR=red]dim rstident as recordset[/color]

With rs
    If Not rs.EOF Then
        rs.MoveFirst
        Do While Not rs.EOF
            OwnId = rs("OwnerNew")
            CompId = rs("CompanyAbbr")
            db.Execute ("Insert INTO CSP_T_TeamActivityLog (ActivitySchedType, ActivityStatus, OwnerNew, Begin, [End-Due Date], Location) VALUES ('" & cboMeeting & "', '" & cboTargeted & "', '" & OwnId & "', #" & txtDate & "#, #" & txtDate & "#, '" & CompId & "')")
            OpId = rs("ID")[COLOR=red]
            Set rst = CurrentDb.OpenRecordset("Select @@IDENTITY as ident")
            maxnum  = rstident!ident[/color]
             db.Execute ("Insert INTO CSP_T_TaskDetail (ActivityID, OpportunityNew) VALUES ('" & maxnum & "', '" & OpId & "')")
            rs2.Close
        rs.MoveNext
        Loop
    End If
End With

Debug.Print rst(0)

 

[dolfin13]

Thanks so much for putting me on the right track!

Just had to revise a little sintax:

            Set rstident = db.OpenRecordset("Select @@IDENTITY as ident")
            maxnum = rstident("ident")

Thanks again!!!

 

[PWise]

this an be shortened to (less overhead)

With rs
If Not rs.EOF Then
rs.MoveFirst
Do While Not rs.EOF
OwnId = rs("OwnerNew")
CompId = rs("CompanyAbbr")
db.Execute ("Insert INTO CSP_T_TeamActivityLog (ActivitySchedType, ActivityStatus, OwnerNew, Begin, [End-Due Date], Location) VALUES ('" & cboMeeting & "', '" & cboTargeted & "', '" & OwnId & "', #" & txtDate & "#, #" & txtDate & "#, '" & CompId & "')")

opid = rs("ID")
db.Execute (" Insert INTO CSP_T_TaskDetail (ActivityID, OpportunityNew) Select @@IDENTITY as ActivityID " & ", '" & opid & "'")
rs2.Close
rs.MoveNext
Loop
End If
End With

 

[dolfin13]

Thanks a lot!

I'll use that, I've got a few more forms to fix in the same manner.