Does this require VBA? Experts, please help...

[floridaboy2004]

Hello all,

I have a question that I absolutely CANNOT solve...I believe that Cbrine touched on some of it before (thanks!), but I still cannot figure out how to do this:

If a customer has one of the following hobby preferences assigned #'s of 2, 7, 15, 22, 35, or 50 selected for arbitrary question # 2 (as an open box), then how could I 'black out' question #'s 31 through 33 (e.g. NOT allow an answer to be entered for #'s 31-33) on the form?

Also, if I fill in a customer data form and put "N" (for No) for question #5, then how could I 'black out' the REST of the form (e.g. question #'s 6-35)?

Also, if I fill in a customer data form and put "N" (for No) or "NA" (for No Answer) for question #8, then how could I 'black out' question # 21?

Could someone explain how I could select from my customer population BY AGE (at date of purchase) when the only 2 fields that I have on my form are DATE OF BIRTH (DOB) and DATE OF PURCHASE (DOP)? For example, if the customer was <40 years old on the date of purchase, I would like to skip question # 24.

The more specific (e.g. menus, fields, etc. to use) you can be, the better off I will be.

Thank-you again for your help, kind souls...I REALLY appreciate everyone's help as I learn how to do this!

Many thanks! -FB2004

 

[mdmilner]

Wish I had time to be more specific, but, talking very generally Filtering will help you with this.

Create a yes/no (boolean) field in the table underlying the form.
In the field (textbox on the form) you're using to receive the answer...add some VBA code to the after_update event that:

Display the fields using a query that filters out whenever your boolean field is false.

1) grabs the question #
2) reads the answer
3) opens the table, changes the field values in questions you wish to display to No/False
4) requery the table so it grabs the info again (and the now changed values)

This is probably not the only way to do this, but it would work. This is at least an intermediate task.

Mike

 

[floridaboy2004]

Thanks for the reply...do you think that you could give me an example using one of the instances that I mentioned in my above post? I am slowly learning VBA and I think that if someone could supply me with an example, I could modify it to meet my needs. Thanks again.

 

[Norie]

Could you explain what you already have?

Are you using forms?

 

[floridaboy2004]

I have a form based on a several tables:

Customer Type
Brands of Candy
Dietary habits (e.g. no sugar, vegetarian, meat only, etc.)

The form has ~30 questions, including NAME, DATE OF PURCHASE, DOB, as well as open boxes including the above topics.

I would like to, for example, make question #'s 7-10 'black out' if, somehow using the DATE of PURCHASE and DOB fields, the customer is determined to be 40 years old or younger, since questions #'s 7-10 pertain to hair-loss products and I want to focus on the over 40 crowd.

Also, if they select 'DIABETIC' as their Dietary Habit in the open box for question #10, then the data fields/open boxes,/etc. on question #'s 16-20 of the form will 'black out' since question #'s 16-20 pertain to 'sugary sweet candy brands' as an open box list.

Is there also a way to create a 'POP-UP' window to say 'This customer is under 40 years old...skip question #'s 7-10'? I am not sure if this would help as a warning BEFORE 'freezing out' the remaining questions/boxes/etc.

PLEASE HELP ME...my manager really needs me to complete this by tomorrow (?) and I am really confused...thanks a million, everyone!

 

[mdmilner]

Not a specific example, but a how-to but it starts all the way back with database design (relationships)

Start with three tables.
1st would be a set or records that have info about the individual and a unique indexed field that is used to build a relationship to the 2nd table.

Second has unique field identifying the unique individual AND a numeric value identifying the hobby preferences. Person #1 would have 6 records (rows) with the values 2, 7, 15, 22, 35, 50 (ones you used above) on them.

Third table would contain your questions - that uses as a unique key value the question numbers (#1 - #50 at least). Set a relationship between the 2nd table and the first (many-to-one)

Build a form. Create a Combo or List box that extracts field values from the first table (use the names possibly).

Build a subform - it will be inserted into the first form. As a recordsource, use the 2nd table. In the main form (looking at it in design mode) set the subform's visible property to False. (hides it until you're ready)

In the combo-boxes after_update event, grab the value (me.cboBoxName.Value) and use it to change the recordsource of the subform. Requery the subform AND set the subform to true.
The query will need to do a join on the 2nd & 3rd tables...idea is to use the 2nd table to determine which questions will appear but the question displayed is actually the third.

Overall, this approach is a little different from yours. It doesn't hide the unavailable questions, it only shows the questions that match this particular user.

Here is an example of how to set the Filter properties of a subform.
Me refers to the current form...sfmTask is the name of the subform. .Filter is the specific property you are altering.

http://www.mvps.org/access/forms/frm0031.htm

Me.sfmTask.Form.Filter = "[task_name]='" & Me.cboSelTask.Value & "'"
Me.sfmTask.Form.FilterOn = True
Me.sfmTask.Form.Visible = True

Mike

-Not sure how much more I can follow this thread today. Ran out of time.