How to get certain fields visible depending on other values in access form

[Amian]

Hello,

I'm struggling with figuring out how to have my form auto select certain fields in access 2010.

I only want fields to show up on a form if certain data was entered in a field in a diff form.

Ex, If Form 1 had a selection of fruits (Apples, Oranges, and Blueberries

then I want form 2 to have the field Apples there and all possible dessert selections for Apples , (apple pie, apple crisp, etc.).

but if blueberries is selected in form 1, I dont want form 2 to show possible apple desserts, I want it to show only possible blueberry desserts.


I hope this makes sense. My actual database is for patient testing and patient results. Since there are so many possibilities for both, I don't want all possible results in the form, only possible results based on testing selected in form 1, and I want to be able to refine the possibilities even further when certain result options are selected. But I'm not sure where to start on this one.

Thanks!

[deanamiles86]

I have done somthing very similar

using your scenario i created two tables the 1st had the fruit and the second had the fruit again in the 1st columns then a dessert in the second.

i then created a form with a combo box that looked at my fruit table

i then created a query that looked at my dessert table and added criteria that looked at my form = [Forms]![Fruit Form]![Combo2]

i then created a second form with the query as my data source

any problems i can send you a copy of the database i created

Regards

[Amian]

Maybe some more information might help.

Form 1 is frmPatientTesti ng

Form 2 is a copy of form 1 with an embedded subform - Form 2 is called frmPatientResul ts

I created form 2 a copy of form 1 because I wanted all the demographics for the patient to be visible and read only so I disabled all the fields and it works so that I see all the patient fname, lname, dob etc.

Now in form 2 since it's a copy, it sees all the possible testing options and those that were selected in form 1, all i want to see in form 2 are the selected options from form 1, not any unselected ones.

I created a a query that looked at my tblPatientTesti ng and the criteria was from form 1 and made that the data source for form 2. That didnt actually refine my data in form 2, I still see everything from form 1. Did I do something wrong?

[zmbd]

Amian
are you using VB or VBA to do this with?

[deanamiles86]

not 100% sure what you mean, i dont think this is a VBA or VB issue.

if you can send me a copy of the database with fictional data i can have a look

E-mail me at [z{Modedit - email redacted}]

[zmbd]

Amian:
You do not need to send nor attach your database in anyway what so ever.

What you need to do is answer my question!
Are you working in the Native Access application and using the form designer and VBA editor
OR
Are you working in VB or VB.Net?

If you are using the Native Access editors, then:

1. this is the wrong forum
2. Your question can be solved in at least three very simple ways
   2a) Leave the 1st form open, hide it, and refer to the values either directly from the second form or within a parmeter query
   2b) Pass the values from the first form to the second using the DoCMD method
   2c) Use a parent/subform configuration.
   2d) Use a single form with combobox that sets the filters for the form or for the rowsource
3. We may need to look at how well your database is normalized I refer you to the following: > Database Normalization and Table Structures. Having a very good understanding of this concept is VITAL to making your life easier when you start data-mining your information. Both forms and queries will almost write themselves (well, maybe not that easy; however, you should get my point).

[deanamiles86]

sorry zmbd im new to this forum and the rules are different to what im used to

as for the question, i also agree with zmbd response

lesson well and truly learned

[Amian]

Hi zmbd,

I'm using VBA in MS Access.

Thanks,