[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 459
  • Last Modified:

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!
0
Megin
Asked:
Megin
  • 8
  • 7
  • 5
  • +2
3 Solutions
 
Dale FyeCommented:
After the

.update

Line, insert:

.bookmark = .lastmodified

Then, you can use something like:

LngID = .ID
.close
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
OR ...
    Dim lID As Long
    With rst
        .AddNew
        ![TeamName] = TNAME
        .Update
        .Move 0,  .LastModified
        lID = ![YourPrimaryKeyFieldNameHere]
        .Close
     End With  'rst
0
 
Dale FyeCommented:
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
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
I don't know actually ... never used the Bookmark for this.

btw .... re

LngID = .ID

are you sure it shouldn't be

LngID = !ID
0
 
Dale FyeCommented:
Good catch
0
 
PatHartmanCommented:
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

0
 
MeginAuthor Commented:
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?
0
 
Dale FyeCommented:
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
0
 
PatHartmanCommented:
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.
0
 
MeginAuthor Commented:
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? ;)
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
@ 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
0
 
MeginAuthor Commented:
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?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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 = ![STAFFid]
        .Close

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

 Set rst = Nothing
0
 
MeginAuthor Commented:
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).
0
 
MeginAuthor Commented:
StaffID is a field on the form.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
0
 
MeginAuthor Commented:
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

0
 
PatHartmanCommented:
Copy the SQL string and paste it into the query builder.  You'll get better error messages.
0
 
IrogSintaCommented:
I believe its because you forgot the space
strsql = "Select Top 1* from TeamStaff"
should be
strsql = "Select Top 1 * from TeamStaff"

Ron
0
 
MeginAuthor Commented:
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!
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
0
 
MeginAuthor Commented:
More streamlining!

Thank you!!!
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Actually (from above)

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


 Set rst = CurrentDb.OpenRecordset("TeamStaff", dbOpenDynaset)
0
 
Dale FyeCommented:
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
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 8
  • 7
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now