Multiple Choice Problem - Access 2003

[HWhite]

Example: I have a "Master" table with a lookup field called "Acceptable
Colors".

It links to a "Color" table which has yes/no options for yellow, blue, black
etc... and created a form with the check boxes.

In my Main Entry Form, I added the "Color" sub-form. So far, so good.

In testing, I go record to record randomly selecting multiple colors. As I
scroll forward and back through the records, it remembers what I've selected
for each record. Cool.

However, when I go and look at my Colors table and I want to see the record
that shows I checked "red", it doesn't show me the record when I click the
plus sign next to the ID number. When I go to my master table and look at
the Acceptable Colors field, there is nothing in there. But, when I go back
to my form, everything is how it should be.

How can I fix this? Does this really matter? What am I doing wrong?
I'm just concered that later when I need to run queries and reports that
this will be a problem.
Thanks for your help.

 

[HWhite]

By the way, I did create a query and after fixing the "join" fields so ID and
ID were joined, the query worked fine. But I still would like the master
table to reflect that there's an entry in the field and the colors table to
show the record that the selections belong to. I can live with this the way
it is, but I'm concerned if others access the master table and don't see an
entry there, they might make a change that would screw things up.

thanks again.

 

[Wayne-I-M]

Hi

The query will not affect the linked tables in the Table View.

It is a good idea not to use this access facility as it will "always" lead
(sooner or later) to users inputting data directly into the tables.

What you need is a linking table (so you have a many to many relationship).

Table1 = JobID + other stuff
Table2 = CoulorID + other stuff
Table 3 = JobID & CoulorID + stuff directly related to this this specifc
data mix. ie. Date of order, order made by, notes, etc

Create a query based on these tables and then create a form based on this
query.

Either view the details in a query or a report.
The tables are there just to hold data and do a few of things (indexing,
etc) but nothing else and users should never input directly into a table.

 

[Ken Sheridan]

As Wayne says, you need a third table to model the many-to-many relationship
type between the Master table and the Colors table. The Colors table needs
just one column , Color, with values Red, Blue, etc.

The Master table should not have an AcceptableColors column (field). Rather
this should be the name of the new table which models the many-to-many
relationship type. This table will have two columns, one a foreign key which
references the primary key of the master table, MasterID say, the other
Color, also a foreign key referencing the primary key of Colors. The primary
key of AcceptableColors is a composite one made up of the two columns
MasterID and Color.

When it comes to data input you should use a form based on the master table,
with a subform based on the AcceptableColors table in it. The main form must
be in single form view, and the subform should be in continuous form or
datasheet view. The subform will be linked to the main parent form on the
MasterID columns by virtue of these being the LinkMasterFields and
LinkChildFields properties. It will contain just one control, a combo box
bound to the Color column. The combo box's RowSource will be:

SELECT Color FROM Colors ORDER BY Color;

To select any number of acceptable colours for the main form's current
record is simply a question of inserting records into the subform by
selecting a colour from the combo in each case.

As regards a query you simply join the Master table to the AcceptableColors
table on the MasterID columns and return whatever columns you require from
the Master table and the Color column from AcceptableColors. In a report
based on this query you can group on MasterID and give it a group header,
putting the controls bound to the columns from master in the header, and the
Color control in the detail section. Alternatively you can use a report and
subreport in the same way as the form/subform set-up described above. The
advantage of the latter is that it will allow you to put the acceptable
colours per Master record horizontally in a line by using an 'across then
down' layout multi-column subreport, rather than vertically as a single
column, which you'd get with a grouped report. You'll find an example of a
subreport used in this way at:


http://community.netscape.com/n/pfx...libraryMessages&webtag=ws-msdevapps&tid=24271


The file also includes a single report which does the same thing by amending
the layout with code in the report's module. This was only included in
answer to a reader's question, however (the file was originally produced for
magazine column by a contact of mine) to show that it was possible, not as a
realistic solution. Using a subreport is far simpler.

One thing you might be wondering is why, when the acceptable colours per
Master record are stored in the AcceptableColors table, it is necessary to
have a Colors table at all. One reason is that it is used as the source for
the combo box on the subform, but a more fundamental reason is that it
protects data integrity. By creating a relationship between Colors and
AcceptableColors and enforcing referential integrity and cascade updates this
ensures (a) that only a valid colour name can be entered into
AcceptableColors and (b) that if the name of a colour in Colors should be
changed, e.g. if 'Red' were changed to 'Rose' or 'Scarlet' say, then the
values in all the matching rows in AcceptableColors would be automatically
updated.

Ken Sheridan
Stafford, England

 

[Wayne-I-M]

The primary key of AcceptableColors is a composite one made up of the two
columns MasterID and Color.


hmmmmmmm not sure about that. (but what do I know)
AQs there may be a mix of colours this may not work

It "may" be abetter idea (would take some setting up but...)
In the colours table (that Ken suggested) record the CMYK or RGB of each
colour (told you it would take some setting ??)
Then add the colour to the 3rd table by clicking the colours you have in
stock.

If you sit down and play around for a while you could have a box on you form
that will show the "mix" of the colours you have clicked.

You "may" be able to google for this - there are already thing like this for
HTML but not sure about VBA.

Would be good though if you got it to work.


About now someone will be sending you a link that will do just (lebans??)