Link to home
Start Free TrialLog in
Avatar of Megin
Megin

asked on

How do I use bookmark.lastmodified?

I have a nice piece of code that I have gotten straightened out by people here, but now I need to mess it up some.
I have to take the primary key from the record that the code just added to a table and then add that to another table with information from a third field in the form.

I believe that I need to use "Bookmark.LastModified" but I am not sure how it works.

Currently the code looks like this:

Private Sub btn_AddtoTeam_Click()

 Dim rst As DAO.Recordset
 Dim strsql As String
 Dim TNAME As String

 TNAME = Forms!frm_Staff.Form!FName & " " & Forms!frm_Staff.Form!Lname
 strsql = "Select Top 1 * from Team"

    Set rst = CurrentDb.OpenRecordset(strsql)
    With rst
        .AddNew
        ![TeamName] = TNAME
        .Update
        .Close
     End With  'rst

 Set rst = Nothing

 End Sub

Open in new window


I need to take the ID number (TeamID) created by this code and add it to a second table (TeamStaff) with the staff ID on the form (StaffID).

I know that I want to bookmark the last record that was created and then pull that ID number into the next table, but I don't know how to work that into the code.

Thank you for this and for all of the help preceding this!
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

After the

.update

Line, insert:

.bookmark = .lastmodified

Then, you can use something like:

LngID = .ID
.close
OR ...
    Dim lID As Long
    With rst
        .AddNew
        ![TeamName] = TNAME
        .Update
        .Move 0,  .LastModified
        lID = ![YourPrimaryKeyFieldNameHere]
        .Close
     End With  'rst
Joe,

Never tried the .move method here.  Any advantage over the

.bookmark = .lastmodified

method, that you know of.  Or is it just personal preference?

Dale
I don't know actually ... never used the Bookmark for this.

btw .... re

LngID = .ID

are you sure it shouldn't be

LngID = !ID
Good catch
Assuming the BE is Jet or ACE, you can simply reference the ID field.
Private Sub btn_AddtoTeam_Click()

 Dim rst As DAO.Recordset
 Dim strsql As String
 Dim TNAME As String
 Dim NewTeamID as Long

 TNAME = Forms!frm_Staff.Form!FName & " " & Forms!frm_Staff.Form!Lname
 strsql = "Select Top 1 * from Team"

    Set rst = CurrentDb.OpenRecordset(strsql)
    With rst
        .AddNew
        ![TeamName] = TNAME
        .Update
        NewTeamID = !TeamID
        .Close
     End With  'rst

 Set rst = Nothing

 End Sub

Open in new window

Avatar of Megin
Megin

ASKER

How do I reference the second table? The name of it is StaffTeam. Does it need to be a second piece of code, or is it blended into this code?
Pat,

I have had instances where the .update "seemed" to move the focus to the next record (or maybe it was with a .add and that caused some other record pointer issue).  At anyrate, I always ensure i go back and set the focus to the record I just updated.

This may be a hold over from some issue I encountered many versions ago, but it is now engrained habit.

Dale
You can actually move the statement ahead of the update.  Since Access assigns the autonumber at the .addnew, it is available prior to .update.

To add a record to the second table, create a new ".addnew" statement.  One of the fields to populate will be the foreign key.  Use the saved value in NewTeamID to populate TeamID in the second table.
Avatar of Megin

ASKER

Ok. So I figure this is, like, 100 kinds of wrong. But here is the code I have now.

Private Sub btn_AddtoTeam_Click()

 Dim rst As DAO.Recordset
 Dim strsql As String
 Dim TNAME As String

 TNAME = Forms!frm_Staff.Form!FName & " " & Forms!frm_Staff.Form!Lname
 strsql = "Select Top 1 * from Team"

    Set rst = CurrentDb.OpenRecordset(strsql)
    With rst
        .AddNew
        ![TeamName] = TNAME
        .Update
        NewTeamID = !Teamid
        
        
        .Close
     End With  'rst
strsql = "Select Top 1* from TeamStaff"
    Set rst = CurrentDb.OpenRecordset(strsql)
    With rst
        .AddNew
        ![Teamid] = NewTeamID
        ![STAFFid] = STAFFid
        .Update
        .Close
    
    End With
    

 Set rst = Nothing

 End Sub

Open in new window



I am getting the error message saying my SQL statement includes a reserved word or an argument name that is missing or misspelled, or the punctuation is incorrect.

Do I need to add a new Dim statement because I am referencing a second table? Or is that just completely not even close to explaining the immense wrong that this code is? ;)
@ Pat
" Since Access assigns the autonumber at the .addnew, it is available prior to .update."
Unfortunately, this behavior has changed over various releases of Access - ie exactly just 'when' the AutoNumber is created.

Move 0. .LastModified  always works.

mx
Avatar of Megin

ASKER

Here is what I have now:

Private Sub btn_AddtoTeam_Click()

 Dim rst As DAO.Recordset
 Dim strsql As String
 Dim TNAME As String
 Dim lngID As Long

 TNAME = Forms!frm_Staff.Form!FName & " " & Forms!frm_Staff.Form!Lname
 strsql = "Select Top 1 * from Team"

    Set rst = CurrentDb.OpenRecordset(strsql)
    With rst
        .AddNew
        ![TeamName] = TNAME
        .Update
        .Bookmark = rst.LastModified
    lngID = rst!STAFFid.Value

     End With
    
strsql = "Select Top 1* from TeamStaff"
    Set rst = CurrentDb.OpenRecordset(strsql)
    
With rst
    .AddNew
    ![TEAMid] = lngID
    ![STAFFid] = STAFFid.Value
    .Update
    
    .Close
    End With  'rst

    

 Set rst = Nothing


 End Sub

Open in new window


I am getting the message "Item not found in this collection" now. Debugging highlights
lngID = rst!Staffid.value

Open in new window


Any ideas? Is this the way I should be approaching this?
ASKER CERTIFIED SOLUTION
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Megin

ASKER

But I already have TeamID equal to LngID. Would that still be right?  I want to pull the StaffID from the form and the TeamID from the last record entered on in the Team table (the one added with all the code before this part).
Avatar of Megin

ASKER

StaffID is a field on the form.
Well then ...
Dim STAFFid  As Long
STAFFid  = ' ?? From the Form?  Forms!frm_Staff.Form!FName & " " & Forms!frm_Staff.Form!STAFFid  ??

With rst
    .AddNew
    ![TEAMid] = lngID
    ![STAFFid] = STAFFid
    .Update
   
    .Close
    End With  'rst
Avatar of Megin

ASKER

Okay. I am clearly not understanding this. I have rewritten it a bit and it seems like it should work for me, but it isn't. The code underneath the first SQL statement should add TNAME to the Team table and then bookmark that record.

The code after the second SQL statement should take the primary key from that Team record that was just entered and put it into the TeamStaff table along with the number in the StaffID field from the form.

The form is called frm_Staff.

Here is what I have:
Private Sub btn_AddtoTeam_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strsql As String
Dim TNAME As String
Dim lngID As Long
Dim STAFFid As Long

TNAME = Forms!frm_Staff.Form!FName & " " & Forms!frm_Staff.Form!Lname
STAFFid = Forms!frm_Staff.Form!STAFFid

strsql = "Select Top 1* from Team"

    Set rst = CurrentDb.OpenRecordset(strsql)

    rst.AddNew
        rst!TeamName = TNAME
        rst.Update
        rst.Bookmark = rst.LastModified
        
lngID = rst!TEAMid.Value

strsql = "Select Top 1* from TeamStaff"

Set rst = db.OpenRecordset(strsql)

rst.AddNew
    rst!TEAMid = lngID
    rst!STAFFid = STAFFid

rst.Update

rst.Close

End Sub

Open in new window




I am getting this error message: "The SELECT statement includes a reserved work or an argument name that is misspelled or missing, or the punctuation] is incorrect."

When I debug, it highlights this piece:

set rst = CurrentDb.OpenRecordset (strsql)

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Megin

ASKER

It finally worked! Putting that into the Query builder totally helped.

Thank you, everyone!  I don't know what I would do without this site and all of you experts!
See changes below

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strsql As String
Dim TNAME As String
Dim lngID As Long
Dim STAFFid As Long

TNAME = Forms!frm_Staff.Form!FName & " " & Forms!frm_Staff.Form!Lname
STAFFid = Forms!frm_Staff.Form!STAFFid

'  strsql = "Select Top 1* from Team"   'You don't really need this

    Set rst = CurrentDb.OpenRecordset("Team")

    rst.AddNew
        rst!TeamName = TNAME
        rst.Update
        rst.Bookmark = rst.LastModified
       
lngID = rst!TEAMid.Value

'  strsql = "Select Top 1* from TeamStaff"  ' You don't really need this

Set rst = db.OpenRecordset("TeamStaff")

rst.AddNew
    rst!TEAMid = lngID
    rst!STAFFid = STAFFid

rst.Update

rst.Close
Avatar of Megin

ASKER

More streamlining!

Thank you!!!
Actually (from above)

 Set rst = CurrentDb.OpenRecordset("Team", dbOpenDynaset)


 Set rst = CurrentDb.OpenRecordset("TeamStaff", dbOpenDynaset)
BTW,

If you migrate to SQL Server as a backend, with Access as the front end, then the ID value will not be assigned until after the .Update, so if planning for future migration, you should keep that in mind as well.

Dale