Combo box data used to add a new record (2003)

i am building a form and I want to use a combo box to look up items currently in the "Equipment Table" and when you select the item, it copies the selected fields and pastes them into a new record. Essentially since the table is constantly adding similar items, but with different serial numbers, I want to be able to easily copy data from existing table and only have to add the required fields.

Re: Combo box data used to add a new record (2003)

Chances are it never will, it's more to use the existing data to copy and paste so we don't have to type multiple items over and over again. Which leads me to a larger question. in this database i have a personnel table, and Equipment table. Should I use a separate table to show which person is assigned which equipment, or since the table is not really that big, just add the SSN of the person who owns each piece of equipment. Both tables are linked through the SSN at this time.

The reason i bring this up is because i thought about putting a combo box to select from the Equipment Table, and have it put the selection into a 3rd table showing who owned equipment.

Also, when building a combo box, one of the features in the wizard is to have Access save the selection for later use. How and when do you retrieve this data?

Re: Combo box data used to add a new record (2003)

If the user doesn't need to edit the fields from the equipment table, there is no need to copy them. You can create a query based on two (or more) tables linked on the appropriate field, and use that as record source for a form or report.

If the equipment table lists individual equipment items, an item can only belong to a single member of personnel (I suppose). In that case, you can put an SSN field in the equipment table that indicates which person currently uses the item.
But if you want to keep a history of who used which item when, you'd need a separate table that lists the SSN, the equipment number and the time period.

The description in the Combo Box Wizard is slightly misleading. It actually means that you can use the value of the combo box for your own purposes, for example using VBA code. If you don't select a field to store the value in, the value will just be available as long as the form is open, it'll vanish into thin air when the user closes the form.

Re: Combo box data used to add a new record (2003)

Thank you for your quick and patient responses.

One more thing though, i have created the query and it is the source for the combo box. How do i get the fields that are selected in the combo box to populate the fields in the table i want, and in a new record to boot.

Re: Combo box data used to add a new record (2003)

i have created a combo box, the record source is a query based on 2 tables. when i select an item from the combo box, how do i get all the fields from the query to populate fields of a newly inserted record in another table? The function i am performing is to insert a record and the combo box will enter several of the fields for this new record. so how do i get the combo box record i select to fill in the fields in a newly inserted record?