Filling in fields on a form based on an outside table

[Rhythmdvl]

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

 

[princess15s]

Create a form based on inventory item table and at the top of the table add a combo box, when creating the combo box select (Find a rec. based on the blah blah bla) when you select the item from this list it'll auto populate the feilds on the form.

I hope this works.

else go to www.viescas.com and download the books database and it's got so many examples.

Good luck.

 

[Rhythmdvl]

Getting there…

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](X),[SerialComboBox].[column](X))

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

 

[Rhythmdvl]

Answering my own question

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