Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    wtucker is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jun 2017
    Posts
    50

    Question On Field Change, Visible = True

    Running into some VBA errors here. Can someone help troubleshoot?



    I want the fields on the form to turn invisible while the RecordType field value is blank or "New".
    All fields are on a subform. The code is stored in the subform Class Object.

    I'm getting the error:
    Compile error: Method or data member not found

    Code:
    Private Sub RecordType_Change()
    If ([me.RecordType = "" or "New") Then
    Me.RequestSent.Visible = False
    Me.Created.Visible = False
    Me.Approved.Visible = False
    Me.Updated.Visible = False
    Me.Built.Visible = False
    Me.Loaded.Visible = False
    Me.BankingCompleted.Visible = False
    Me.BillingSetUp.Visible = False
    Me.TurnedOn.Visible = False
    End If
    End Sub

  2. #2
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    First, just FYI, the Change event fires at every key stroke. You want to use the After Update event. You'll also likely want to place the code in the Current event of the Form as well.

    Second, which line is highlighted by the debugger when the error occurs?

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Remove the solo [.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    wtucker is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jun 2017
    Posts
    50
    Quote Originally Posted by June7 View Post
    Remove the solo [.
    A copy and pasting error, I assure you.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    And have to repeat the field in the conditional. Should maybe deal with possible Null as well.
    If Me.RecordType & "" = "" Or Me.RecordType = "New" Then

    Understand this code will affect every record at same time.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    wtucker is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jun 2017
    Posts
    50
    Quote Originally Posted by Beetle View Post
    First, just FYI, the Change event fires at every key stroke. You want to use the After Update event. You'll also likely want to place the code in the Current event of the Form as well.

    Second, which line is highlighted by the debugger when the error occurs?
    Thanks for the quick tips. I moved the code to the form After Update and On Current event procedures.
    I still get the same error. It highlights the first line of each sub: Private Sub Form_AfterUpdate() and Private Sub Form_Current()

  7. #7
    wtucker is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jun 2017
    Posts
    50
    Quote Originally Posted by June7 View Post
    And have to repeat the field in the conditional.
    If Me.RecordType = "" Or Me.RecordType = "New" Then

    Understand this code will affect every record.
    Fixed that part (again, thank you!). I have four of these subs. Only one has the OR clause.
    When you say this will affect every record, you mean that as I go through records, the code will run each time, correct?

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    On continuous form all records will show the same. I think datasheet will ignore the code.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    wtucker is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jun 2017
    Posts
    50
    Quote Originally Posted by June7 View Post
    On continuous form all records will show the same. I think datasheet will ignore the code.
    I've got it on single form with navigation buttons. I'd like it to refresh every time I go to a new record.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Then suggested code should work. See post 5.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    wtucker is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jun 2017
    Posts
    50
    So far no luck. I've decided to include the entire code I'm working with.
    When making a change to the record, nothing happens. When moving from one record to another, I get the same error as above: Compile error: Method or data member not found

    Code:
    Private Sub Form_AfterUpdate()If Me.RecordType & "" = "" Then
    Me.RequestSent.Visible = False
    Me.ScheduleCreated.Visible = False
    Me.ScheduleApproved.Visible = False
    Me.MatrixUpdated.Visible = False
    Me.PlanYearbuilt.Visible = False
    Me.ContributionsLoaded.Visible = False
    Me.BankingCompleted.Visible = False
    Me.BillingSetUp.Visible = False
    Me.CCSMTurnedOn.Visible = False
    
    
    ElseIf Me.RecordType = "Reissue - Change" Then
    Me.MatrixUpdated.Visible = False
    Me.BankingCompleted.Visible = False
    Me.CCSMTurnedOn.Visible = False
    
    
    ElseIf Me.RecordType = "Reissue - No Change" Then
    Me.ScheduleApproved.Visible = False
    Me.MatrixUpdated.Visible = False
    Me.BankingCompleted.Visible = False
    Me.CCSMTurnedOn.Visible = False
    
    
    ElseIf Me.RecordType = "Banking Change" Or Me.RecordType = "Misc. Change" Then
    Me.ScheduleCreated.Visible = False
    Me.ScheduleApproved.Visible = False
    Me.MatrixUpdated.Visible = False
    Me.PlanYearbuilt.Visible = False
    Me.ContributionsLoaded.Visible = False
    End If
    End Sub
    Code:
    Private Sub Form_Current()If Me.RecordType & "" = "" Then
    Me.RequestSent.Visible = False
    Me.ScheduleCreated.Visible = False
    Me.ScheduleApproved.Visible = False
    Me.MatrixUpdated.Visible = False
    Me.PlanYearbuilt.Visible = False
    Me.ContributionsLoaded.Visible = False
    Me.BankingCompleted.Visible = False
    Me.BillingSetUp.Visible = False
    Me.CCSMTurnedOn.Visible = False
    
    
    ElseIf Me.RecordType = "Reissue - Change" Then
    Me.MatrixUpdated.Visible = False
    Me.BankingCompleted.Visible = False
    Me.CCSMTurnedOn.Visible = False
    
    
    ElseIf Me.RecordType = "Reissue - No Change" Then
    Me.ScheduleApproved.Visible = False
    Me.MatrixUpdated.Visible = False
    Me.BankingCompleted.Visible = False
    Me.CCSMTurnedOn.Visible = False
    
    
    ElseIf Me.RecordType = "Banking Change" Or Me.RecordType = "Misc. Change" Then
    Me.ScheduleCreated.Visible = False
    Me.ScheduleApproved.Visible = False
    Me.MatrixUpdated.Visible = False
    Me.PlanYearbuilt.Visible = False
    Me.ContributionsLoaded.Visible = False
    End If
    End Sub

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    In your latest code, the first line also includes an If statement in each case
    That may be a glitch when you posted but if that is your code, move those to the following line.

    Otherwise, you may have a missing VBA reference.
    Open the VBE, GO TO Tools...References and post a screenshot.
    Whilst in the VBE, go to Options and tick Require Variable Declaration.
    Next make sure you have the line Option Explicit as the second line in every code module.
    Then go to Debug menu and click Compile.
    Fix any compile errors it finds.
    You may have to do this several times until it is ok.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  13. #13
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I get the same error as above: Compile error: Method or data member not found
    and the line that is generating the error will be highlighted - which line is it?

  14. #14
    wtucker is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jun 2017
    Posts
    50
    Quote Originally Posted by ridders52 View Post
    In your latest code, the first line also includes an If statement in each case
    That may be a glitch when you posted but if that is your code, move those to the following line.

    Otherwise, you may have a missing VBA reference.
    Open the VBE, GO TO Tools...References and post a screenshot.
    Whilst in the VBE, go to Options and tick Require Variable Declaration.
    Next make sure you have the line Option Explicit as the second line in every code module.
    Then go to Debug menu and click Compile.
    Fix any compile errors it finds.
    You may have to do this several times until it is ok.
    That is a glitch. My code has them as two lines.
    For Option Explicit, I went to Options and checked the box to require variable declaration so that Option Explicit is the default.

    I compiled the database and got the "Method or data member not found" error for each Me.MatrixUpdated.Visible = False
    After removing those from my code, I can no longer compile the database.
    I no longer get an error when navigating through records, but nothing works yet.
    Why would that field be an issue? I've checked it over and over, and there's nothing wrong with it.


    Here's a screenshot of my References:

    Click image for larger version. 

Name:	Access Screenshot.JPG 
Views:	15 
Size:	47.9 KB 
ID:	33723

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 03-31-2018, 04:38 PM
  2. Replies: 5
    Last Post: 08-14-2017, 02:19 AM
  3. Replies: 1
    Last Post: 03-27-2016, 10:29 PM
  4. visible form but not have focus when true
    By Ruegen in forum Programming
    Replies: 5
    Last Post: 04-30-2015, 10:31 AM
  5. Replies: 4
    Last Post: 05-29-2013, 01:29 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums