In the OnClose procedure for a form I want to modify the [ThisComm] field of a couple of records in the form's underlying table, table "Communities". Microsoft Docs (https://docs.microsoft.com/en-us/office/vba/access/concepts/data-access-objects/find-a-record-in-a-table-type-dao-recordset) seems to tell me that I should use the Seek method to do this. So I've written the following code:
- Private Sub Form_Close()
-
'
-
' Before closing the form, ensure that ThisComm is set for the linked Community
-
' (If we have just entered data in tbl Communities and not linked the BE,
-
' ThisComm will be still pointing to the new Community and not the linked one).
-
'
-
Dim NewComm As String, db As Database, rs As Recordset
-
On Error GoTo ErrorProc
-
-
' See which Community is currently pointed to in Communities table
-
NewComm = Nz(DLookup("CommunityCode", "Communities", "ThisComm = True"))
-
-
' If it's not the linked Community, update Communities table to match
-
If NewComm <> gblCommunityCode Then
-
Set db = CurrentDb
-
Set rs = db.OpenRecordset("Communities")
-
With rs
-
.Index = "CommunityCode" ' [CommunityCode] is the primary key of tbl Communities
-
.Seek "=", NewComm
-
.Edit
-
!ThisComm = False ' Clear ThisComm for the new community
-
.Update
-
.Seek "=", gblCommunityCode
-
.Edit
-
!ThisComm = True ' Set ThisComm for the linked community
-
.Update
-
End With
-
End If
-
-
ByeBye:
-
Exit Sub
-
-
ErrorProc:
-
MsgBox "Error No. " & Err.Number & ", " & Err.Description
-
GoTo ByeBye
-
-
End Sub
-
But as soon as it hits the first Seek statement (line 19) it gets "Error No. 3251, Operation is not supported for this type of object."
What am I doing wrong?