Results 1 to 13 of 13
  1. #1
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305

    Can a "field visible/invisible" VBA execute before an entry is saved?

    Sorry if the title is confusing. I've attached an example database.



    Essentially, I've got a form of complaints and some fields are visible/invisible based on the complaint type. If we change the category for whatever reason, the visible/invisible fields remain visible/invisible until the entry is saved and you move to another entry. If you return, the field is now visible/invisible. See the example. If you change the second entry to "billed wrong amount", the billed amount field doesn't appear until you leave the entry.

    Bit of a shot in the dark, but is there a way for the fields to show/hide immediately? Prior to having to save and switch entries?

    I thought it was at least worth checking here. Thanks!

    Example1.accdb

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    look at using the after update event of the category control. You may need additional code to 'zero' fields that are made invisible as a result and have already been populated

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Remember also that you cannot hide a control that has the focus.
    If that is an issue, first change the focus to another visible & enabled control
    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

  4. #4
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    As Ajax suggested, use afterupdate event like this:

    Code:
    Option Compare Database
    Option Explicit
    
    
    Private Sub ComboComplaint_AfterUpdate()
        Call Form_Current
    End Sub
    
    
    Private Sub Form_Current()
        If Complaint = "Billed wrong amount" Then
            BillAmount.Visible = True
        Else
            BillAmount.Visible = False
        End If
    End Sub

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    or just

    BillAmount.Visible = Complaint = "Billed wrong amount"

  6. #6
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    Quote Originally Posted by davegri View Post
    As Ajax suggested, use afterupdate event like this:

    Code:
    Option Compare Database
    Option Explicit
    
    
    Private Sub ComboComplaint_AfterUpdate()
        Call Form_Current
    End Sub
    
    
    Private Sub Form_Current()
        If Complaint = "Billed wrong amount" Then
            BillAmount.Visible = True
        Else
            BillAmount.Visible = False
        End If
    End Sub
    This worked!! Thank you!!

    Once last question, say I'm changing it from "billed wrong amount" to "never received bill" and so the billed amount field goes away. Is it possible to include code which clears out the billed amount field as well?

  7. #7
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Once last question, say I'm changing it from "billed wrong amount" to "never received bill" and so the billed amount field goes away. Is it possible to include code which clears out the billed amount field as well?
    Try adding this:

    Code:
    Private Sub ComboComplaint_AfterUpdate()
        If Complaint = "Didn't Receive Bill" Then
            BillAmount = 0
        End If
        Call Form_Current
    End Sub
    
    
    Private Sub Form_Current()
        If Complaint = "Billed wrong amount" Then
            BillAmount.Visible = True
        Else
            BillAmount.Visible = False
        End If
    End Sub

  8. #8
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    If I wanted it blank rather than zero (say for a non-number field), would I just do:

    Code:
    Private Sub ComboComplaint_AfterUpdate()    
        If Complaint = "Didn't Receive Bill" Then
            BillAmount = ""
        End If
        Call Form_Current
    End Sub

  9. #9
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by templeowls View Post
    If I wanted it blank rather than zero (say for a non-number field), would I just do:

    Code:
    Private Sub ComboComplaint_AfterUpdate()    
        If Complaint = "Didn't Receive Bill" Then
            BillAmount = ""
        End If
        Call Form_Current
    End Sub
    Try it and see?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    For numbers I would use null rather than “”. Not a good idea to mix datatypes

  11. #11
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    Quote Originally Posted by davegri View Post
    Try adding this:

    Code:
    Private Sub ComboComplaint_AfterUpdate()
        If Complaint = "Didn't Receive Bill" Then
            BillAmount = 0
        End If
        Call Form_Current
    End Sub
    
    
    Private Sub Form_Current()
        If Complaint = "Billed wrong amount" Then
            BillAmount.Visible = True
        Else
            BillAmount.Visible = False
        End If
    End Sub
    This worked and the null did the trick too. Very last question...I promise. Is there a way to kinda reverse the coding so it reads if complaint doesn't equal 'billed wrong amount' then 'BilledAmount' equals 0? I ask because I have a lot more categories in my real DB besides just Didn't Receive Bill so I'd have to build an IF code for all of them with this structure

  12. #12
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    Nvm I figured it out (<> instead of =)! Thanks everyone!!!!

  13. #13
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    The control is comboComplaint and you are testing Complaint?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 11-14-2019, 11:30 AM
  2. Replies: 2
    Last Post: 11-13-2016, 02:04 PM
  3. Execute, "Too few parameters", "Expected 2"
    By jhrBanker in forum Forms
    Replies: 3
    Last Post: 10-30-2014, 02:18 PM
  4. Replies: 7
    Last Post: 02-23-2012, 07:19 PM
  5. Replies: 7
    Last Post: 01-29-2012, 07:44 AM

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