Form/Subform Checkbox

Hi Al, the associated records are still not updating (sigh).

I had set a command button as you suggested but I think the problem is that
I don’t understand what I’m doing, so although I copied and pasted your codes
I must have made other mistakes or missed something out.

Many thanks for all your help anyway, I appreciate the time you’ve spent
trying to help me.

"Al Campagna" wrote:

Minnie,
I had suggested using a button to trigger the query qryLeftCompanyTrue,
but I'll use a checkbox if you insist...
Any time you have problems with code, please copy (cut & paste) your code
exactly to your post.

Private Sub LeftCompany_AfterUpdate()
If LeftCompany = True Then
DoCmd.OpenQuery "qryLeftCompanyTrue"
End If
End Sub
--
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" wrote in message
...
Hi Al, changed the field name to LeftCompany as you suggested. Its now
asking
for a Then or Else statement after If LeftCompany = True.

Hope you can be a star once again.

"Al Campagna" wrote:

Minnie,
Don't name your field "Left". Left is a string function in Access,
and
as such... is a reserved word.
Rename your check field to something like LeftCompany.
--
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" wrote in message
...
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.

"Al Campagna" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
news 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.

Minnie,
What version of Access are you using?
Can you send me the database, using the Contact feature of my website
(below)?
If you do send the database please indicate what form is involved, and
what subform is involved.
Also, include the word "newsgroup" in your email "subject." I have a
pretty tough spam filter, so if you do send the file/s, please add a post to
this thread indicating that you have done so, and I'll be on the lookout for
it.
--
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" wrote in message
...
Hi Al, the associated records are still not updating (sigh).

I had set a command button as you suggested but I think the problem is
that
I don't understand what I'm doing, so although I copied and pasted your
codes
I must have made other mistakes or missed something out.

Many thanks for all your help anyway, I appreciate the time you've spent
trying to help me.

"Al Campagna" wrote:

Minnie,
I had suggested using a button to trigger the query
qryLeftCompanyTrue,
but I'll use a checkbox if you insist...
Any time you have problems with code, please copy (cut & paste) your
code
exactly to your post.

Private Sub LeftCompany_AfterUpdate()
If LeftCompany = True Then
DoCmd.OpenQuery "qryLeftCompanyTrue"
End If
End Sub
--
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" wrote in message
...
Hi Al, changed the field name to LeftCompany as you suggested. Its now
asking
for a Then or Else statement after If LeftCompany = True.

Hope you can be a star once again.

"Al Campagna" wrote:

Minnie,
Don't name your field "Left". Left is a string function in
Access,
and
as such... is a reserved word.
Rename your check field to something like LeftCompany.
--
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" wrote in message
...
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.

"Al Campagna" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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" wrote in
message
news 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.