How to create a multi-table input form in Microsoft Access?

Question

Is it possible to a make a form in Microsoft Access where a you can create entries in 2 tables in the same page?

Imagine there is a table with 3 fields:

1. ObjectId
2. ObjectName
3. TypeId

where TypeId is the PK of another table which has the following fields:

1. TypeId
2. TypeName

I want to show in my form 2 editable text fields

1. ObjectName
2. TypeName

I want the user to be able to add both Objects and Types but, the way I am implementing it, it can only

1. add a Type entry when adding a Object
2. edit the associated Type when editing a Object

But I want to also be able to

1. select a exisiting Type when adding a Object
2. add a Type when editing a Object

In the same form

Solution

The usual way of doing this is to create a combo box with two columns, the TypeID and TypeName fields from the second table, hide the first column, and bind the combo box to the TypeID field of the first table on your form.

The combo box wizard can do this for you, in fact.

Comments

[CodyChen]

Have you thought about subforms? They can be appear to be a part of the actual form but in fact has different table properties. You link the two forms together by the master / child relationship. In this method you can update the master record while also update the child records.

[Jader Dias]

That's exactly what I am doing.

[David W. Fenton]

Adding a new type involves using the NotInList event. The help file gives examples of how to do this.