Combo Box help!

[mdavis via AccessMonster.com]

I have a form that is based on a table called SpecTransactions. I have an
unbound combo box called cboCabExt1.The cboCabExt1 row source is based on a
table that is not related to SpecTransactions.

The cboCabExt1 combo box contains different types of hardwood plywood (ex. PS
Maple, PS Cherry, Highly Figured Anigre, etc.) If the user does not see the
type of plywood they need I would like them to be able to double click on the
cboCabExt1 combo box and have a pop up form open. The pop up form would be
based on a table called TblCabExtHP. I would have an autonumber for the
primary key, a field called VENEER CUT (ex. PS, Quartered, Rotary, etc.),
VENEER SPECIE (ex. Maple, Cherry, etc.), FIGURE (Highly Figured, Medium
Figure, Lightly Figured). After the user finishes filling out the pop up
form they would click SAVE and it would take them back to SpecTransactions
Form and already have the new record appear in the cboCabExt1 combo box.
Here is where it would get tricky.

I have a field in the SpecTransactions table called SpecEntry. I would
somehow like to have the cboCabExt1 description (not the autonumber) to be
entered into the SpecEntry field when the SAVE command button is clicked for
the SpecTransactions Form. Is this possible? Is there a better way to do
this?

Thank you in advance.

 

[Ken Snell \(MVP\)]

I'm curious why you want to (apparently) redundantly store data in one table
that already exist in another table? You can always look that value up via
query.

But, you can use the Column property of the combo box control to read the
value from another column in the combo box's Row Source. This property is
zero-based, meaning that the first column in the Row Source is numbered
zero, the second is one, etc.

So, to store the value from the second column:

Me.NameOfWhereToStoreValue = Me.cboCabExt1.Column(1)

 

[mdavis via AccessMonster.com]

Thanks for responding so quickly Ken. In answer to your question I'm
attempting to create a specification database for a custom cabinet business.
On the specifications report there is over 100 different fields. So what I
did was created a table called TblSpecFields that has an auto #id and the
description of these 100 fields. Then I related it to the SpecTransactions
Table. Each of the TblSpecFields holds differnt info. For example there is
a field called CABINET MOTIF (which I created a simple cbo based on values
that I entered into the properties.), CABINET EXTERIOR MATERIAL (which is
what you helped me with in this thread.). I want the user to have detailed
fields (Veneer Cut, Veneer Species, Figure) guiding them through the data
entry process so that data is consistent and to make it as dumby proof as
possible. The problem is that all of the data for each of the SpecFields is
being held in a field in the SpecTransactions Table called SPEC ENTRY. So
that is why I can't relate the TblCabExtHP to the SpecTransactions Table. Is
there a better way of setting up my tables than what I currently have?

I'm curious why you want to (apparently) redundantly store data in one table
that already exist in another table? You can always look that value up via
query.

But, you can use the Column property of the combo box control to read the
value from another column in the combo box's Row Source. This property is
zero-based, meaning that the first column in the Row Source is numbered
zero, the second is one, etc.

So, to store the value from the second column:

Me.NameOfWhereToStoreValue = Me.cboCabExt1.Column(1)

I have a form that is based on a table called SpecTransactions. I have an
unbound combo box called cboCabExt1.The cboCabExt1 row source is based on

[quoted text clipped - 25 lines]

Thank you in advance.

 

[tina]

there is

a field called CABINET MOTIF (which I created a simple cbo based on values
that I entered into the properties.)

using a Lookup field in a table - any field, any table - is going to cause
you a lot of grief. for more information, see
http://home.att.net/~california.db/tips.html#aTip8.

other than that, a table with 100 fields is almost guaranteed to *not* be
normalized. recommend you stop in your tracks, and read up on the principles
of relational design before you go any further in developing your database.
for more information, see
http://home.att.net/~california.db/tips.html#aTip1.

hth

Thanks for responding so quickly Ken. In answer to your question I'm
attempting to create a specification database for a custom cabinet business.
On the specifications report there is over 100 different fields. So what I
did was created a table called TblSpecFields that has an auto #id and the
description of these 100 fields. Then I related it to the SpecTransactions
Table. Each of the TblSpecFields holds differnt info. For example there is
a field called CABINET MOTIF (which I created a simple cbo based on values
that I entered into the properties.), CABINET EXTERIOR MATERIAL (which is
what you helped me with in this thread.). I want the user to have detailed
fields (Veneer Cut, Veneer Species, Figure) guiding them through the data
entry process so that data is consistent and to make it as dumby proof as
possible. The problem is that all of the data for each of the SpecFields is
being held in a field in the SpecTransactions Table called SPEC ENTRY. So
that is why I can't relate the TblCabExtHP to the SpecTransactions Table. Is
there a better way of setting up my tables than what I currently have?

I'm curious why you want to (apparently) redundantly store data in one table
that already exist in another table? You can always look that value up via
query.

But, you can use the Column property of the combo box control to read the
value from another column in the combo box's Row Source. This property is
zero-based, meaning that the first column in the Row Source is numbered
zero, the second is one, etc.

So, to store the value from the second column:

Me.NameOfWhereToStoreValue = Me.cboCabExt1.Column(1)

I have a form that is based on a table called SpecTransactions. I have an
unbound combo box called cboCabExt1.The cboCabExt1 row source is based

on
[quoted text clipped - 25 lines]

Thank you in advance.

 

[Ken Snell \(MVP\)]

I am not fully understanding your setup, but typically one would store the
primary key value of a chosen item and then can run a query to look up the
related information for that primary key value from the table. For example,
if you have a specification table that lists all the information, and that
table has a primary key field, a description field, etc., you would store
the primary key value in the table that is being populated with all the
individual specifications that apply to a cabinet record; one would not
store the actual description, etc. in that cabinet record.

From your description of your cabinet table, though, I get the strong
impression that your table(s) is/are not normalized -- if you have separate
fields in the cabinet record for each specification type, then I'm
definitely sure of my conclusion. Typically, one would set up a cabinet
table like this:
tblCabinets
CabinetID (primary key)
PurchaserID
DueDate
StartDate
(etc. -- fields related to the cabinet identification)

tblSpecs
SpecID (primary key)
SpecName
SpecComments
(etc. -- fields related to an individual specification)

tblCabSpecs
CabinetID (composite primary key with SpecID; foreign key to
tblCabinets)
SpecID (composite primary key with CabinetID; foreign key to
tblSpecs)
CabinetSpecValue

The third table allows you to assign as many specifications to each cabinet
as you wish without having to have a separate field in tblCabinets for each
spec). This not only makes it very easy to add / delete specs from your
database, but it also will greatly simplify your queries that will be used
for reports, data lookups, etc.

I concur with tina about using "lookup compo boxes" in a table. If you're
doing that, don't. Use combo boxes on the form instead.
--

Ken Snell
<MS ACCESS MVP>

Thanks for responding so quickly Ken. In answer to your question I'm
attempting to create a specification database for a custom cabinet
business.
On the specifications report there is over 100 different fields. So what
I
did was created a table called TblSpecFields that has an auto #id and the
description of these 100 fields. Then I related it to the
SpecTransactions
Table. Each of the TblSpecFields holds differnt info. For example there
is
a field called CABINET MOTIF (which I created a simple cbo based on values
that I entered into the properties.), CABINET EXTERIOR MATERIAL (which is
what you helped me with in this thread.). I want the user to have detailed
fields (Veneer Cut, Veneer Species, Figure) guiding them through the data
entry process so that data is consistent and to make it as dumby proof as
possible. The problem is that all of the data for each of the SpecFields
is
being held in a field in the SpecTransactions Table called SPEC ENTRY. So
that is why I can't relate the TblCabExtHP to the SpecTransactions Table.
Is
there a better way of setting up my tables than what I currently have?

I'm curious why you want to (apparently) redundantly store data in one
table
that already exist in another table? You can always look that value up via
query.

But, you can use the Column property of the combo box control to read the
value from another column in the combo box's Row Source. This property is
zero-based, meaning that the first column in the Row Source is numbered
zero, the second is one, etc.

So, to store the value from the second column:

Me.NameOfWhereToStoreValue = Me.cboCabExt1.Column(1)

I have a form that is based on a table called SpecTransactions. I have
an
unbound combo box called cboCabExt1.The cboCabExt1 row source is based
on

[quoted text clipped - 25 lines]

Thank you in advance.

 

[mdavis via AccessMonster.com]

Actually this is how I currently have my tables set up (except instead of
tblCabinets I have tblRoom). The problem is for each of the Spec fields
(Motif, Cabinet Exterior, Door Style, Cabinet Finish, etc.) I would like to
have specific fields for the user to input data into so the data is
consistently entered for each of the Spec fields.
For example the Cabinet Exterior Form would have the following fields and
controls:
Veneer Specie (Text field -- User would type in Maple or Cherry)
Veneer Cut (cbo based on values -- values would be PS;Rift
Cut;Rotary;Quartered)
Figured (cbo based on values -- values would be Highly Figured;Medium
Figure;Lightly Figured)
After a user is done filling out this form they could have "Quartered Highly
Figured Cherry"
The way my database is set up right now is the description "Quartered Highly
Figured Cherry" is stored in a field in the SpecTransactions table called
SpecEntry. The Quartered Highly Figured Cherry" ID is not in the
SpecTranactions because they are not related. I think you already understand
this part. The problem is that I have 100 fields that I would like to supply
detailed data entry fields for like the example above for the Cabinet
Exterior Form but how do I do that since everyone of the 100 fields is going
to have different detailed data entry field?

What if I had a seperate table for each of the 100 Spec fields and then
related each of these tables to my SpecTransactions Table? I think the
maximum fields in a table is 255. Would this cause problems when it came to
designing reports?

So I would have a seperate table for the CABINET MOTIF, CABINET EXTERIOR,
DOOR STYLE, ETC.

What do you think I should do??
I really appreciate all of your help.
Matt

I am not fully understanding your setup, but typically one would store the
primary key value of a chosen item and then can run a query to look up the
related information for that primary key value from the table. For example,
if you have a specification table that lists all the information, and that
table has a primary key field, a description field, etc., you would store
the primary key value in the table that is being populated with all the
individual specifications that apply to a cabinet record; one would not
store the actual description, etc. in that cabinet record.

From your description of your cabinet table, though, I get the strong
impression that your table(s) is/are not normalized -- if you have separate
fields in the cabinet record for each specification type, then I'm
definitely sure of my conclusion. Typically, one would set up a cabinet
table like this:
tblCabinets
CabinetID (primary key)
PurchaserID
DueDate
StartDate
(etc. -- fields related to the cabinet identification)

tblSpecs
SpecID (primary key)
SpecName
SpecComments
(etc. -- fields related to an individual specification)

tblCabSpecs
CabinetID (composite primary key with SpecID; foreign key to
tblCabinets)
SpecID (composite primary key with CabinetID; foreign key to
tblSpecs)
CabinetSpecValue

The third table allows you to assign as many specifications to each cabinet
as you wish without having to have a separate field in tblCabinets for each
spec). This not only makes it very easy to add / delete specs from your
database, but it also will greatly simplify your queries that will be used
for reports, data lookups, etc.

I concur with tina about using "lookup compo boxes" in a table. If you're
doing that, don't. Use combo boxes on the form instead.

Thanks for responding so quickly Ken. In answer to your question I'm
attempting to create a specification database for a custom cabinet

[quoted text clipped - 38 lines]

 

[tina]

data entry issues shouldn't dictate table design. you're going to be better
off with a normalized structure; you can get as creative as you need to in
setting up the user interface. so that you can control which values are
available to be selected for a specific spec, just set up two tables, as

tblSpecs
SpecID (primary key)
SpecName
(Motif, Cabinet Exterior, Door Style, Cabinet Finish, etc, will each be a
record in this table.)

tblSpecOptions
OptionID (primary key)
SpecID (foreign key from tblSpecs)
OptionName
(Maple, Cherry, PS, Rift Cut, Rotary, Quartered, Highly Figured, Medium
Figured, Lightly Figured, etc, would each be a record in this table. and
each record would be related to the appropriate record in tblSpecs - Maple
and Cherry records linked back to Veneer Specie; PS, Rift Cut, Rotary, and
Quartered records linked back to Veneer Cut, etc.)

hth

Actually this is how I currently have my tables set up (except instead of
tblCabinets I have tblRoom). The problem is for each of the Spec fields
(Motif, Cabinet Exterior, Door Style, Cabinet Finish, etc.) I would like to
have specific fields for the user to input data into so the data is
consistently entered for each of the Spec fields.
For example the Cabinet Exterior Form would have the following fields and
controls:
Veneer Specie (Text field -- User would type in Maple or Cherry)
Veneer Cut (cbo based on values -- values would be PS;Rift
Cut;Rotary;Quartered)
Figured (cbo based on values -- values would be Highly Figured;Medium
Figure;Lightly Figured)
After a user is done filling out this form they could have "Quartered Highly
Figured Cherry"
The way my database is set up right now is the description "Quartered Highly
Figured Cherry" is stored in a field in the SpecTransactions table called
SpecEntry. The Quartered Highly Figured Cherry" ID is not in the
SpecTranactions because they are not related. I think you already understand
this part. The problem is that I have 100 fields that I would like to supply
detailed data entry fields for like the example above for the Cabinet
Exterior Form but how do I do that since everyone of the 100 fields is going
to have different detailed data entry field?

What if I had a seperate table for each of the 100 Spec fields and then
related each of these tables to my SpecTransactions Table? I think the
maximum fields in a table is 255. Would this cause problems when it came to
designing reports?

So I would have a seperate table for the CABINET MOTIF, CABINET EXTERIOR,
DOOR STYLE, ETC.

What do you think I should do??
I really appreciate all of your help.
Matt

I am not fully understanding your setup, but typically one would store the
primary key value of a chosen item and then can run a query to look up the
related information for that primary key value from the table. For example,
if you have a specification table that lists all the information, and that
table has a primary key field, a description field, etc., you would store
the primary key value in the table that is being populated with all the
individual specifications that apply to a cabinet record; one would not
store the actual description, etc. in that cabinet record.

From your description of your cabinet table, though, I get the strong
impression that your table(s) is/are not normalized -- if you have separate
fields in the cabinet record for each specification type, then I'm
definitely sure of my conclusion. Typically, one would set up a cabinet
table like this:
tblCabinets
CabinetID (primary key)
PurchaserID
DueDate
StartDate
(etc. -- fields related to the cabinet identification)

tblSpecs
SpecID (primary key)
SpecName
SpecComments
(etc. -- fields related to an individual specification)

tblCabSpecs
CabinetID (composite primary key with SpecID; foreign key to
tblCabinets)
SpecID (composite primary key with CabinetID; foreign key to
tblSpecs)
CabinetSpecValue

The third table allows you to assign as many specifications to each cabinet
as you wish without having to have a separate field in tblCabinets for each
spec). This not only makes it very easy to add / delete specs from your
database, but it also will greatly simplify your queries that will be used
for reports, data lookups, etc.

I concur with tina about using "lookup compo boxes" in a table. If you're
doing that, don't. Use combo boxes on the form instead.

Thanks for responding so quickly Ken. In answer to your question I'm
attempting to create a specification database for a custom cabinet

[quoted text clipped - 38 lines]

Thank you in advance.

 

[Ken Snell \(MVP\)]

I concur with tina. It sounds as if your tables are not normalized yet if
you're still wanting separate fields for each specification.

Data entry on a form for the different specifications is easily done via a
continuous forms view for a subform.
--

Ken Snell
<MS ACCESS MVP>

data entry issues shouldn't dictate table design. you're going to be
better
off with a normalized structure; you can get as creative as you need to in
setting up the user interface. so that you can control which values are
available to be selected for a specific spec, just set up two tables, as

tblSpecs
SpecID (primary key)
SpecName
(Motif, Cabinet Exterior, Door Style, Cabinet Finish, etc, will each be a
record in this table.)

tblSpecOptions
OptionID (primary key)
SpecID (foreign key from tblSpecs)
OptionName
(Maple, Cherry, PS, Rift Cut, Rotary, Quartered, Highly Figured, Medium
Figured, Lightly Figured, etc, would each be a record in this table. and
each record would be related to the appropriate record in tblSpecs - Maple
and Cherry records linked back to Veneer Specie; PS, Rift Cut, Rotary, and
Quartered records linked back to Veneer Cut, etc.)

hth

Actually this is how I currently have my tables set up (except instead of
tblCabinets I have tblRoom). The problem is for each of the Spec fields
(Motif, Cabinet Exterior, Door Style, Cabinet Finish, etc.) I would like to
have specific fields for the user to input data into so the data is
consistently entered for each of the Spec fields.
For example the Cabinet Exterior Form would have the following fields and
controls:
Veneer Specie (Text field -- User would type in Maple or Cherry)
Veneer Cut (cbo based on values -- values would be PS;Rift
Cut;Rotary;Quartered)
Figured (cbo based on values -- values would be Highly Figured;Medium
Figure;Lightly Figured)
After a user is done filling out this form they could have "Quartered Highly
Figured Cherry"
The way my database is set up right now is the description "Quartered Highly
Figured Cherry" is stored in a field in the SpecTransactions table called
SpecEntry. The Quartered Highly Figured Cherry" ID is not in the
SpecTranactions because they are not related. I think you already understand
this part. The problem is that I have 100 fields that I would like to supply
detailed data entry fields for like the example above for the Cabinet
Exterior Form but how do I do that since everyone of the 100 fields is going
to have different detailed data entry field?

What if I had a seperate table for each of the 100 Spec fields and then
related each of these tables to my SpecTransactions Table? I think the
maximum fields in a table is 255. Would this cause problems when it came to
designing reports?

So I would have a seperate table for the CABINET MOTIF, CABINET EXTERIOR,
DOOR STYLE, ETC.

What do you think I should do??
I really appreciate all of your help.
Matt

I am not fully understanding your setup, but typically one would store the
primary key value of a chosen item and then can run a query to look up the
related information for that primary key value from the table. For example,
if you have a specification table that lists all the information, and that
table has a primary key field, a description field, etc., you would
store
the primary key value in the table that is being populated with all the
individual specifications that apply to a cabinet record; one would not
store the actual description, etc. in that cabinet record.

From your description of your cabinet table, though, I get the strong
impression that your table(s) is/are not normalized -- if you have separate
fields in the cabinet record for each specification type, then I'm
definitely sure of my conclusion. Typically, one would set up a cabinet
table like this:
tblCabinets
CabinetID (primary key)
PurchaserID
DueDate
StartDate
(etc. -- fields related to the cabinet identification)

tblSpecs
SpecID (primary key)
SpecName
SpecComments
(etc. -- fields related to an individual specification)

tblCabSpecs
CabinetID (composite primary key with SpecID; foreign key to
tblCabinets)
SpecID (composite primary key with CabinetID; foreign key to
tblSpecs)
CabinetSpecValue

The third table allows you to assign as many specifications to each cabinet
as you wish without having to have a separate field in tblCabinets for each
spec). This not only makes it very easy to add / delete specs from your
database, but it also will greatly simplify your queries that will be used
for reports, data lookups, etc.

I concur with tina about using "lookup compo boxes" in a table. If
you're
doing that, don't. Use combo boxes on the form instead.
Thanks for responding so quickly Ken. In answer to your question I'm
attempting to create a specification database for a custom cabinet
[quoted text clipped - 38 lines]

Thank you in advance.