Solved New To Access and VBA; Stupid Question (1 Viewer)

[lbenjaminl]

I'm new, but cannot find an answer in the documentation.

I have a controling subform (DisplayOptions). It has 6 checkboxes that control which columns to display throughout the application in addition to the three default columns. What I want to have happen is that when a checkbox is checked or unckecked (the table "My Profile" is updated (this is happening) and the columns in the other two subforms are requeried and Formatted.

I have a subform (NutritionalGoals). It has a RecordSource of (SELECT [My Profile].ID, [My Profile].CalorieGoal, [My Profile].CarbGoal, [My Profile].ProteinGoal, [My Profile].FatGoal, [My Profile].SodiumGoal, [My Profile].PotassiumGoal, [My Profile].PhosphorusGoal, [My Profile].PrefFat, [My Profile].PrefSodium, [My Profile].PrefPotassium, [My Profile].PrefPhosphorous, [My Profile].PrefCalcium, [My Profile].PrefSugars FROM [My Profile] WHERE ((([My Profile].ID)=[TempVars]![sysProfileID])); The columns in the subform are dynamically formatted based on the checked boxes in the DisplayOptions subform

I have a very complex (FoodDiary) subform. It contains a tab control. the first tab will be modified so that allong with the required fields, the last four fields will be dynamically built and totalled based on the checkbox changes in DisplayOptions.

Believe it or not, I have the dynamic building working.

What I can't seem to get working is the Requery of the two subforms when the checkboxes in DisplayOptions change.

Go figure!

 

[CJ_London]

You say 6 columns but your query has 14

but the usual way is simply subformcontrolname.requery

this assumes your checkboxes are on the main form

 

[plog]

...[My Profile].CalorieGoal, [My Profile].CarbGoal, [My Profile].ProteinGoal, [My Profile].FatGoal, [My Profile].SodiumGoal, [My Profile].PotassiumGoal, [My Profile].PhosphorusGoal...

Good news, you can put aside the issue you posted about. Bad news--you've got a huge table issue you need to address and its going to effect everything else (Forms, reports, queries).

Field names should be generic and specific values should be stored in the table itself not in table nor field names. All those words before 'Goal' in the field above should be values in a field, not names of fields. You need an entirely new table to hold all your goal data. It should be structured like this:

Goals
goal_ID, autonumber, primary key
ID_Profile, number, foreign key back to [My Profile] table
goal_Type, text, this will hold all those values currently in the field names (Fat, Sodium, Carb, etc.)
goal_Value, ?, this will hold the value currently in all those fields.

That's it, those 4 fields can now accomodate all your goal data--and if you decide you want to add more goals, you don't have to do a thing to the table to accomodate them--just add a new record with the new goal_Type and your database can handle it.

You need to do the same thing for all those fields prefixed with 'Pref' as well. No more fields for each type, you make a new table to accomodate them.

I fear you may have made other mistakes because I gleaned all that from just your query. If you could complete the Relationship Tool for your database, expand all the tables to show their fields then post a screen shot back here we can ensure you set up your tables correctly.