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

    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
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,142
    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 Very idle programmer
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    4,928
    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 (Mendip Data Systems): Website, email
    Try again. Fail again. Fail better.
    A
    sking for help isn't giving up. Its refusing to give up.

  4. #4
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,781
    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
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,142
    or just

    BillAmount.Visible = Complaint = "Billed wrong amount"

  6. #6
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    134
    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
    2,781
    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
    134
    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
    Welshgasman is offline Expert
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    994
    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, please use # when posting code snippets.
    Cross Posting Etiquette: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



  10. #10
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,142
    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
    134
    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
    134
    Nvm I figured it out (<> instead of =)! Thanks everyone!!!!

  13. #13
    Welshgasman is offline Expert
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    994
    The control is comboComplaint and you are testing Complaint?
    Please, please use # when posting code snippets.
    Cross Posting Etiquette: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+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 - Senior Forums