MS Access - Automatic field update question

Factual Questions
#1

I have an MS Access 2003 Table A which has a Type field and a Subtype field. I want to be able to select a Type+Subtype combination from a lookup link to Table B, which has an ID field linked to a unique Type/Subtype combination, and have both the Type and Subtype fields in Table A filled based on the selection.

For example, if I have a new record in Table A and select the record A9/Widget/Blue from Table B, the record field for Type will then show Widget and the Subtype field will show Blue.

I have no trouble with creating a lookup which will update one field at a time, but I want to be able to update both fields based on a single selection.

Any ideas?

#2

Dropdown is a combo box, showing the ID, Type, and Subtype in the display, right?

In the OnUpdate event for the dropdown, you need to call VB code looking something like this:

txtType = Me.Combo1.Column(1)
txtSubtype = Me.Combo1.Column(2)

(The column numbers aren’t typos; the first column is column 0 in VB, even though the column numbers on the Access form [eg. for the Display property on a combo box] start with 1. Don’t ask me, ask Microsoft.)

#3

SCSimmons, how do I do this? I have some limited experience with working with Event code in reports, but am not an Access/database expert, and there is no Event section visible in the table design screen.

#4

Why can’t you just keep that info in table B, and use a join to get it out in a query?

#5

This is a database cataloguing all my hobby reference sources from my library, and is up to 6000 records with about 25% of them included so far. I’m doing data entry directly into Table A, and it helps a lot to be able to see what’s there, as Type & Subtype are critical info, and to be able to filter on the Type and/or Subtype to speed up entry and verification. It will also help when I’m finished and want to make a quick ad-hoc search directly in the table without setting up a special query.