How would I address the "Compile error: qualifier must be a collection" on the indicated line in the code below?
I basically have two tables: items
(ID
, title
, amount
) and relationships
(parentId
, clientId
). I'm basically just trying here to create a new record in items
, and then creating a new relationships
table record as well.
I'm not sure how to allocate the the new items
record (just created) to the new relationships
record's childId
field.
Private Sub Command18_Click()
Debug.Print ("*** Starting ***")
' New Record
Dim rsItems As Recordset
Set rsItems = CurrentDb.OpenRecordset("items")
rsItems.AddNew
rsItems![title] = "title"
rsItems![amount] = 123
rsItems.Update
' Get new ID
rsItems.Bookmark = rsItems.LastModified
newId = rsItems.Bookmark
Debug.Print ("New ITEM record with ID " & newId)
' Relationships
Dim rsRelationship As Recordset
Set rsRelationship = CurrentDb.OpenRecordset("relationships")
rsRelationship.AddNew
'Debug.Print ("Relationships Field Types: " & TypeName(gcItemParentId) & ", " & TypeName(rsItems.LastModified!ID))
rsRelationship![parentId] = gcItemParentId 'taken from text box on main form that holds current parent ID
rsRelationship![clientId] = rsItems.LastModified!["ID"] ' *** ERROR HERE ***
rsRelationship.Update
' Get new ID
rsRelationship.Bookmark = rsRelationship.LastModified
newId = rsItems.Bookmark
Debug.Print ("New RELATIONSHIP record with ID " & newId)
' Refresh Form
Me.Refresh
' Cleanup
rsItems.Close
rsRelationship.Close
Set rsItems = Nothing
Set rsRelationship = Nothing
End Sub
You're getting this error because you're trying to use LastModified
and Bookmark
to represent IDs in records rather than just record positions in recordsets. They can't do that.
What you need instead of newId = rsItems.Bookmark
, therefore, is newId = rsItems!ID
. Once you have the primary key for the record you just added, you'll use it with rsRelationship!clientId = newId
. (You should probably also add a primary key field named RelationshipID
[although the name is not really important] to the Relationships
table. In Access, all tables that have enforceable relationships [in the Access sense] must have primary keys, and generally just about all tables should have them whether they technically need them or not. Mostly because all tables that can theoretically have enforceable relationships should; Access is far better at maintaining data integrity and making easy-to-use links than hand-written code usually is.)
The code as a whole, then, would look something like this, debugging statements, error handling, and redundant comments removed:
Private Sub Command18_Click()
Dim rsItems As Recordset
Set rsItems = CurrentDb.OpenRecordset("items")
rsItems.AddNew
rsItems!title = "title"
rsItems!amount = 123
rsItems.Update
' Go back to the record we just saved
rsItems.Bookmark = rsItems.LastModified
newId = rsItems!ID
Dim rsRelationship As Recordset
Set rsRelationship = CurrentDb.OpenRecordset("relationships")
rsRelationship.AddNew
rsRelationship!parentId = gcItemParentId
rsRelationship!clientId = newId
rsRelationship.Update
Me.Refresh
rsItems.Close
rsRelationship.Close
End Sub