List Box to populate subform table (1 Viewer)

[msybarra]

I have two tables in my database

Contract Table
Site Table

The tables are linked. Each contract needs a specific number of sites.

I created a form using the contract table and added a subform for the sites. How can I populate the subform table by selecting specific sites from the "site table"? e.g. specifically adding 10 sites from the "Site table" to subform in my contract form. I dont want to have to retype everything.

Is it possible to create a list box that will populate the subform?

 

[jdraw]

It sounds like you are trying to add records to the "child" table without having created a "parent" record.
Are these 10 sites all related to 1 contract?

 

[msybarra]

Correct. The sites are the child records and specific to the one contract.

I have a site table with over 300 sites, but I want to create a feature that allows the user to pick sites based on location, for example, to add to the subform specific to the contract. One contract can have multiple sites. I don't want to make the user retype fields into the subform.

I've seen other databases were they have an "Add" button to the subform header that opens up a list box then allows individuals to select sites which are then added to the subform by hitting "add sites".

 

[jdraw]

So, if I understand, you have a Contract Table with some records and a Site Table with 300 records.
You could have an additional table ContractHasSite or similar.

TblContract-->tblContractHasSite<--tblSite

On a form frmAssignSiteToContract or similar, you have a combobox with the ContractInfo aand a Listbox with the site info and for simplicity a button.
When you select a Contract, then select the associated Sites for that Contract, then click the button. Behind the button is vba code to process all the selected data and add the contract ID (from the combo) and each of the Sites selected in the listbox and insert these into the table ContractHasSite.
I'm sure there are many ways to accomplish what you want without having to retype things.

There is a similar requirement (combo selection then multiple selections from listbox)
in this thread. See the mock up form and discussion.

Good luck.

 

[msybarra]

Interesting. I'll need to think about this. Thank you. I appreciate the feedback.

 

[msybarra]

Do you have a general VBA code that combines items selected from a list box and combo box which get inserted into a table?

 

[jdraw]

As mentioned in earlier thread, there is a similar requirement (combo selection then multiple selections from listbox)
in this thread. See the mock up form and discussion.
The combo box contains the CustomersLawns. Once one is selected, then move to the listbox.
The listbox contains the tasks to be performed.Select 1 or more tasks.
The vba code behind the AssignTasks button click event is provided provided in the attached txt file in post #13. There are comments within the vba code.

I have no heard back from the original poster.

 

[msybarra]

Thank you! I didn't see the thread link in your first response. Thank you.