MS Access Subform field automatically

Question

I have a MS Access form and subform, the subform is linked to the Form, while for each project (Form) there is 3 type of visits (Monthly, Quarterly & Annually). What I require that for each project, I want the subform to show the 3 options automatically (in the combo box), and the user should fill the FF Hours and FA Hours Subform

Answer 1

Good day, You can achieve it by the following steps.

1. Create a table to store the visits(add three records for monthly, quarterly, and annual). In my own case, i called the table projectvisit.

2. For the main form, in my example i created a table called site

3. For the subform, i created a table called sitedetails

4. Then i created an insert query that will insert 3 records(from the projectvisit table into the visittype field in sitedetails table, for the active main form record)

5. I create a macro that has 3 actions A. Open query(run the insert query) B. Gotonextrecord C. Goto previousrecord.

6. Finally i added a command button to the mainform that runs the macro.

Find attached images of the tables, query, form,and macro. You can modify it to meet your needs.

Do take note that before the command button will run the macro, you must have populated the current record of the main form.

Also, go to access options,advanced tab and uncheck action queries.