option group true/false

[Guest]

Hi,
I have an option group on a form that has 5 choices that can be true or false.
I've created a matrix table (tblMatrix) that gives all the possible options
for those buttons and each combination is given a number "LimitNo" that I
want to store in a table associated with other demograhics for a driver
(tblDriverInfo).

When the form comes back up with the individual's record, I want the
"LimitNo" value to correctly populate the option buttons on the form.

I'm trying to get the "LimitNo" into the tblDriverInfo by coding an event on
the form in the After Update area & I'm having trouble.

I'm thinking:
SELECT LimitNo From tblMatrix WHERE Me.optLens=tblMatrix.Lens_
AND Me.optHear=tblMatrix.Hear AND etc.

I get an error that says "Expect Case"
Also, I'm not sure what to say to place the data in the tblDriverInfo.Limit
cell.

Thank you,
Karl

 

[Baz]

Hmmm, I think you have designed this wrong. Seems to me that the LimitNo is
derived data. As such, there is no need to store it in your table, indeed
it is poor design to do so.

I think that what you should do is to have 5 check boxes on your form (an
option group is unnecessary: option groups are really for when only one of
the options can be chosen, like with a bunch of radio buttons) and have the
check boxes bound to 5 Yes/No fields in your table. Then, on fields or
reports that need to show the LimitNo, you have a calculated control which
works out and displys the LimitNo (by calling a user-defined function, if
necessary).

 

[Baz]

Hi again,

You have misunderstood me.

Firstly, I'm suggesting that you should not have a LimitNo field in the
DriverInfo table. If you have got the 5 Yes/No fields in the DriverInfo
table, you don't need to store the LimitNo because you can calculate it from
the Yes/No fields whenever you need it. Don't worry about having 5 extra
fields in the DriverInfo table, it will be hardly any overhead for Access
and it's much more important to get the design logically right rather than
to worry about micro-efficiencies.

So, with the 5 Yes/No fields in the table, you can easily create a form with
5 bound check boxes to display the Yes/No values. You can also have a text
box on the form to display the LimitNo, and it is this, the text box, which
would be a calculated control.

You already have a table with all your Yes/No combinations and the
corresponding LimitNo. At it's crudest/simplest, you could put this in the
control source for the LimitNo text box (substitute your own field/control
names):

=Dlookup("LimitNo","tblMatrix","Choice1=" & chk1 & " AND Choice2=" & chk2 &
" AND Choice3=" & chk3 &" AND Choice4=" & chk4 &" AND Choice5=" & chk5)

 

[Baz]

Always a wise choice Karl...