Solved Display diff columns based on option group (1 Viewer)

Kayleigh

Member
Local time
Today, 09:35
Joined
Sep 24, 2020
Messages
706
Hi I have a form which filters data displayed based on radio button selected in option group.
How do I display different columns (it is datasheet view) based on option selected?
 

Ranman256

Well-known member
Local time
Today, 05:35
Joined
Apr 9, 2015
Messages
4,339
Code:
Select case fraVal
  Case 0 
      Docmd.openquery "qsQry0"
Case 1
      Docmd.openQuery "qsQry1"
End select

Open the query depending on the option value (in the frame)
Each query shows different columns.
In the form assign it to the form source.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:35
Joined
May 21, 2018
Messages
8,463
How do I display different columns (it is datasheet view) based on option selected?
To show or hide different columns of a datasheet you use the undocumented property "columnHidden"

Code:
Public Sub hideColumns()
  Dim lst As Access.ListBox
  Dim frm As Access.Form
  Dim itm As Variant
  Dim I As Integer
  Set frm = Me.subFrmQueries.Form
  Set lst = Me.lstChoose
  Call showColumns
  For Each itm In lst.ItemsSelected
    frm.Controls(lst.ItemData(itm)).ColumnHidden = -1
  Next itm
End Sub
Public Sub showColumns()
  Dim lst As Access.ListBox
  Dim frm As Access.Form
  Dim itm As Variant
  Dim I As Integer
  Set frm = Me.subFrmQueries.Form
  Set lst = Me.lstChoose
  For I = 0 To lst.ListCount - 1
    frm.Controls(lst.ItemData(I)).ColumnHidden = 0
  Next I
End Sub

See demo. I use a multiselect not a radio button. I show also how to change column order
 

Attachments

  • DataSheetUtilities.accdb
    984 KB · Views: 243

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 09:35
Joined
Sep 12, 2006
Messages
15,614
re @MajP hiding columns. I believe that with an .de database, the design change doesn't persist, as the form design change can't be saved. I think this affects users changing column width's as well. Please let me know if this isn't correct.
 
Last edited:

Dreamweaver

Well-known member
Local time
Today, 09:35
Joined
Nov 28, 2005
Messages
2,466
Hi @gemma-the-husky I have a form see below that I change columns in which is under test as an ACCDE you are correct the changes do not persist after the objects been closed.

Edit: I'll check to see how I close it



 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:35
Joined
May 21, 2018
Messages
8,463
re @MajP hiding columns. I believe that with an .de database, the design change doesn't persist as the form design change can't be saved. I think this affects users changing column width's as well. Please let me know if this isn't correct.
I would normally persist this data to a table (selected columns and column widths, frozen columns) so it is not an issue.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:35
Joined
May 21, 2018
Messages
8,463
If you are asking me, I provide an example in the database. I believe I may have also shown how to set fixed sizes.
 

Kayleigh

Member
Local time
Today, 09:35
Joined
Sep 24, 2020
Messages
706
Thanks @MajP . The freeze columns works beautifully and persists.
Can you suggest how I can adapt the hideColumns to be utilised as backend code, where I have two or three radio buttons on a form which modifies the datasource, which means that depending on query there will be different columns displayed.
Maybe your comboBox used for query selection would work?
I have enclosed one example where I would like to utilise hideColumns (three queries for the three radio button options) although I do have several other forms I'm working on. (Hope to post another DB on other thread tmrw...)
 

Attachments

  • TrainingTest2.accdb
    1.1 MB · Views: 119

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:35
Joined
May 21, 2018
Messages
8,463
The very simple way to do this would be to make these subforms. This is how I would do it, but I cannot figure out how to fix it without recreating the main form. I had no idea this was a split form disaster. I could strangle the Microsoft person who thought that was a good idea. Anyways I would do an unbound main form and just change the source object of the subforms. It changes, but some reason they become messed up.


The idea is correct, but since this started as a split form it is screwing things up. If you like the idea just build a new main form.
 

Attachments

  • TrainingTest2.accdb
    1.5 MB · Views: 219

Kayleigh

Member
Local time
Today, 09:35
Joined
Sep 24, 2020
Messages
706
Your idea is good. So are you suggesting to scrap the split form idea and instead the radio buttons displays a different subform when selected?
This would mean hideColumns is unnecessary as each subform can select required columns in data source.

Another similar issue - I have form which is based on query but depending on filter, different data is displayed. However the field names are not the same. Either I can use a union query to join the fields into one. Or do similar to what you have done above. Or perhaps hide columns. Can you suggest what is best approach?
Also having a lot of trouble getting the pop-out Covid Consent form to work. It should display the selected record with all relevant details. Here again different fields would be displayed depending on if student/staff is selected. How would I successfully include these fields?
 

Attachments

  • CovidTest1.accdb
    1.1 MB · Views: 215

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:35
Joined
May 21, 2018
Messages
8,463
I personally will not look at a split form or try to help someone with it. It is a waste of time IMO, but others may like it. I will not wrestle with it to make a bad idea work. If you want a split form then hopefully someone else provides help.
The subform idea would be easier IMO than hard wiring code to hide columns.

You can do a Union query and you alias field names. So you can do that approach as well, One or the other.
I can do a union looks like this (plus other fields)
qryParents qryParents

fldParentIDParentRelationParentTitleParentFirstNameParentLastNamefldHomePhoneParentMobilePhoneParentWorkPhonefldEmailfldEmail2
93​
FatherMrMichaelShell123-456-7890456-789-1234654-789-1234something@some.netsome@some.com
93​
MotherMrsDebbieShell123-456-7890678-901-2345876-901-2345something@some.netsome@some.com

Either way is relatively easy. Use different Subforms and change the source object, or a union query and filter on ParentRelation. Or since I did qryFather and qryMother both of those queries have the same field names. So you could change the recordsource from qryfather to qrymother.
 

Attachments

  • CovidTest_Union.accdb
    1.8 MB · Views: 220

Kayleigh

Member
Local time
Today, 09:35
Joined
Sep 24, 2020
Messages
706
Okay that works well for parents. And will try having diff subforms for the staff/student option as there are some very different fields in each query.

Can we go back to the original sample we looked at - I tried the subform suggestion but could not get the data to display correctly. Also it is not presented very neatly?

Thanks
 

Attachments

  • TrainingTest3.accdb
    1.8 MB · Views: 225

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:35
Joined
May 21, 2018
Messages
8,463
Like I said. The main form is FU because of being based on split form. Just make a simple main form for it to work. If you want nice formatting then you can make the subforms Tabular forms instead of datasheets. Make sure to look at my form. It is not the default form.
 

Attachments

  • TrainingTest4.accdb
    892 KB · Views: 173

Kayleigh

Member
Local time
Today, 09:35
Joined
Sep 24, 2020
Messages
706
Thanks - I misunderstood your idea of subform so added nested form which wasn't necessary. Your method is simple enough :)
Could probably do as a continuous form for effective formatting...

As a side point - would you always advise using this method which would mean adding more queries and subforms to database. Is there any downsides to having a database chockful with objects?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:35
Joined
May 21, 2018
Messages
8,463
As a side point - would you always advise using this method which would mean adding more queries and subforms to database. Is there any downsides to having a database chockful with objects
It depends. There are different ways to do things. If these all had the same columns then I would have one subform and change its recordsource. I could have wrote code to show and hide certain columns and still have only one subform, but that may not be as easy for most people. Much easier to design a subform. Having more forms and reports and queries is not a problem if you have a very good naming convention so you can manage them. Also the navigation pane has features to manage such as grouping objects and hiding objects.

I write a lot of code, so my databases have very few duplicated forms or reports. They are modified in code. That may not be easy for most people to do and they will have more forms and reports. An example I may have 1 report but will have a form that allows me to sort and filter it any way I want. I may hide or show certain fields based on the filter. Not everyone is comfortable doing this.
 

Kayleigh

Member
Local time
Today, 09:35
Joined
Sep 24, 2020
Messages
706
I understand. And probably this means that your queries are written once across the database so more consistent.
I really liked the Utilities database you shared a while back - where the columns etc can be selected by user. However I don't want to give too much control to the client so prefer to code this as much as possible.
 

Users who are viewing this thread

Top Bottom