Form/Subform Checkbox

[Minnie M]

In Access 2002 I have a form ‘Employee’ and a sub form ‘Training’ that lists
all the training courses that the employee has taken. There’s a checkbox on
both forms called ‘Left’ but the user doesn’t want to go through the sub
forms and check them all individually. I therefore need to link the forms so
that when the user checks the ‘Left’ box on the employee form, the ‘Left’ box
on all the sub forms for that employee also checks.

I’d really appreciate some help on how to do this.

 

[Al Campagna]

Minnie,

on "all the sub forms" for that employee also checks.

I think you mean that when you select Left on the main form, all the
"subform records" (associated with that employee) will be checked True.
We should be bealing with only one subform...

Your main form should have a unique key value that relates to a key
value in the subform table. Like an EmployeeID...
Using the AfterUpdate event of the main form's Left checkbox...
If Left = True... then run an Update query against the subform table
that sets all the Left values to True in the subform table... **where the
EmployeeID is equal to the EmployeeID on the main form**
If Left = False, run an Update query against the subform table that does
the opposite.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Minnie M]

Hi Al, sorry about the incorrect terminology in the question, you’re correct
about what you think I meant.
I still don’t completely understand what I need to do, I don’t suppose you
could give me an idiot guide could you? I’m new to writing code and am not
sure what to type and where to type it, it’s quite confusing for a newbie.
I’d really appreciate if you could give me a little more help. Thanks ever so
much.

 

[Al Campagna]

Minnie,
First rule regarding Update queries... back up your original data!

What is the name of your Main form, and your Subform?
What is the name of the check field on the Main form.
What is the field name, on the subform, that we want to set to true?
What is the name of the table that your subform is based on?
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Minnie M]

Hi Al, thanks for the reminder to back up my data, no problem, I’m working on
a copy of the db.
To answer your questions:
Main form is Employee Information Form, subform is Training Form
The name of the check field on the main form is Left
The field name on the subform to be set to true is Left Company
The table that the subform is based on is Training
Many thanks for your help.

 

[Al Campagna]

Minnie,
I forgot to ask what your key (Parent/Child) field on the Main and Sub
is... so I'll just use EmpID for this example... you use your own name/s.

Create an Update query.
Open a new Query design grid, and select Query/Update from the query
menu.
Place your LeftCompany field on the grid, and fill in the UpdateTo for
LeftCompany to...
True
Place your key field EmpID from Training onto the grid, and give it a
criteria of...
=Forms![Employee Information Form]![EmpID]
Save this query as qryLeftCompanyTrue.
This query will update every Training record to True... that has an
EmpID equal to that of the EmpID on your open form.

Why not just create a button on the Main form called
cmdSetTrainingToTrue.
Any time this button is clicked, it sets all the associated Traing
records to True.
Using the OnClick event of the button, use this code...

Private Sub cmdSetTrainingToTrue_Click()
DoCmd.OpenQuery "qryLeftCompanyTrue"
End Sub
OR...
If your more comfortable with Macros... just use the OpenQuery Action
instead.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Minnie M]

Thanks Al, I’ve followed your instructions but I must have done something
wrong as I get the following ‘Compile error: Syntax error’ message when I
click the ‘Left’ checkbox on my main form:

On the visual basic screen, the top left hand box is set to Left, the top
right hand box is set to After Update, the following is in the text part of
the screen:
Option Compare Database
Option Explicit

Private Sub Left_AfterUpdate() (this line is highlighted in yellow with a
yellow arrow pointing to it from the left)
If Left = True (this line is highlighted)
End Sub

Private Sub cmdSetTrainingToTrue_Click()
DoCmd.OpenQuery "qryLeftCompanyTrue"
End Sub

Hope this makes sense to you, and I’d really appreciate a little more help.

Minnie,
I forgot to ask what your key (Parent/Child) field on the Main and Sub
is... so I'll just use EmpID for this example... you use your own name/s.

Create an Update query.
Open a new Query design grid, and select Query/Update from the query
menu.
Place your LeftCompany field on the grid, and fill in the UpdateTo for
LeftCompany to...
True
Place your key field EmpID from Training onto the grid, and give it a
criteria of...
=Forms![Employee Information Form]![EmpID]
Save this query as qryLeftCompanyTrue.
This query will update every Training record to True... that has an
EmpID equal to that of the EmpID on your open form.

Why not just create a button on the Main form called
cmdSetTrainingToTrue.
Any time this button is clicked, it sets all the associated Traing
records to True.
Using the OnClick event of the button, use this code...

Private Sub cmdSetTrainingToTrue_Click()
DoCmd.OpenQuery "qryLeftCompanyTrue"
End Sub
OR...
If your more comfortable with Macros... just use the OpenQuery Action
instead.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Hi Al, thanks for the reminder to back up my data, no problem, I'm working
on
a copy of the db.
To answer your questions:
Main form is Employee Information Form, subform is Training Form
The name of the check field on the main form is Left
The field name on the subform to be set to true is Left Company
The table that the subform is based on is Training
Many thanks for your help.