Results 1 to 7 of 7
  1. #1
    caltrans is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    4

    hide columns in datasheet based on combo box selection in main form


    I have a main form with an unbound combo for selecting the necessary filter for the datasheet. If I select value "A" from the combo box I want to hide columns "4", "5", "6" in the datasheet. After generating reports on based on this initial filter, I clear the datasheet select a new value from the combo box, lets say "B" and generate a new datasheet, but this time I want columns "4", "5", "6" to be visible and columns "7", "8", "9" hidden. Any suggestions?

  2. #2
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    if you set the visible property for a control it will not sure up in datasheet view for a form.
    So somehting like this in the After Update Event for the Combo box
    Code:
    sub mycombobox_afterupdate()
      select case ucase(me.text)
         case "A" 
         me.controlname4.visible = false
         me.controlname5.visible = false
         me.controlname6.visible = false
         case "B"
         me.controlname4.visible = true
         me.controlname5.visible = true
         me.controlname6.visible = true
         me.controlname7.visible = false
         me.controlname8.visible = false
         me.controlname9.visible = false
    end select
    end sub

  3. #3
    caltrans is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    4
    Perceptus:

    I think you are on the right track, as I can type in
    Select Case Me.look_for_material
    Case "Soil"
    Me.Search_Subform_Stats.visible=False
    End Select

    and the subform disappears. But I have not been able to figure out how to specify any of the columns to disappear in the datasheet. I tried both Me.controlbox4 and Me.controls.4 as there is no me.controlname4, but neither one worked. Any suggestions?

    Thanks
    Caltrans

  4. #4
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    the visible property relates to controls on a form, not columns in a datasheet

    use controlname.columnwidth=0

  5. #5
    caltrans is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    4
    Thanks, but it's a no go. The coding doesn't recognize the text box for the column that I am trying to hide in the subform datasheet. This is what I have tried:
    Select Case Me.look_for_material
    Case "Soil"
    Me.4.columnwidth=0
    End Select

    Where "4" is the name of the text box in the datasheet. The actual control source field name is "50004" from the underlying query. I tried replacing 4 with [50004] both with and without brackets and it made no difference. I get a compile error "Method or data member not found".

    Caltrans

  6. #6
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    using numbers for field and control names is a bad idea and is probably the reason for your problem

    I'm surprises you didn't get an error when you typed

    Me.4.columnwidth=0

    in vba

    this works for me

    Me.[4].columnwidth=0

  7. #7
    caltrans is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    4
    Actually, you're correct and while the field names are numbers corresponding to various test methods, the control names are all nonnumeric names. I was trying to keep the sample simple by using "4", but I had tried it with the actual control name "pH". I think I know what's going on, but I'm not sure how to fix it. Let me run this past you. The combo box is unbound (no control source) in the main form. When you select a drop down from the this box, to populate the subform it's necessary to click on a command button which runs a Where clause. By placing an event on the "After Update" of the combo box in question, it's looking for a field in the subform that isn't populated until after the Where clause has been run. Hence the compile error. I tried finding a way to hide the necessary columns on Loading the subform, but that hasn't worked, so maybe the coding needs to be associated with the code for the Where clause. Does that make any sense?

    Thanks
    Caltrans

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

Similar Threads

  1. Replies: 4
    Last Post: 05-29-2013, 01:29 AM
  2. Replies: 8
    Last Post: 08-26-2012, 11:11 PM
  3. Replies: 1
    Last Post: 05-23-2011, 07:11 AM
  4. Replies: 6
    Last Post: 02-24-2011, 03:24 PM
  5. Replies: 6
    Last Post: 06-03-2009, 02:01 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