Creating detail records in a form from table query

I am developing a contracts tracking database. Many contracts share a common set of deliverables. I have created a table that stores the list of deliverables that are common to many contracts. In a form I would like to select the deliverable class from the DeliverablesDefinition table which stores the individual lines to be created for a given contract. I would like to have a button on the form that will read the selected deliverable class, query the deliverable class table and insert the specified rows into the ContractsDeliverable table. In effect I am trying to pre-fill the form with a set of records queried from another table where the select critera are defined in the form.

Hi jfalexan,

I would create a combobox that has a rowsource set to your table DeliverablesDefinition. Include all the fields that you want to add to your form. Then in the AfterUpdate of the combobox, you can add some basic code:

Field1.Value = combobox.Column(1)
Field2.Value = combobox.Column(2)

Typically the first column of the combobox is your Primary ID and it will always be Column (0)

This would fill in the fields on your form with the desired information from your table based on what was selected in the combobox.

lwells

lwells,

Thanks for the reply. The portion of the form that I am trying to fill is a subform that is in datasheet form and can have n-number of rows. Each row has about 10 columns. I thought that it would be easiest to click a button that would insert records into the table and then requery the subform. I am not sure how to write the insert into statment (I know the syntax for the the statement) into an event, if this is the right approach. This is my first time working with access forms and events.

Jim.

Hi Jim,

Understand. Are you trying to inserting multiple records at one time or is this just one record inserted into your subform after you make your selection? Either way can be done.

What I would do is to test out using the append query method. First create the query that will have the records based on whatever you select from your form. Use this query in the append query to insert the records into the table that your subform is based on. The button on your form would execute this query and then requery the subform to reflect the new records that were added.

If you would rather have all this in vba code without the physical query objects, it can be done by making a temporary recordset of the records based on your criteria and then inserting those records into a recordset of the table. This method is a little more complex, but can be done that way as well.

lwells

lwells,



Thanks for the additional information. I will look at this later today. I truly appreciate your time and expertice. I understood all of the concepts you presented. Execution may be another story.

Thanks again,

Jim.