'nother Access queston: Filling in fields on a form based on an outside table

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

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 who have a message board specificly for Access Questions, as well as other computr realted subjects.

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

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

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.

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
comp.databases.ms-access or if you don’t have access to an NNTP server, comp.databases.ms-access at Google Groups