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:
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!
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
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!
OR ...
Dim lID As Long
With rst
.AddNew
![TeamName] = TNAME
.Update
.Move 0, .LastModified
lID = ![YourPrimaryKeyFieldNameH ere]
.Close
End With 'rst
Dim lID As Long
With rst
.AddNew
![TeamName] = TNAME
.Update
.Move 0, .LastModified
lID = ![YourPrimaryKeyFieldNameH
.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
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
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
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
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.
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.
ASKER
Ok. So I figure this is, like, 100 kinds of wrong. But here is the code I have now.
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? ;)
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
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
" 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
ASKER
Here is what I have now:
I am getting the message "Item not found in this collection" now. Debugging highlights
Any ideas? Is this the way I should be approaching this?
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
I am getting the message "Item not found in this collection" now. Debugging highlights
lngID = rst!Staffid.value
Any ideas? Is this the way I should be approaching this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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).
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!STAFF id ??
With rst
.AddNew
![TEAMid] = lngID
![STAFFid] = STAFFid
.Update
.Close
End With 'rst
Dim STAFFid As Long
STAFFid = ' ?? From the Form? Forms!frm_Staff.Form!FName
With rst
.AddNew
![TEAMid] = lngID
![STAFFid] = STAFFid
.Update
.Close
End With 'rst
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:
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:
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
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)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!
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!STAFF id
' 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
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
STAFFid = Forms!frm_Staff.Form!STAFF
' 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
ASKER
More streamlining!
Thank you!!!
Thank you!!!
Actually (from above)
Set rst = CurrentDb.OpenRecordset("T eam", dbOpenDynaset)
Set rst = CurrentDb.OpenRecordset("T eamStaff", dbOpenDynaset)
Set rst = CurrentDb.OpenRecordset("T
Set rst = CurrentDb.OpenRecordset("T
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
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
.update
Line, insert:
.bookmark = .lastmodified
Then, you can use something like:
LngID = .ID
.close