# MS Access - Automatic field update question
**URL:** https://boards.straightdope.com/t/ms-access-automatic-field-update-question/419363
**Category:** Factual Questions
**Created:** [September 16, 2007, 1:56am UTC](https://boards.straightdope.com/t/ms-access-automatic-field-update-question/419363 "2007-09-16T01:56:27Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@Bookkeeper](https://boards.straightdope.com/u/Bookkeeper)
#### Post date: [September 16, 2007, 1:56am UTC](https://boards.straightdope.com/t/ms-access-automatic-field-update-question/419363/1 "2007-09-16T01:56:27Z")
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?
---
### Author:  [@SCSimmons](https://boards.straightdope.com/u/SCSimmons)
#### Post date: [September 16, 2007, 2:28am UTC](https://boards.straightdope.com/t/ms-access-automatic-field-update-question/419363/2 "2007-09-16T02:28:53Z")
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.)
---
### Author:  [@Bookkeeper](https://boards.straightdope.com/u/Bookkeeper)
#### Post date: [September 16, 2007, 3:05am UTC](https://boards.straightdope.com/t/ms-access-automatic-field-update-question/419363/3 "2007-09-16T03:05:12Z")
**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.
---
### Author:  [@ultrafilter](https://boards.straightdope.com/u/ultrafilter)
#### Post date: [September 16, 2007, 4:03am UTC](https://boards.straightdope.com/t/ms-access-automatic-field-update-question/419363/4 "2007-09-16T04:03:23Z")
Why can’t you just keep that info in table B, and use a join to get it out in a query?
---
### Author:  [@Bookkeeper](https://boards.straightdope.com/u/Bookkeeper)
#### Post date: [September 16, 2007, 4:17am UTC](https://boards.straightdope.com/t/ms-access-automatic-field-update-question/419363/5 "2007-09-16T04:17:55Z")
[QUOTE=ultrafilter]
Why can’t you just keep that info in table B, and use a join to get it out in a query?
[/QUOTE]
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.