0

I recently created two new forms CommunitySMBSubform and CommunityUsersSubform.

Forms

As I was writing some VBA code to Requery them I encountered:

Compile Error: Method or data member not found

In troubleshooting the cause, I discovered forms have no class objects and this may be the cause of the error.

class objects

Is there anything that can be done to establish their class objects so that they can be referenced by VBA?

TIA.

-------- Code in question below ------------

Private Sub ClearForm_Click()

Dim db As Database
Set db = CurrentDb
Dim Query As String
Dim Query1 As String
Dim Query2 As String

'Clear any selected mailboxes

Query = "Update [Mailbox Status] Set AddToBatchFlag = FALSE Where 
AddToBatchFlag = TRUE"
db.Execute Query

' Requery the SMB Subform
Query1 = "Select * from CommunitySMB"
db.Execute Query1
Me.CommunitySMBSubform.Requery    <=  Compile Error: Method or data member not found occurs here

'Requery the Users Subform
Query2 = "Select * from CommunityUser"
db.Execute Query2
Me.CommunityUsersSubform.Requery

RefreshBatchStats_Click

End Sub
Tom Zvolensky
  • 63
  • 6
  • 1
    Sometimes corruption cannot be fixed. Can you open form in design view and create a VBA event procedure by selecting in Property Sheet? – June7 Apr 01 '20 at 19:41
  • I was able to do that, and now their objects appear. I was hoping that this would do the trick, however, when the below code executes, it still throws the Compile Error: Method or data member not found ... on the second to last line. ' Requery the SMB Subform Query1 = "Select * from CommunitySMB" Me.CommunitySMBSubform.Form.RecordSource = Query1 Me.CommunitySMBSubform.Requery I was hopeful, but perhaps I was barking up the wrong tree. – Tom Zvolensky Apr 01 '20 at 20:00
  • Edit question to post your code. Must reference subform container name. I always name container different from object it holds. Why are you setting RecordSource property just to do a requery? Try Requery method. Try Me.YOURsubformcontainername.Requery. – June7 Apr 01 '20 at 20:02
  • Run Compact & Repair. If that doesn't fix then delete forms, run C&R, build new forms. If still have issue, import working elements to new db file, run C&R, build forms. Sometimes decompiling then recompiling can help. https://stackoverflow.com/questions/55901715/how-to-decompile-an-access-database – June7 Apr 01 '20 at 20:10
  • Code added to the question. C&R has no impact. If there is no simple resolution, will follow your advice, albeit with a lot or re-work. – Tom Zvolensky Apr 01 '20 at 20:16
  • 1
    Don't use Execute on a SELECT statement. SELECT statements are for setting recordset objects. This code is behind main form? The UPDATE statement should have compile error if you really have it broken with carriage return. Requery code works for me. – June7 Apr 01 '20 at 20:55
  • Prior to reading your comment, I realized the Queryx= and Execute lines are not needed and removed them. I ended up deleting the subforms, re-created them and added them to the parent form. After doing this, the code to clear the checkboxes on the form works properly. Fortunately, the other options were not necessary. Thanks very much for your comments & suggestions! – Tom Zvolensky Apr 02 '20 at 01:44

0 Answers0