2

I have a simple database that stores a customer's name, city and state. I've added to this two new tables. One is a lookup table, for describing a car model, and the second is a junction table for setting up one or more car models with a customer record.

Here's the relationship layout as of now:

enter image description here

And datasheets for each table. cust_id and model_id are auto increment columns.

demographics

enter image description here

lookup_model

enter image description here

junction_model

enter image description here

Using a scripting language, MySQL and HTML I could whip up a complete form in a few minutes. I am having a little trouble figuring out how this is done in Access 2007.

Two features I need are

  1. Ability to edit/add items to the lookup table
  2. Ability to add new customer records including selecting from cars found in the lookup table, while maintaining primary key relationships.

Ex:

"Mary Jane" owns a Ford Pinto and Datson 510. When I add her info with the to-be-created form, her cust_id would be 5, and two new entries would show up in the junction table with her cust_id: 2 (ford) and 5 (datsun).

Is there a relatively painless way of setting up this form in Access 2007?

Edit: this is what I've managed to get working so far. This subform lists the correct model/cust associations, but I am looking to show a combo box with only the model_desc shown (not the model id).

enter image description here

a coder
  • 7,530
  • 20
  • 84
  • 131

1 Answers1

1

A form containing a "subform" will serve you well in a case like this.

  • You can create a form named "Owners" that is bound to the [demographics] table in which you can add/edit the owner information (name, address, ...).

  • You can create a "continuous forms" form named something like "Cars_Owned_subform" that is bound to the [junction_model] table. That form will contain a combo box whose Row Source is the [lookup_model] table, displaying the [model_desc] and returning the [model_id].

  • Then you can add a Subform control to the "Owners" table using the Subform Wizard and it will help you link up the Subform to its parent based on [cust_id]. Then as you move from one Owner to the next their cars will automatically update in the subform, and if you add a new car in the subform it will automatically be associated with the [cust_id] of the Owner whose record is currently being displayed.

As for editing the lookup table, you could very easily create a simple form to edit the [lookup_model] table.

Edit re: question update

To have a combo box that displays the [model_desc] and returns the [model_id] you'll want to have the combo box "display" two columns where the first column has a width of zero. That is, the Format properties of the combo box should include values like...

Column Count: 2
Column Widths: 0";1.3646"

...and the Data properties should include values like...

Row Source: lookup_model
Bound Column: 1

The final result would look something like this:

owners_form.png

The subform in Design View would simply be:

subform.png

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • I think the combo box is working now. But it only shows one selected item. I have to use a datasheet view to show all contents from junction_models, but here I am only seeing the IDs. Is there a better way to show data that is already selected, in a human readable form? – a coder May 13 '13 at 19:46
  • @acoder RE: showing multiple items - On the Format tab of the Property sheet for the [cars_owned_subform], make sure that the Default View is set to "Continuous Forms". (p.s. I added some screenshots to my answer.) – Gord Thompson May 13 '13 at 19:50
  • 1) Can I disallow multiples of the same value? 2) Can I let users delete an existing customer-car association? – a coder May 13 '13 at 20:46
  • Since your answer successfully addresses this question, I've created another dealing with #2 above: http://stackoverflow.com/questions/16531496/delete-row-from-access-junction-table-via-form – a coder May 13 '13 at 21:36
  • @acoder RE: Can I disallow multiples of the same value? - Your Primary Key on [junction_model] is ([cust_id], [model_id]) so each person will only be able to "own" the same model once. – Gord Thompson May 13 '13 at 21:58