Filter in form using subform.... one more time...

[Taiano]

Hello!!
I am building a database of questionnaires!
I have one main table called PERSON linked to 27 huge tables using a one to
many relationship to each of the 27.
Each of the 27 huge tables have a unique form and this form has list boxes,
combo box, check box, option box, etc etc...
My main form is called PERSONFORM (associated to table PERSON) and the other
27 forms are scaterred around in a tab folder that is in PERSONFORM.
There are around 300 options in all the 27 forms.
I want to check a single option box (for example, "People who drink water")
in one of the forms that are scattered in the tab folder and filter all the
people "who drink water"
I have seen code that explains this, but do I have to write it "inner
joining" 27 tables with around 300 attributes in all 27 tables or can I use
the wonderful FILTER BY FORM with a few lines of extra code?
I am using access because the company affords so....
I am lacking words to express my desperate state, but can anyone answer me??
Thanks in advance!

 

[ManningFan]

I would suggest a menu option only certain people can see, and by
choosing the option you can have access to an "Admin Panel" where you
can write queries like this.

For instance, you can have a combo box with all possible answers tied
to a query, and if you choose "drinks water" from the combo it checks
the "Drinks Water" field in your table and pulls back all the people
who chose "Yes". This also gives you the freedom to query "on the fly"
pretty easily, and depending on your skillset you can do practically
anything.

 

[Taiano]

Hello again!!
Sorry for my late reply and thank you so much for replying!! It felt really
good to have someone trying to help me!!
The menu option is a very good idea thank you! i will implement it only for
administration to see! As for the problem, It wasn´t quite the thing I
needed, but I seemed to solve it! Here is how I did it, maybe there is a
better solution...

The "persons" table is linked to one of the 27 tables which is called
"HOUSEANDPROPERTIES". There is a form "PERSONS" and a subform
"HOUSEANDPROPERTIES" (and of course the other 26 forms referencing the other
26 tables). The form "HOUSEANDPROPERTIES" has 39 option boxes. Here is what I
did: (for the 3 options, I still have to do this for the remaining 36)


Private Sub Comando490_Click()

Dim varrent As Boolean
Dim varownhome As Boolean
Dim varshack As Boolean

varrent = False
varownhome = False
varshack = False

' rent, ownhome and shack are names given to the option boxes
' they have NO control source
If Forms!questionnaire!houseproperties!rent = True Then
varrent = True
End If
If Forms!questionnaire!houseproperties!ownhome = True Then
varownhome = True
End If
If Forms!questionnaire!houseproperties!shack = True Then
varshack = True
End If

varsql = "SELECT DISTINCTROW persons.* FROM persons INNER JOIN
Tablehouseproperties ON persons.cod_person = Tablehouseproperties.cod_person
WHERE (((Tablehouseproperties.rent)=" & varrent & ") AND
((Tablehouseproperties.ownhome)=" & varownhome & ")" & _
"AND ((Tablehouseproperties.shack)=" & varshack & "))"

Me.RecordSource = varsql

End Sub

So basically, I duplicated the form, removed every contro source from every
option box and applied the sql code and the ME.RECORDSOURCE = VARSQL

Once again thnks for the inspiration and kind attention!!!

Pedro