Subform Help

[Kiernan]

Hey all...I need some serious help, as I don't have a clue how to get the results I'm looking for here.

I have a table that has a text field and 12 yes/no fields. That text can fall into any of the 12 categories, and they select what they want it to apply to when entering.

I want a combo box that shows all 12 options. When they select one of the 12 options, I want a subform to open that shows only the text fields that fall into that particular category so they can scroll through them.

Please help!

 

[GingerR]

hmmmm...first, does each text thingy only fall into ONE of the 12 categories, or can a text thingy apply to ONE OR MORE of the 12 categories?

 

[Kiernan]

It can apply to any number of the 12 categories.

I have queries set up for each 12 categories that will filter based on they yes/no. I guess what I need is for each combo box selection to run the appropriate query, and for the query to list in the subform. Does that make more sense?

 

[GingerR]

what if someone wants to choose more than one of the twelve? will they get results back on matches for that? or are you letting them choose only one?

what if instead you had 12 check boxes then ran a query?

otherwise, if you want to stick with the combo box idea: what is the rowsource of the combo box? it must be a table that has 12 rows of data, each the same as a field name of one of the 12 check boxes?

 

[GingerR]

and what are the names of your queries? are the names of the queries themselves related in any way to the check box field names?

can you paste one of the queries here? perhaps you don't need 12 queries when we're done....

 

[GingerR]

ok i have to go soon, so i'll tell you what i've come up with:

main table with 12 check boxes
a second table with 12 rows, each is the same as a check box field name

i have a main form with no recordsource
on the form, i have a combo box which uses the second table as the rowsource
also on the form is a subform, datasheet view, of the main table.

in the combo box's On Change event, i put this:

Private Sub cboChoice_Change()
    Dim strFilter
    strFilter = "[" & Me.cboChoice & "] = True"
    
    Me.subFormName.Form.Filter = strFilter
    Me.subFormName.Form.FilterOn = True
    
End Sub

substitute cboChoice with your combo box name.
substitute subFormName with the name of your subform.

1. this assumes combo box has one column.
2. if there are more columns and the one with the word in it that we are using is not in the first column, use Me.cboChoice.column(1) use 1 if it's in the second column, 2 if it's in the third column, etc.

this just puts a filter on the subform of the WORD in the combo box that is the same as the check box name. ok?

 

[Kiernan]

Wow, that was pretty cool. Not really what I'm trying to do, but something I'll file for later use, I'm sure.

Let's see if I can clear this up a bit more. Here's a rough abstract of my form:

TEXT 1 2 3 4 5 6 7 8 9 10 11 12
________ Y/N Y/N Y/N Y/N Y/N Y/N Y/N Y/N Y/N Y/N Y/N Y/N

The above is simply for entering new information.

In the other section of my form, I want a combobox with all options 1-12 listed in it. Then a subform that will list all items that are in the category selected by the combobox. So, if someone picks 10 in the combobox, all items with 10 as a Yes will display. If one TEXT item falls into category 2 and 5, it will show in the subform for either when they are selected from the combobox.

Really, section 1 and 2 of the form have nothing to do with each other. One is for input, the other is for user controlled display. They're just both on the same form to save time.

 

[Kiernan]

I worked with what you gave me, and it's still not performing quite the way I want. At least I'm not getting an error now, which is a bonus.

If I select category 1 from my combobox, I get all the items that are in the table, whether they're true for that category or not. If I pick any other category from the combobox, I get no items listed, even though there are records with that value being true.

I also tried to set up what you gave me on a blank form, just to see if I could get it, and it gives me a "Enter Parameter" window doing it that way.

I really appreciate your help, and thanks for taking the time to try to point me in the right direction. Times like this I wish I were a programmer and not a hardware guy.

 

[Kiernan]

Does anyone have any ideas on this one? I'd be more than happy to zip and send the file if it would help.

 

[GingerR]

rowe147@hotmail.com