field calculation data

[Christina]

I am a fairly new Access user and am having trouble
setting up my database most efficiently.

I have a large group of signs, each with individual
physical attributes. I would like to have one table with
the sign name as one field and the physical attributes as
others (ie, height, width, color, etc). Then I would like
to have another table that computes the sign design
criteria based on the physical attributes listed in the
first table. I need it to run like this, I enter the sign
name into a form, then Access retrieves the physical
attributes from the sign table and uses them to perform
the calculations as appropriate.

I currently have just one table/form and all of the sign
attributes are listed in if then statesments in code (ie
if SignDesignation = "1a" then width=24). This worked
fine for a while, but now I have too many signs, and the
code has become too large access errors out.

I have also tried breaking that portion of the code into
several smaller procedures and was hoping to call each
procedure using a macro, but I am having trouble getting
that to work too.

Ideally I would like to have two seperate tables. I think
I should be able to do this using relationships, but
haven't been able to get it to retrieve the physical
aspects. Any ideas?

 

[tina]

i believe you're on the right track to have a Signs table
that lists the sign name, height, width, color, etc as
fields.
i'm not quite sure what you're planning on doing with the
second table...but...
using a Signs table, you could try this in the form:
create a combo box using a Select statement for the
RowSource that includes all the sign attribute fields you
need for your calculations.
make sure the Signs table's primary key field is the Bound
column in the combo box.
if you don't want to see any column except the name, set
the Column Width of the other columns to zero.
to run the calculations in code, refer to the combobox's
columns to pull the appropriate values.
Example - combobox columns are
PK (bound column)
Name (visible column)
Height
Width
Color
in form view, enter the sign's name in the combo box.
in the VBA calculation, refer to the selected record's
Width as Me!ComboSigns.Column(3)
remember the first column in a combo box is zero, not 1.
hth