Design question, listbox or something else?

[brigitte]

Hi all,

First off, I'm not a trained programmer, but I usually manage to
achieve what I want by adapting code fragments.

My users asked me to add functionality to their database which I've
spent the last week trying to implement, without success.

It's a database focussing on trademark administration and keeping track
of actions that must be performed prior to a specific date.

Trademarks are recorded for several classes of goods and services and
there's a maximum of 45 classes to choose from. Classes consist of a
class number and a standard description.

However, they furthermore asked whether it's possible to choose the
classes and adapt the descriptions to the client's specific
requirements. Also, could there be some indication that the standard
description has been altered?

Is it possible to use a popup form with a multi-select listbox
containing all 45 classes, fill in the user selection in a subform on
the main form, allow users to change the descriptions and use e.g. a
checkmark to indicate it's a non-standard description? Are there any
better alternatives? How to go about coding it all? I've already looked
into capturing the input from a multi-select listbox and feeding it to
an append query but either my relationships are set up wrong or it's
the wrong approach.

As for relationships, I have a table containing classes and a table
with trademark information, the PK's of which are linked in a separate
table.

Thanks for any help!


Brigitte

 

[Allen Browne]

Hi Brigitte

It sounds like a trademark can belong to multiple classes, and of course a
class can have many trademarks. This means you have a many-to-many relation
between trademarks and classes, so you will need a TrademarkClass table--a
junction table that has a foreign key field to Trademark and a foreign key
field to Class.

If we can assume that any trademark is the property of a single client (i.e.
a trademark is never owned jointly by companies), your basic tables will be
something like this:

Client table (one record for each company that takes out trademarks):
ClientID Autonumber primary key
Client Text company name
...

Trademark table (one record for each trademark taken out):
TrademarkID AutoNumber primary key
Trademark Text name of this trademark
ClientID Number relates to Client.ClientID
...

Class table (one record for each class a trademark can cover):
ClassID AutoNumber primary key
Class Text name of this class
ClassDescrip Text standard description
...

TrademarkClass (one record for each class a trademark includes):
TrademarkClassID AutoNumber primary key
TrademarkID Number relates to Trademark.TrademarkID
ClassID Number relates to Class.ClassID
Descrip Text actual description used for this
entry.

You will end up with a form to enter the Trademark, and it will have a
subform in Continuous or Datasheet view showing the classes that apply to
this trademark (one per row.) The subform will have a combo box for
selecting the ClassID (so the combo's RowSource will be the Class table.)

For the subform, create a query that contains the TrademarkClass table and
the Class table. You need all fields from TrademarkClass in the query, and
just the ClassDescrip field from the Class table. Save the query, and use it
as the RecordSource for your subform.

With that structure an interface in place, you can use the AfterUpdate event
of the ClassID combo to assign the Descrip based on the ClassDescrip, i.e.:
Private Sub ClassID_AfterUpdate()
If Not IsNull(Me.ClassID) Then
Me.Descrip = Me!ClassDescrip
End If
End Sub

You can now add a check box to the subform and put this into its
ControlSource:
=([Descrip] <> [ClassDescrip])
The check box will automatically indicate if the two values are different.

You now have an interface where the text defaults to the same value, can be
changed to something else, and Access automatically flags the rows where it
has been changed.

 

[Jeff Boyce]

Brigitte

Maybe it's only the way I'm looking at it, but it seems to me that if:

you have 45 "set" classes (with their description), and
you revise the description, that
you have a new "class"

Are you saying you have only 45 categories possible in your universe, but
that users are creating new (and different) instances, so that there could
be 17 different descriptions of class #3?

It sounds like you might have a one-to-many situation.

On the other hand, perhaps you are describing a situation in which there are
only and ever 45 set classes, but each user may wish to entitle/describe
his/her use of class #3 differently, while still maintaining the original
description.

In that case, you could consider adding a new field, not in the table of
class descriptions, but in the table you use to hold which class the user
has selected. The new field can remain blank, but could also be filled with
a "what I want to call this class" description. This way, a user could use
class #3 100 times, in context, and describe it differently each time, but
still be able to point back to the "original" description.

You could work with this approach without bothering to add a checkbox, since
the presence of an "alternative" description in the record is proof of the
existence of an alternative description!

Or am I still missing your description?


--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[brigitte]

Well, what I'm trying to avoid is the following. The standard
description for e.g. class 1 is: "Chemicals used in industry, science
and photography, as well as in agriculture, horticulture and forestry;
unprocessed artificial resins, unprocessed plastics; manures; fire
extinguishing compositions; tempering and soldering preparations;
chemical substances for preserving foodstuffs; tanning substances;
adhesives used in industry."

It's very broad and although some clients are happy with the standard
description, others may wish to change it as they are not involved in
e.g. agriculture or any the other examples nor have any such plans in
future. They might want to change the class to: "Chemicals used in
photography, especially for developing film" so as to specify their
particular use, which I believe strengthens the protection the
trademark enjoys.

The chances of us having a second client opting to use the exact same
description are rather slim and I don't want to end up with hundreds of
standard plus non-standard descriptions for my users to choose from,
which is why I came up with the "limited to list" listbox approach.

With two different approaches you guys have given me something to work
with and I can at least see one mistake I made in my table setup from
what Allen describes. I'll resume work on this project tomorrow and
will get back to you if I can't figure it out still, if I may. Thanks a
lot!

 

[brigitte]

Just wanted to let you both know it works perfectly! I've even added a
bonus for my users, English translations of the Dutch descriptions,
called up by the press of a button. These can of course also be amended
as required. I never thought I would be able to figure this out, but I
did thanks to your help