How to control which colums are displayed as output by using query?

[VBnewb13]

The question is a bit more complicated than it sounds I think. What I am trying to accomplish I will explain using an example:

I have a subform based on a query. This query is a data table that contains 5 columns. I have a textbox into which I input data, hit a command button, and it searches the query and displays matching items in the subform. However, it shows all 5 columns... What I would ideally like to do is have a checkbox that correspnds to each column and, when checked, that column is displayed in the results. So, if I have checkbox1 and checkbox2 selected, column1 and column2 are the only columns displayed as results in the subform.

If anyone knows how to pull this off that would be awesome. Additionally, if the question is unclear just let me know - it might be hard to conceptualize.

Thanks :)

[NeoPa]

Is the intention also to limit the searching to just those fields selected too?

As far as limiting the display is concerned the bound controls must be there for all fields. However, it is perfectly possible to hide the controls of your choice depending on the values of the CheckBoxes. Does this sound like what you're after?

An explanation for why you need this, may help conceptualisation.

[VBnewb13]

@NeoPa
I need this because some tables I want to search through contain 20+ columns, most of which are irrelevant. I want the user to be able to select which columns they want to appear so they may be able to view more relevant information instead of scrolling through everything.

I am interested in how to hide the controls based on checkboxes. Does this mean I will be able to hide what columns appear?

Thanks again

[NeoPa]

Hiding yes. That's relatively straightforward. Every control has a .Visible property. What is more intriguing is what you want done with the gaps. Obviously the design of the form or report is mainly done manually, but you can resize and reposition controls with your code too if you feel that would be worth the effort.

[VBnewb13]

The subform is based on a query data table. Is it possible to choose which columns on the table appear in the subform itself?

[NeoPa]

Yes. That is possible. Unfortunately, I have no idea what you mean by a query data table.

This is possible both when designing the form manually and from within code. Which are you interested in? Presumably the latter.

[Jim Doherty]

@VBnewb13
I have had a play this afternoon, not as a solution, rather merely to give you an idea of what can be done when looked at from different perspectives. The logic of this it seems to me is to merely have the ability to view only the columns in datasheet view that you are interested in based on a selection from a multi selection capable list.

The following paste coded represents that contained in the attached zip file.

What the code does is based on the northwind employees table.
1) A multi select listbox is populated with the field names of employees.
2) You choose which fieldnames you are interested in then click a button.

The code generates a new form based on copying an empty template. Textboxes are generated in code, the form saved and then displayed via the subform.

This happens each time you click the button.

The creation of the new form is necessary in this instance because creating textboxes in code on any form only has a certain shelf life ie 754 maximum count over the life of the form. By creating a new form the count resets to zero.

The code below is not as tidy as it could be (ie: error handling it either works or it does not) but it does give you a flavour of the type of thing you can do given you wanted to display the data in a datasheet gridview via a form. It is only an idea!! This code does NOT address all eventualities for instance it is only creating textboxes. It is not looking at field types to determine which control type to create and so on. It will not handle OLE or Binary data. Like I say..... it is merely a flavour

I am sure you can read into this the searchability aspect of your table given it is only an SQL statement you could fit it in somewhere to suit your needs I,m sure

Private Sub cmdProcess_Click()
    On Error Resume Next    '<<< No error handling invoked it either works or it doesnt
    Dim ctl As Control
    Dim intDataX As Integer, intDataY As Integer, iinti As Integer, intj As Integer
    'position the controls on the form
    intX = 1000
    intY = 100
    If Me!lstEmployees.ItemsSelected.Count = 0 Then
        MsgBox "Select at least one item from the list first", vbExclamation, "System Message"
        Me!lstEmployees.SetFocus
        Exit Sub
    End If
    'adjust the subform to hook to the placeholder then open the newly created form in degin
    ' and set its recordsource accordingly
    Me!Submain.SourceObject = "fsubPlaceHolder"
    'delete pre- existing form
    DoCmd.DeleteObject acForm, "fsubEmployees"
    'copy the template to a new form
    DoCmd.CopyObject "", "fsubEmployees", acForm, "fsubEmployeesEmptyTemplate"
    'and open in design
    DoCmd.OpenForm "fsubEmployees", acDesign, "", "", , acHidden
    Forms!fsubEmployees.RecordSource = "SELECT * FROM Employees"
    'loop through the listbox and create a textbox for each field selected in the list
    For Each varItem In Me!lstEmployees.ItemsSelected
        Set ctl = CreateControl("fsubEmployees", acTextBox, , acDetail, lstEmployees.ItemData(varItem), _
                                intX, intY)
        ctl.Name = lstEmployees.ItemData(varItem)
    Next varItem
    'close the form and save
    DoCmd.Close acForm, "fsubEmployees", acSaveYes
    'now adjust the subform to pick up on the newly created form having the fields for display
    Me!Submain.SourceObject = "fsubEmployees"
End Sub
 
Private Sub Reset(lst As Access.ListBox)
    On Error Resume Next
    Dim i As Integer
    With lst
        For i = (.ItemsSelected.Count - 1) To 0 Step -1
            .Selected(.ItemsSelected(i)) = False
        Next i
    End With
End Sub
 
Private Sub cmdReset_Click()
    On Error Resume Next
    Call Reset(Me!lstEmployees)
    Me!Submain.SourceObject = "fsubPlaceholder"
End Sub

[VBnewb13]

@Jim Doherty
That worked out great once I renamed what I needed to! That is exactly what I was trying to accomplish

[Jim Doherty]

@VBnewb13
Glad it helped you :)

Like I say with development you could enhance it by coding into the routines things like fixing up a neater column headers by created labels in addition to screen textbox controls and also checking the datatypes of fields so that the control type created aligns with the datatype (ie: a checkbox for a Yes/No column instead of textboxes) and so forth. I have coded this into my copy since my last post, but given you are now happy that the initial version services your needs I will leave it at that.

Regards

[VBnewb13]

@Jim Doherty
Hey Jim,

I thought I would pick your brain since you were clever enough to develop the previous code. I had a question about what you suggested...

Forms!fsubEmployees.RecordSource = "SELECT * FROM Employees"

I inserted a text box (named searchALL) to act as an 'all encompassing search' of the displayed data. I tried to be more specific with this line by saying

Forms!fsubEmployees.RecordSource = "SELECT * FROM Employees WHERE FIELD1 Like '*" & [Forms]![frmDataSelect]![SearchALL] & "*'" & "or FIELD2 Like '*" & [Forms]![frmDataSelect]![SearchALL] & "*'"

...etc etc

The problem with this is as follows: Say I have FIELD1 selected as the only column in the display and I search for the word "dog." Dog is in FIELD2 and, even though I don't have FIELD2 selected, the cell in the corresponding row in FIELD1 will show up.

Is there any way to make this searchbox so it only sorts through visible cells?

Thanks again

[Jim Doherty]

@VBnewb13
This seems to me to be the chicken/egg scenario. The form generation and accompanying controls created on the fly are based on your fields selections from a list. The logic goes no further than that currently.

Obviously whichever fields you select in that list currently will be displayed exactly in accordance with that and logically field1 WILL indeed be displayed 'irrespective' of the fact that field2 is the only column that contains the word 'DOG' if you have only field1 selected for display and not field2. The computer is in effect doing what it is told.

The process sequence here it seems to me is this.... in reality you are saying in data terms 'You have no idea WHICH column contains the word 'DOG' but whichever column it is you want THAT column to display and not irrelevant columns' ........(if I am reading you right)

If this analogy is correct then in any 'real sense' your thought processes should be fashioned to say this to yourself in the early design stages...'I need to determine where my data lies and once i have identified this only THEN I will go about generating the coding and display formats/workarounds whichever the case may be to make this display happen'.

From a practical, aesthetically pleasing visual display standpoint, if I were looking at an available list of fields contained in a listbox and my intention was to search ANY ONE of those columns for the word 'DOG' and using a generic unbound textbox mounted on a form and intending it to be a searchbox I would be wanting to do some 'pre-processing' before displaying anything.

In other words... If I entered something into that textbox and hit the enter key I would probably want to make the system search relevant columns according to the datatype ie 'DOG?' is text so ignore photo columns and datetime columns and only go for text columns that kind of thing.

With this logic in mind I would certainly consider 'looping' the data and returning a numeric count of which pertinent columns 'potentially' contain data relevant to a furry little animal that barks! and 'present' this as a numeric count PER FIELD listed as a second column adjacent in that listbox.

THIS then gives you the overall general idea of WHICH columns you should be selecting from that list before any display takes place.

With this type of design logic in place, you might even consider making the computer loop the listbox picking out those numeric values that have a 'data' count greater than zero and making the computer 'multi select' them itself. ONLY THEN would you hit a display button to present those columns accordinging to that auto selection.

Obviously this is a whole new raft of different coding technique that would need to be applied to your system carrying a good degree of sophistication (I accept you would only seek to employ this type of thing once you fully understood the concepts of how you might go about 'actually' implementing it technically)

It is most probably the way I would seek to attack it personally it if it were mine. I don't believe a straight SQL solution is particularly flexible nor friendly enough its own. That is why we have alternatives such as arrays and recordsets and a plethora of other data manipulation techniques.

Regards

[VBnewb13]

@Jim Doherty
Yea, you're right, that makes perfect sense. I've given it some thought and I feel that I am goign to forgo that capability. It really does not affect what I am trying to do in such a way that it needs to be given attention.

I greatly appreciate your detailed answers. They were very helpful :)

Best

[Jim Doherty]

@VBnewb13
You're very welcome :)