Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    May 2022
    Posts
    9

    How to Show and Hide a field in a SubForm depending on the value in a field on Main form.

    I have also posted this question in Access World Forum you can Click here to see.



    Hi, I need help,

    I've created a voucher form to make different vouchers. There is a lookup field to select the Type of Voucher.
    List of Voucher Types is:

    • CP-Cash Payment
    • CR-Cash Receipt
    • BP-Bank Payment
    • BR-Bank Receipt
    • PB-Party Bill
    • JO-Journal
    • SL-Sales



    A subform named GeneralLedger is associated with this main form.
    Below are some of fields in the subform:
    AccountCode, AccountID, InvoiceNo, ChequeNo, InvoiceDate etc.



    I want to Hide ChequeNo field and Show InvoiceNo field if VoucherType in the main form is:
    CP-Cash Payment, CR-Cash Receipt, PB-Party Bill, JO-Journal Or SL-Sales

    And I want to Hide InvoiceNo and Show ChequeNo field if VoucherType in the main form is:
    BP-Bank Payment Or BR-Bank Receipt


    I've tried the following code:
    Code:
    Private Sub VoucherType_Click()
    Dim SubFrm As Form
    Dim SelectType
    Set SubFrm = Me.GeneralLedger.Form
    SelectType = Me!VoucherType
    Select Case SelectType
    Case "CP-Cash-Payment"
    SubFrm.ChequeNo.Visible = False
    Case "CR-Cash Receipt"
    SubFrm.ChequeNo.Visible = False
    Case "PB-Party Bill"
    SubFrm.ChequeNo.Visible = False
    Case "JO-Journal "
    SubFrm.ChequeNo.Visible = False
    Case "SL-Sales"
    SubFrm.ChequeNo.Visible = False
    Case "BP-Bank Payment"
    SubFrm.ChequeNo.Visible = True
    Case "BR-Bank Receipt"
    SubFrm.ChequeNo.Visible = True
    End Case
    SubFrm.Refresh
    End Sub
    But there is an error on End Case





    Thank you in advance.

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001

    Post

    You need to use

    End Select

    Not End Case

    Also with a Case statment you can use

    Code:
    Case "value1", "value2", "Value5"
        Do SOmething here
    
    Case "Value3", "Value6"
       Do Something here
    
    Case Else
    
    End Select
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,860
    Posted in error.
    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

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Also remove the unwanted spaces inside the quotation marks in each Select Case statement
    e.g. Case " JO-Journal " should be Case "JO-Journal"
    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

  5. #5
    Join Date
    May 2022
    Posts
    9
    Thank you Minty,
    Now replaced End Case with End Select:

    Code:
    Private Sub VoucherType_AfterUpdate()
    Dim SubFrm As Form
    Dim SelectType
    Set SubFrm = Me.GeneralLedger.Form
    SelectType = Me!VoucherType
    Select Case SelectType
    Case "CP-Cash-Payment"
    SubFrm.ChequeNo.Visible = False
    Case "CR-Cash Receipt"
    SubFrm.ChequeNo.Visible = False
    Case "PB-Party Bill"
    SubFrm.ChequeNo.Visible = False
    Case "JO-Journal"
    SubFrm.ChequeNo.Visible = False
    Case "SL-Sales"
    SubFrm.ChequeNo.Visible = False
    Case "BP-Bank Payment"
    SubFrm.ChequeNo.Visible = True
    Case "BR-Bank Receipt"
    SubFrm.ChequeNo.Visible = True
    End Select
    SubFrm.Refresh
    End Sub

  6. #6
    Join Date
    May 2022
    Posts
    9
    Deleted all unwanted spaces inside the quotation marks. My code is still not working even though there is no error now.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Try this

    Code:
    Private Sub VoucherType_AfterUpdate()Dim SubFrm As Form
    Set SubFrm = Me.GeneralLedger.Form
    
    
    Select Case Me!VoucherType
    Case "CP-Cash-Payment"
    SubFrm.ChequeNo.Visible = False
    Case "CR-Cash Receipt"
    SubFrm.ChequeNo.Visible = False
    Case "PB-Party Bill"
    SubFrm.ChequeNo.Visible = False
    Case "JO-Journal"
    SubFrm.ChequeNo.Visible = False
    Case "SL-Sales"
    SubFrm.ChequeNo.Visible = False
    Case "BP-Bank Payment"
    SubFrm.ChequeNo.Visible = True
    Case "BR-Bank Receipt"
    SubFrm.ChequeNo.Visible = True
    End Select
    
    
    Me.GeneralLedger.Requery
    End Sub
    Once you get it working, streamline your code similar to that given by Minty
    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

  8. #8
    Join Date
    May 2022
    Posts
    9
    Its also not working. Should I make any change in the property of field ChequeNo and InvoiceNo?
    You can check the screenshot below VoucherType: CR-Cash Receipt is selected and InvoiceNo and ChequeNo both are visible.
    Click image for larger version. 

Name:	V.jpg 
Views:	14 
Size:	43.3 KB 
ID:	47847

  9. #9
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    As this is a datasheet, and you aren't getting any obvious errors, try hiding the column instead of using the visible property;

    SubFrm.ChequeNo.ColumnHidden = True
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    What provides the combo list data - table, query or value list and how many columns does it have. Possibly combo list is 2 or more columns so its value isn't what you're seeing.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    Join Date
    May 2022
    Posts
    9
    Thank you so much Minty. Its working now. But there is a small problem.
    Code:
    Private Sub VoucherType_AfterUpdate()
    Dim SubFrm As Form
    Set SubFrm = Me.GeneralLedger.Form
    Select Case Me!VoucherType
    Case "CP-Cash-Payment"
    SubFrm.ChequeNo.ColumnHidden = True
    Case "CR-Cash Receipt"
    SubFrm.ChequeNo.ColumnHidden = True
    Case "PB-Party Bill"
    SubFrm.ChequeNo.ColumnHidden = True
    Case "JO-Journal"
    SubFrm.ChequeNo.ColumnHidden = True
    Case "SL-Sales"
    SubFrm.ChequeNo.ColumnHidden = True
    Case "BP-Bank Payment"
    SubFrm.ChequeNo.ColumnHidden = False
    Case "BR-Bank Receipt"
    SubFrm.ChequeNo.ColumnHidden = False
    
    
    End Select
    Me.GeneralLedger.Requery
    
    
    End Sub
    Its Hiding the ChequeNo column when the VoucherType is:
    "CP-Cash-Payment" Or "CR-Cash Receipt" Or "PB-Party Bill" Or "JO-Journal" Or "SL-Sales"
    and Showing the ChequeNo column when the VoucherType is:
    "BP-Bank Payment" Or "BR-Bank Receipt"

    I also want to show the InvoiceNo column when the VoucherType is:
    "CP-Cash-Payment" Or "CR-Cash Receipt" Or "PB-Party Bill" Or "JO-Journal" Or "SL-Sales"
    and hide the InvoiceNo column when the VoucherType is:
    "BP-Bank Payment" Or "BR-Bank Receipt"

    How to change the above code for the desired result?

  12. #12
    Join Date
    May 2022
    Posts
    9
    Thank you for your response Micron! Its a value list. You can see the attached image.
    Click image for larger version. 

Name:	valuelist.jpg 
Views:	12 
Size:	51.6 KB 
ID:	47855

  13. #13
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Something like below. Note how indenting and spacing out your code makes it much easier to read and see what is happening.

    Code:
    Private Sub VoucherType_AfterUpdate()
        
        Dim SubFrm As Form
    
        Set SubFrm = Me.GeneralLedger.Form
    
        Select Case Me.VoucherType
    
            Case "CP-Cash-Payment", "CR-Cash Receipt", "PB-Party Bill", "JO-Journal", "SL-Sales"
                SubFrm.ChequeNo.ColumnHidden = True
                SubFrm.InvoiceNo.ColumnHidden = False
    
            Case "BP-Bank Payment", "BR-Bank Receipt"
                SubFrm.ChequeNo.ColumnHidden = False
                SubFrm.InvoiceNoNo.ColumnHidden = True
    
        End Select
       
        Me.GeneralLedger.Requery
    
    End Sub
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  14. #14
    Join Date
    May 2022
    Posts
    9
    The Code is working perfectly!

    Code:
    Private Sub VoucherType_AfterUpdate()
    Dim SubFrm As Form
    
    
        Set SubFrm = Me.GeneralLedger.Form
    
    
        Select Case Me.VoucherType
    
    
            Case "CP-Cash-Payment", "CR-Cash Receipt", "PB-Party Bill", "JO-Journal", "SL-Sales"
                SubFrm.ChequeNo.ColumnHidden = True
                SubFrm.InvoiceNo.ColumnHidden = False
    
    
            Case "BP-Bank Payment", "BR-Bank Receipt"
                SubFrm.ChequeNo.ColumnHidden = False
                SubFrm.InvoiceNo.ColumnHidden = True
    
    
        End Select
       
        Me.GeneralLedger.Requery
    
    
    End Sub
    Thank you so much dear Minty! Thanks alot.

  15. #15
    Join Date
    May 2022
    Posts
    9
    How to mark this post to Solved? I am new on this Forum please guide me.
    Thank you

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

Similar Threads

  1. Replies: 3
    Last Post: 03-06-2022, 03:16 AM
  2. Replies: 7
    Last Post: 05-10-2021, 10:01 AM
  3. Replies: 3
    Last Post: 07-20-2014, 08:56 PM
  4. Replies: 9
    Last Post: 12-15-2010, 01:44 PM
  5. Replies: 1
    Last Post: 11-13-2010, 12:57 PM

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