User-defined fields

[bhammer]

A2003

First Table called:
---------------------------
TBLPRODUCTS
ProductID
Description
Property1YN
Property2YN
Property3YN
etc. . .
-------------------------

Second Table called:
---------------------------
TBLPROPERTIES
PropID
PropertyDescription
---------------------------

This is a single user db. I want the user to be able to build the
Properties table as they wish (for example, green, blue, red) and
then, from a datasheet form based on the Products table, check the Y/N
box where that Product has those Properties, or not.

What do I link? How do I get the Property to show as a column header,
when it may change?

Brad.

 

[Arvin Meyer [MVP]]

Include all the check box controls in the form, and write a bit of code in
each of the check box's click event to show or hide (or in a datasheet, set
the columnwidth property)

 

[bhammer]

Arvin,

I don't think I need an OnClick event for the checkbox--I don't want
to show or hide anything; I want to store yes (or no) if that product
has one or more of the properties from the Properties table. The basic
problem I'm having is how to associate a particular checkbox with a
particular property. In other words, how do I link ckbxProperty3YN
with, say, the 5th Property in the Property table? For example,
Product254 is Green, Square, Metal (the user checks those boxes) but
not others.

This seems basic, but is stumping me for some reason.

 

[Arvin Meyer [MVP]]

IOW, if there is a value in one of the properties, you want to store a yes
or no value in a Boolean field? To do that is to store the result of a
calculation that can be displayed in a form every time the record is shown.
Results of calculations should not be stored since they can be repeated at
any time.

 

[bhammer]

IOW, if there is a value in one of the properties, you want to store a yes
or no value in a Boolean field?

Hmm. Not exactly. (Maybe a junction table is what I need?).

Rather simply, I want the user go down the list of Products (in
DataSheet form view), click in a product row (record), and move along
the columns and be able to tick-off yes or no for each ProductProperty
(all ProductProperties are displayed as column headers in the same
DataSheet form).

The user builds and edits the ProductProperties in a different
form. . .

 

[Arvin Meyer [MVP]]

That design isn't right either. What you are describing is a spreadsheet
with checkboxes, not a database. Proper normalization would have each
ProductID and property occupying a single row. You could then use a
cross-tab query to display what you want in a report.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[bhammer]

Arvin,

Thank you for all your help. Yes, it appears that what I want is not
possible in a database--an editable crosstab query.

I designed a successful work-around however, that uses a junction
table with two primary keys. The junction table is bound to a subform.
IN the main form the user can double-click on the desired
ProductProperty (displayed in a listbox) to assign it to the Product
displayed on the main form (by making a new record in the subform).

I can then view the whole thing in a crosstab query. But not edit :
( . . .

-thx again, Brad