dropdowns and listboxes

[t_dot]

I have a form with a drop down menu that you can select an item and a query is run to find the information on that item from a form.... pretty basic.

However, this brings back every piece of information about that item and I am trying to let the user choose which pieces of information they want to come back instead of everything. So a user would pick an item from a drop down, then select what information it wants about it and then the query would be run with only certain things returning to the user.

(ex. if they chose "apple" from a drop down, they could then select from a listBox: "colour", "size", and "type" and wouldn't have to get other information from columns in the table such as "date picked","farm location", etc.)

I realize that this could be done with a query for each combination that the user could choose, however this is quite clumsy since the only way that I know how to do that would be with a pushbutton for each query.

I hope that this makes sense to read, please tell me if it doesn't. Any help is greatly appreciated... even if you just say that this is impossible. Anyways, thanks

[trb5016]

Well it depends on how your table structure is set up a little bit, but without knowing details this is what I would do:

Have a form that has a combo box and listbox and command button.

populating the combo box and list box can be fixed, or coded to be more diverse - it depends on your needs.

For instance, you could have the source of the combo select distinct types of fruit. The list box could be populated using code to show field names.

From there you could use the QueryDef object to change the source of a query based on your combo and listbox.

This would require using code to create an SQL statement that is built using the field choices in the list box.

These is really broad, but if you need more details and can post a sample DB we could get you more help

[t_dot]

Attached is a very simple example of what I am trying to do... haha it's pretty basic, I just made a bunch of stuff up.

Right now, the form button runs the query on whatever item is picked out of the comboBox and displays the information. I would like it to do that but also only provide the columns that the user selects from the listBox. So if they pick "apple" and only want to see the "size" and "colour", they can select those three things and then the list that is provided has only two columns (size & colour).

Also, this does not have to be the design by any means, this is just something that I had come up with. If there is another way that makes more sense or one that is easier to do, I would happily change. I just started using Access for the first time last week so obviously have very little experience and the same goes for coding in VB.

Again, thank you so much for your help.

[trb5016]

Alright, I've added the functionality to your sample (I saved it in Access 2000 format, I dunno what version you're using so I hope you can open it)

Explanation:
The combo box row source is a query that selects one of each fruit from your table. This way you can add different fruits and not have to worry about adding them as a choice to the combo box.

When the form opens code pulls in all the field names from the table and adds them as the source for the list box. This way you can add fields to the table without having to add them to the listbox.

The command button code is commented

Let me know if you have any questions!

[t_dot]

Is there any chance that you can save that in access 97? haha sadly, that's what I'm using...

[trb5016]

Ahh I should have looked at the handy little icon that says what version you're using lol.
I can't convert back any further than 2000 because I only have 2007/10 on hand.

If some nice person on the forum see's this that has 2003 or 2000 they could convert it for you.


Other than that, heres the changes I made, maybe this can get you started.

Change the Rowsource of your combo box to "SELECT DISTINCT FruitType FROM [Fruit Stand]"
And change the RowsourceType to Table/Query

In the forms open event but the following code:

Private Sub Form_Open(Cancel As Integer)

    Dim RS As DAO.Recordset
    Dim f As Field
    Dim strListSource As String
    
    'Opens the Table as a recordset
    Set RS = CurrentDb.OpenRecordset("Fruit Stand")
    
    'Buils the string of the row source
    For Each f In RS.Fields
        strListSource = strListSource & ";" & f.Name
    Next
    
    'removes leading semicolon
    strListSource = Mid(strListSource, 2)
    
    'Sets rowsource to listbox
    List2.RowSource = strListSource
    
    'Updates listbox
    List2.Requery
    
    Set RS = Nothing

End Sub

And in the command buttons On click event put

Private Sub Command5_Click()

    Dim strWHERE, strSelect, SQL As String
    Dim choice As Variant
    
    'Checks to make sure at least one column is chosen
    If List2.ItemsSelected.Count = 0 Then
        MsgBox ("You must select at least one field to view")
        Exit Sub
    End If
    
    'Loop through Listbox items and build select part of SQL string
    For Each choice In List2.ItemsSelected
        strSelect = strSelect & ", [" & List2.ItemData(choice) & "]"
    Next choice
    
    'removes leading comma and space
    strSelect = Mid(strSelect, 3)
    
    'Builds full query definition
    If Combo0.Value & "" = "" Then 'No fruit type was selected, so we want to show all fruits, therefore no where clause
        strWHERE = ""
    Else
        strWHERE = "WHERE [FruitType]='" & Combo0.Value & "'"
    End If
    
    SQL = "SELECT " & strSelect & " FROM [Fruit Stand] " & strWHERE
    
    'Sets the definition of the query
    CurrentDb.QueryDefs("Query1").SQL = SQL
    
    'Opens the query and closes the form
    DoCmd.OpenQuery "Query1"
    DoCmd.Close acForm, "Form1", acSaveYes

Eeeek, I wish you could see it in action haha GL
End Sub

[t_dot]

Oh my goodness, it works!! --- you've saved the day!! Thank you soo much!