How to locate and update records in a table

Petrol

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:

Expand|Select|Wrap|Line Numbers

  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?

NeoPa

You're trying to update a Form (or the bound data thereon) when it's not in the Open state. Remember, by this point a request has already been submitted to close it.

This is not a _BeforeClose() event!

Petrol

Ah, has it? I thought the proc might have been executed first. Thank you.

NeoPa

Sure it has. It wouldn't be triggered unless the request had been triggered somehow. That can happen a number of ways but you can be darn sure it has been.

You may get some joy with the Form_Unload() Event Procedure but I couldn't find anything clear. Try it out and see may make sense.

Petrol

Well that's funny (sort of). I decided to move the offending code from the OnClose proc to an OnClick event for the Close button (with an appropriate test and warnng message in the OnClose event in case they bypass the Close button).
But it gets the same 3251 error on the same Seek statement as before, even though there's as yet no sign of the form being closed.
The code is now

Expand|Select|Wrap|Line Numbers

 
Private Sub cmdCloseButton_Click()
 
Dim NewComm As String, db As Database, rs As Recordset

Dim Message As String, Response As Integer

On Error GoTo ErrorProc
 
'   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).

'

If Me.Dirty Then

   Me.Dirty = False                          '  Save the Communities record

End If
 
'  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:

    DoCmd.Close acForm, "", acSaveYes

    Exit Sub
 
ErrorProc:

    MsgBox "Error No. " & Err.Number & ", " & Err.Description

    GoTo ByeBye
 
End Sub

NeoPa

Hi Petrol.

I'm pretty sure .Seek is a local table only Method. That means the Recordset must be opened with Type:=dbOpenTable, which only works with local tables (IE. Not linked at all). The default when not specified (as in your code) is dbOpenDynaset.

Petrol

Experimentation confirms that you're right - as always!
So I switched to dbOpenDynaset explicitly (to be on the safe side) and used .FindFirst. That works.
Thanks again.

NeoPa

Glad it help Petrol. Always happy to :-)

How to locate and update records in a table

✓ answered by NeoPa

Post your reply

Similar topics