making a form to display existing records in a ComboBox, adding to

[Gingertrees]

I'm making a library database for a small non-profit org, and I need help
streamlining the database to be user-friendly.

The books are organized by several things, including "Primary topic" and a
related shelf number. For example, primary topic "Parenting" has numbers
120.001, 120.005, etc. I've been arbitrarily assigning #s in datasheet view
of my main table. I set up a form that handles most of my input needs,
including a combo box for Primary Topic.
How do I make a form that does one of the following:
- when I select "parenting" from field Primary Topic, I want field Number to
display/ assign another 120.--- value. (e.g., if 120.001 and 120.003 are the
only existing values, my new book would be assigned 120.005). [And do similar
funcs for other prim. topics and number sets]
- OR if that's too complex, field Number could display all numbers that are
120.--- (as opposed to showing all values) in a drop-down list. I want to
arbitrarily add another number that will be saved in the table with the rest
of the numbers/titles.

I have the Access 2000 bible, but I haven't found info on how to do this, so
any help would be appreciated.
~G

 

[mscertified]

First you need to get your tables and relationships set up:

You need a Books table and a PrimaryTopics table
(This design assumes each topic has a separate shelf. If different topics
can share a shelf, or topics can occupy more than one shelf, the design needs
to change.)
You also need to know if every book has a separate shelf sequence.

Something like this:

PrimaryTopic:
ID = 1
TopicName = Parenting
Shelf Number = 120

Books:
ID
Title
Publisher
ISBN
PrimaryTopicID = 1
ShelfSequence (this is position on shelf e.g. 001)

You then choose the Topic from a combo box

The shelf sequence combo box source is dependent on the Topic combo box
selection.

If you are adding a new record, you need to assign one more than the last
ShelfSequence used. This can be done in the 'before update' event for the
form. Don't allow the user to choose or enter it just generate it
automatically.

I would think there would be off-the-shelf (pardon the pun) Access
applications you could use for this. It must have been done many times.

Dorian

 

[Gingertrees]

OK, I did most of that (already had topic and shelf number tables, they just
weren't one before). However,
"If you are adding a new record, you need to assign one more than the last
ShelfSequence used. This can be done in the 'before update' event for the
form."

How do I say "add one to previous" in the event procedure?

~G

First you need to get your tables and relationships set up:

You need a Books table and a PrimaryTopics table
(This design assumes each topic has a separate shelf. If different topics
can share a shelf, or topics can occupy more than one shelf, the design needs
to change.)
You also need to know if every book has a separate shelf sequence.

Something like this:

PrimaryTopic:
ID = 1
TopicName = Parenting
Shelf Number = 120

Books:
ID
Title
Publisher
ISBN
PrimaryTopicID = 1
ShelfSequence (this is position on shelf e.g. 001)

You then choose the Topic from a combo box

The shelf sequence combo box source is dependent on the Topic combo box
selection.

If you are adding a new record, you need to assign one more than the last
ShelfSequence used. This can be done in the 'before update' event for the
form. Don't allow the user to choose or enter it just generate it
automatically.

I would think there would be off-the-shelf (pardon the pun) Access
applications you could use for this. It must have been done many times.

Dorian

I'm making a library database for a small non-profit org, and I need help
streamlining the database to be user-friendly.

The books are organized by several things, including "Primary topic" and a
related shelf number. For example, primary topic "Parenting" has numbers
120.001, 120.005, etc. I've been arbitrarily assigning #s in datasheet view
of my main table. I set up a form that handles most of my input needs,
including a combo box for Primary Topic.
How do I make a form that does one of the following:
- when I select "parenting" from field Primary Topic, I want field Number to
display/ assign another 120.--- value. (e.g., if 120.001 and 120.003 are the
only existing values, my new book would be assigned 120.005). [And do similar
funcs for other prim. topics and number sets]
- OR if that's too complex, field Number could display all numbers that are
120.--- (as opposed to showing all values) in a drop-down list. I want to
arbitrarily add another number that will be saved in the table with the rest
of the numbers/titles.

I have the Access 2000 bible, but I haven't found info on how to do this, so
any help would be appreciated.
~G