Product Configurator forms, Best way to code?

[EvilSewingMachine]

I'm building product configurator forms. There are 6 subforms, each
one dedicated to a product line that have 35 or so options each. I'm
using the visible property to show or hide various controls depending
on the product selected.

Then depending on the those options chosen more controls appear or
become enabled depending on the selections.

I'm using "OnGotFocus" to hide or show controls and then an IIF
Statement in the "OnCurrent" of the form to to check the fields in the
table to display previously entered configurations correctly.

IF Field1 = 1 Then
Control1.Visible = False
Else Control1.Visible = True
End IF

IF Field2 = 1 Then
Control2.Visible = False
Else Control2.Visible = True
End IF

IF Field3 = 1 Then
Control3.Visible = False
Else Control3.Visible = True
End IF

And so on and so forth for all of the options.
Is this the best way to code this? It seems kind of redundant using
the OnGotFocus to switch the visible properties and then using
OnCurrent with all those IF statements to bring back each records
settings. What kind of problems does anyone foresee?
I'm new to VB and Access, your advice is like a lighthouse on the
shore line.

 

[John W. Vinson]

I'm building product configurator forms. There are 6 subforms, each
one dedicated to a product line that have 35 or so options each. I'm
using the visible property to show or hide various controls depending
on the product selected.

Forget about the forms for a moment here: I think you have some serious
rethinking to do on your Tables!

Does each Subform have a different table? And does each table have 35 or so
*fields*, one for each option?

If so, you may want to consider a normalized table structure:

ProductLines
LineID <Primary Key>
<information about the line>

Options
OptionNo
Description

LineOptions
LineID <what line uses these options>
OptionNo <what option pertains to this line>

Products
ProductID
LineID
<more info about this specific instance of the product>

OptionsUsed
ProductID
OptionNo

This structure will let you select a ProductLine, and have a subform for the
options used on that product; you can dynamically change the Rowsource of a
combo box or listbox to show only those options which are relevant to a given
product line.

John W. Vinson [MVP]

 

[EvilSewingMachine]

I have a table "tbl_Specs" that contains the fields for all 6 product
lines. Each product entered gets a primary key. All product lines
share general fields like "Length" and "Width", they also all have
fields unique to that product like "Density" or "ControlLeftOrRight".
I have 6 subforms set in tabs with each subform's record source coming
from queries that uses a unique field of each product to display the
right ones. Each subform is then configured by the options selected,
as stated originally.

I'm feeling a bit foggy on what you mean John. Normalized table
structure? Maybe I'm missing some DB theory or terminology. Could you
elaborate?

Noah