By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,275 Members | 1,990 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,275 IT Pros & Developers. It's quick & easy.

How to locate and update records in a table

100+
P: 125
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
  1.  Private Sub Form_Close()
  2. '
  3. '   Before closing the form, ensure that ThisComm is set for the linked Community
  4. '   (If we have just entered data in tbl Communities and not linked the BE,
  5. '    ThisComm will be still pointing to the new Community and not the linked one).
  6. '
  7. Dim NewComm As String, db As Database, rs As Recordset
  8. On Error GoTo ErrorProc
  9.  
  10. '  See which Community is currently pointed to in Communities table
  11. NewComm = Nz(DLookup("CommunityCode", "Communities", "ThisComm = True"))
  12.  
  13. '  If it's not the linked Community, update Communities table to match
  14. If NewComm <> gblCommunityCode Then
  15.     Set db = CurrentDb
  16.     Set rs = db.OpenRecordset("Communities")
  17.     With rs
  18.         .Index = "CommunityCode"    ' [CommunityCode] is the primary key of tbl Communities
  19.         .Seek "=", NewComm
  20.         .Edit
  21.         !ThisComm = False           ' Clear ThisComm for the new community
  22.         .Update
  23.         .Seek "=", gblCommunityCode
  24.         .Edit
  25.         !ThisComm = True            ' Set ThisComm for the linked community
  26.         .Update
  27.      End With
  28. End If
  29.  
  30. ByeBye:
  31.     Exit Sub
  32.  
  33. ErrorProc:
  34.     MsgBox "Error No. " & Err.Number & ", " & Err.Description
  35.     GoTo ByeBye
  36.  
  37. End Sub
  38.  
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?
3 Weeks Ago #1

✓ answered by 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.

Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,660
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!
3 Weeks Ago #2

100+
P: 125
Ah, has it? I thought the proc might have been executed first. Thank you.
3 Weeks Ago #3

NeoPa
Expert Mod 15k+
P: 31,660
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.
3 Weeks Ago #4

100+
P: 125
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
  1. Private Sub cmdCloseButton_Click()
  2.  
  3. Dim NewComm As String, db As Database, rs As Recordset
  4. Dim Message As String, Response As Integer
  5. On Error GoTo ErrorProc
  6.  
  7. '   Before closing the form, ensure that ThisComm is set for the linked Community
  8. '   (If we have just entered data in tbl Communities and not linked the BE,
  9. '    ThisComm will be still pointing to the new Community and not the linked one).
  10. '
  11. If Me.Dirty Then
  12.    Me.Dirty = False                          '  Save the Communities record
  13. End If
  14.  
  15. '  See which Community is currently pointed to in Communities table
  16. NewComm = Nz(DLookup("CommunityCode", "Communities", "ThisComm = True"))
  17.  
  18. '  If it's not the linked Community, update Communities table to match
  19. If NewComm <> gblCommunityCode Then
  20.     Set db = CurrentDb
  21.     Set rs = db.OpenRecordset("Communities")
  22.     With rs
  23.         .Index = "CommunityCode"    ' [CommunityCode] is the primary key of tbl Communities
  24.         .Seek "=", NewComm
  25.         .Edit
  26.         !ThisComm = False           ' Clear ThisComm for the new community
  27.         .Update
  28.         .Seek "=", gblCommunityCode
  29.         .Edit
  30.         !ThisComm = True            ' Set ThisComm for the linked community
  31.         .Update
  32.      End With
  33. End If
  34.  
  35. ByeBye:
  36.     DoCmd.Close acForm, "", acSaveYes
  37.     Exit Sub
  38.  
  39. ErrorProc:
  40.     MsgBox "Error No. " & Err.Number & ", " & Err.Description
  41.     GoTo ByeBye
  42.  
  43. End Sub
3 Weeks Ago #5

NeoPa
Expert Mod 15k+
P: 31,660
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.
3 Weeks Ago #6

100+
P: 125
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.
3 Weeks Ago #7

NeoPa
Expert Mod 15k+
P: 31,660
Glad it help Petrol. Always happy to :-)
3 Weeks Ago #8

Post your reply

Sign in to post your reply or Sign up for a free account.