OK, before I got to the form & code, I did some cleanup with your tables/fields. First, I created autonumber primary keys for all tables that did not have them to make all of the tables consistent. I used these primary key fields to join to their respective foreign key fields in related tables as necessary.
Second, I noticed that you had several lookup (combo/list boxes) fields in your tables. Although Access has this capability, having them in your tables can cause several issues. This site details those issues. I moved the value lists that you had tied to those lookup fields to their own separate tables and made the necessary joins back to the original tables from which they were referenced.
Third, I removed all of the spaces and special characters from your table and field names because having them forces you to enclose those names in square brackets which becomes a lot of extra typing in queries and code. Failing to enclose them in square brackets causes execution errors with queries and code.
I removed/altered some of your data so that I only had a small dataset for test purposes.
In the form that opens when you open the database, you must first select the discipline from the combo box. You will then need to select one or more items from both lists and then click the button to append the same required work selections to every selected subcontractor. After the append completes, another form (with a main form/subform setup) is opened where you can add the details of each quote for each required work/subcontractor combination. Adding the details of each quote cannot be automated since the details are variables. I should also note that I locked the Work Item Number and Reference number controls in the subform because I based the subform on a query that included multiple tables. If I had left those controls unlocked, things would probably not work correctly since that information is coming in from related tables. I just show the information so you have the details to which the quote applies. If you want to add new items manually, I would recommend creating another form for that.
The revised database is attached.
By the way, one possible reason why you could not post your original database was because of it's size (64MB). If you are going to post a database, you need to run the compact and repair utility from Access's tools menu and then zip the file before posting. When I ran the compact and repair utility, the size of the DB was reduced from 64MB to <2MB.
Second, I noticed that you had several lookup (combo/list boxes) fields in your tables. Although Access has this capability, having them in your tables can cause several issues. This site details those issues. I moved the value lists that you had tied to those lookup fields to their own separate tables and made the necessary joins back to the original tables from which they were referenced.
Third, I removed all of the spaces and special characters from your table and field names because having them forces you to enclose those names in square brackets which becomes a lot of extra typing in queries and code. Failing to enclose them in square brackets causes execution errors with queries and code.
I removed/altered some of your data so that I only had a small dataset for test purposes.
In the form that opens when you open the database, you must first select the discipline from the combo box. You will then need to select one or more items from both lists and then click the button to append the same required work selections to every selected subcontractor. After the append completes, another form (with a main form/subform setup) is opened where you can add the details of each quote for each required work/subcontractor combination. Adding the details of each quote cannot be automated since the details are variables. I should also note that I locked the Work Item Number and Reference number controls in the subform because I based the subform on a query that included multiple tables. If I had left those controls unlocked, things would probably not work correctly since that information is coming in from related tables. I just show the information so you have the details to which the quote applies. If you want to add new items manually, I would recommend creating another form for that.
The revised database is attached.
By the way, one possible reason why you could not post your original database was because of it's size (64MB). If you are going to post a database, you need to run the compact and repair utility from Access's tools menu and then zip the file before posting. When I ran the compact and repair utility, the size of the DB was reduced from 64MB to <2MB.
Last edited:
