# 'nother Access queston: Filling in fields on a form based on an outside table
**URL:** https://boards.straightdope.com/t/nother-access-queston-filling-in-fields-on-a-form-based-on-an-outside-table/76040
**Category:** Factual Questions
**Created:** [August 13, 2001, 3:13pm UTC](https://boards.straightdope.com/t/nother-access-queston-filling-in-fields-on-a-form-based-on-an-outside-table/76040 "2001-08-13T15:13:03Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@Rhythmdvl](https://boards.straightdope.com/u/Rhythmdvl)
#### Post date: [August 13, 2001, 3:13pm UTC](https://boards.straightdope.com/t/nother-access-queston-filling-in-fields-on-a-form-based-on-an-outside-table/76040/1 "2001-08-13T15:13:03Z")
I have two tables and one form. Both tables share the same structure.
Table one: _Inventory Items_ contains fifty thousand records, the entire nationwide inventory
Table two: _Missing Items_ is a list of inventory items not found during an audit
Form: Simple data entry form (actually, it is a subform, but I don’t think that is important) to build the _Missing Items_ table.
The fields on the form are _Property Number_, _Serial Number_, _Make_, _Model_, and _Noun Description_. I’d like to give the user the ability to put in (or search for) either Property Number OR Serial Number and have all other fields fill in automatically (including the associated Prop or Serial number) based on a record located in the Inventory Items table. The form will also need to retain the ability to enter new inventory items, for those cases where the item is present, but not on the master inventory list.
Thanks
---
### Author:  [@Gartog](https://boards.straightdope.com/u/Gartog)
#### Post date: [August 13, 2001, 3:20pm UTC](https://boards.straightdope.com/t/nother-access-queston-filling-in-fields-on-a-form-based-on-an-outside-table/76040/2 "2001-08-13T15:20:51Z")
From the form design, make sure the wizard is selected and place a combo box on the form, this will do what you want, you will need a sepaerate combo box for each table.
for questions of this nature can I suggest [http://www.experts-exchange.com](http://www.experts-exchange.com) who have a message board specificly for Access Questions, as well as other computr realted subjects.
---
### Author:  [@Rhythmdvl](https://boards.straightdope.com/u/Rhythmdvl)
#### Post date: [August 13, 2001, 5:02pm UTC](https://boards.straightdope.com/t/nother-access-queston-filling-in-fields-on-a-form-based-on-an-outside-table/76040/3 "2001-08-13T17:02:28Z")
I have placed two combo boxes at the top of my form, one keyed to Property Number and one to Serial Number. I have a toggle button that lets a user switch between the two. I can display the results of all needed fields on my form using ComboBoxPropertyNumber.Column(X).
The text box record sources look like this: \*\* =IIf([PropToggle]=True,[PropComboBox].Column,[SerialComboBox].column)\*\*
The record source property of the form is set to the missing items table.
The rowsource property of the combo boxes are set to the Inventory items table.
I am almost there- but two problems remain.
1. Now that I have the information displayed on the form, how do I get it to populate the missing items table? I tried text boxes with [Property Number]=TextX but that doesn’t seem to work. How do I push the information into the table?
2)How do I allow for items that are not on the list? I have the Limit to List property on the combo boxes set to no, but the text boxes are limited to whatever results come from the combo boxes. The user needs to be able to enter new item information into all fields.
Thanks so far!
Rhythmdvl
---
### Author:  [@Rhythmdvl](https://boards.straightdope.com/u/Rhythmdvl)
#### Post date: [August 13, 2001, 5:44pm UTC](https://boards.straightdope.com/t/nother-access-queston-filling-in-fields-on-a-form-based-on-an-outside-table/76040/4 "2001-08-13T17:44:37Z")
Whew. Thanks very much for the help above. I just figured out how to accomplish what I wanted to do in my last post. I pushed the lookup boxes into the form’s header and put regular ol’ bound text boxes in the detail section. I added a button in the search area that has simple VBA code that reads FIELD1=LOOKUP BOX1 for each associated field and ends with _DoCmd.GoToRecord , , acNewRec_
This allows a user to search on either field, paste it in when she is sure she has the right item, and enter in custom information when needed.
Thanks for the start!!!
Rhythmdvl
---
### Author:  [@hardcore](https://boards.straightdope.com/u/hardcore)
#### Post date: [August 13, 2001, 5:57pm UTC](https://boards.straightdope.com/t/nother-access-queston-filling-in-fields-on-a-form-based-on-an-outside-table/76040/5 "2001-08-13T17:57:46Z")
You probably don’t want to hear this, but you shouldn’t have two separate tables tracking the same entity. If you need to know whether an item is missing or not, simply add a ‘Missing’ boolean field to your Inventory table. This will eliminate numerous problems that arise when trying to correlate the data between the two tables.
---
### Author:  [@evilhanz](https://boards.straightdope.com/u/evilhanz)
#### Post date: [August 13, 2001, 6:04pm UTC](https://boards.straightdope.com/t/nother-access-queston-filling-in-fields-on-a-form-based-on-an-outside-table/76040/6 "2001-08-13T18:04:28Z")
This is not a personal criticism, but we see lots and lots of these common MS Access questions. For your benefit and the benefit of others looking for Access help, try exhausting other resources before posting here. For example:
[Dev Ashish’s Access Web](http://www.mvps.org/access/)
comp.databases.ms-access or if you don’t have access to an NNTP server, [comp.databases.ms-access at Google Groups](http://groups.google.com/groups?hl=en&safe=off&group=comp.databases.ms-access)