How do I make certain fields come up based on criteria?

[Guest]

Ok, please keep in mind that I am a beginner...so please make any responses
very elementary in nature...THANKS!

I have a table that is relaing information about why a child couldn't be
placed (Foster Care). I have a Field as a Text Box with Certain prescribed
reasons that are a drop down box.

Here is my question.

How can I have another table OR field appear on the form (that I want to
create for inputting data) once they select a certain Reason (there are
sub-reasons that go under each reason).

Ex: Reason1 has a sub list of reasons (A, B, C) that should appear BUT if
Reason2 is selected I want a sub list of reasons (1, 2, 3) to appear.

ANY help is much appreciated!!!!

 

[John W. Vinson]

Ok, please keep in mind that I am a beginner...so please make any responses
very elementary in nature...THANKS!

I have a table that is relaing information about why a child couldn't be
placed (Foster Care). I have a Field as a Text Box with Certain prescribed
reasons that are a drop down box.

Here is my question.

How can I have another table OR field appear on the form (that I want to
create for inputting data) once they select a certain Reason (there are
sub-reasons that go under each reason).

Ex: Reason1 has a sub list of reasons (A, B, C) that should appear BUT if
Reason2 is selected I want a sub list of reasons (1, 2, 3) to appear.

ANY help is much appreciated!!!!

I'll make some guesses here: correct me if they're wrong. You have a Table
with two fields named PrimaryReason and SecondaryReason along with other
information about the placement; and you have a different table named Reasons,
with records like:

Primary Secondary
Reason1 A
Reason1 B
Reason1 C
Reason2 1
Reason2 2
Reason2 3

to provide a crossreference giving the valid Secondary reasons for any Primary
reason. OK???

Do all of your data entry with a Form. A Table datasheet - even (or I'd say
especially!!) with Lookup Fields - will *not* be flexible enough to do what
you ask. Let's say your form is named MyForm, and you have two Combo Boxes
named cboPrimary and cboSecondary, bound to the primary and secondary reasons.

Create a Query based on Reasons; select *only* the Primary field; view the
query's Properties and set the "Unique Values" property to True. Sort by
Primary. Use this Query as the Rowsource of cboPrimary - this will give you a
list
Reason1
Reason2
Reason3

Create a second Query also based on Reasons. Select both fields. On the
Criteria line under Primary put

=[Forms]![MyForm]![cboPrimary]

Sort by Secondary; uncheck the "Show" checkbox under Primary. This will give
you a list of the valid Secondary reasons for a given Primary reason. Use this
query as the RowSource for cboSecondary.

Just one other thing - view the Properties of cboPrimary; find the After
Update event on the Events tab; click the ... icon and choose "Code Builder".
Edit the code to

Private Sub cboPrimary_AfterUpdate() <<< Access gives you this line
Me.cboSecondary.Requery
End Sub <<< and this one

Hope this helps!

John W. Vinson [MVP]