To check all items in a subform

Hi,

I am currently using microsoft access 2003. The problem senario is
this: I am using a form with a subform. In the main form, I have a
checkbox. The subform also has checkboxes. My requirement is when I
check/uncheck the checkbox in the main form, the subform checkboxes
should also be checked/unchecked. How do I go about doing this, as I
am new to VB? Thanks in advanced


Regards,
Zam

2wheels,
Would you describe the logical realtionship between the checkbox
on the Main form, and the check boxes on the Sub form.
The main form check means... and the sub checks mean...

If the sub checkboxes are are * always equal to the main checkbox *,
then there's no reason to have those bound subform check fields. The
relationship between the main and sub tables will associate the sub records
with your main form value.

But... describe your logic, and we can take it from there...
--
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."

"2wheels" wrote in message
...
Hi,

I am currently using microsoft access 2003. The problem senario is
this: I am using a form with a subform. In the main form, I have a
checkbox. The subform also has checkboxes. My requirement is when I
check/uncheck the checkbox in the main form, the subform checkboxes
should also be checked/unchecked. How do I go about doing this, as I
am new to VB? Thanks in advanced


Regards,
Zam

Hi Al,

Below is the description of my forms:

Main form fields (Types of function, Checkbox)
Sub form fields (Components, Types of function, Checkbox)

The primary key in the main form is Types of function. I cannot make
the checkbox in the main form to be the primary key due to non unique
values. Hence the problem mentioned in previous post. The logic is
when I select a type of function in the main form, all components in
the subform with same type of functions will be selected. Appreciate
your prompt response. Thanks


Regards,
Zam

2wheels,
First, please don't delete the previous messages within a thread. We
need
to see the flow of the problem.
And... if a responder top posts or bottom posts... please follow suit
when you reply.
Otherwise the problem determination sequence will get totally screwed up.

Something doesn't appear to make sense...
1. Describe your main form checkbox/checkboxes in detail, and give
examples.
Do you have an option group with multiple check boxes? Or, are the check
boxes each a
separate field value?
2. How is the subform related to the subform? Please show some sample
subform
records... both the fields that might get checked, as well as some that
wont.
(Just the fields we need for this problem...)
Try... "Here's what I have, and here's what I want to do."
--
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."

"2wheels" wrote in message
...
Hi Al,

Below is the description of my forms:

Main form fields (Types of function, Checkbox)
Sub form fields (Components, Types of function, Checkbox)

The primary key in the main form is Types of function. I cannot make
the checkbox in the main form to be the primary key due to non unique
values. Hence the problem mentioned in previous post. The logic is
when I select a type of function in the main form, all components in
the subform with same type of functions will be selected. Appreciate
your prompt response. Thanks


Regards,
Zam

Hi Al,

I am sorry for the confusion in replying to posts.

In the main form/functions table, the fields a function group and
checklist
In the sub form/components table, the fields a components, function
group and checklist)
There is no option group with multiple checkboxes (I am not familiar
with it). Under the field checklist for both tables, I have set the
format to yes/no with checkbox option

Both forms are using tables as the underlying data. The primary key is
function group with one to many relationship (ie one function group
can have many components associated with it).

My main idea was if a user chooses to select a particular function
group by checking it, all the components with that function group will
be selected (ie their respective checkboxes will be checked
automatically. It will be quite tedious to check every single
components (~100) with that particular function group).


Regards,
Zam



On Nov 14, 12:50 am, "Al Campagna" wrote:
2wheels,
First, please don't delete the previous messages within a thread. We
need
to see the flow of the problem.
And... if a responder top posts or bottom posts... please follow suit
when you reply.
Otherwise the problem determination sequence will get totally screwed up.

Something doesn't appear to make sense...
1. Describe your main form checkbox/checkboxes in detail, and give
examples.
Do you have an option group with multiple check boxes? Or, are the check
boxes each a
separate field value?
2. How is the subform related to the subform? Please show some sample
subform
records... both the fields that might get checked, as well as some that
wont.
(Just the fields we need for this problem...)
Try... "Here's what I have, and here's what I want to do."
--
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."

"2wheels" wrote in message

...

Hi Al,

Below is the description of my forms:

Main form fields (Types of function, Checkbox)
Sub form fields (Components, Types of function, Checkbox)

The primary key in the main form is Types of function. I cannot make
the checkbox in the main form to be the primary key due to non unique
values. Hence the problem mentioned in previous post. The logic is
when I select a type of function in the main form, all components in
the subform with same type of functions will be selected. Appreciate
your prompt response. Thanks

Regards,
Zam

2wheels,
We really needed to see some concrete examples... and some actual
control names. Maybe an example just using 2 checkboxes on the main
form, with a few "these would be checked... these would not" example
subform fields.
My main idea was if a user chooses to select a particular function
group by checking it, all the components with that function group will
be selected...


The fact that you indicate there is a FunctionGroup and a Checklist
field
on the main form, AND on the subform too... would lead me to believe that
the table/s design or form design may be incorrect.

But let's set that aside right now...
I can give you "ball-park" solution...
Assuming the subform checkboxes are True/False Boolean...

On the AfterUpdate event of your main form Group checkbox, run an Update
query
against the subform table that changes all the Component checkboxes to
TRUE... for those
Components related to the Main form Group value selected.
--
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."



"2wheels" wrote in message
...
Hi Al,

I am sorry for the confusion in replying to posts.

In the main form/functions table, the fields a function group and
checklist
In the sub form/components table, the fields a components, function
group and checklist)
There is no option group with multiple checkboxes (I am not familiar
with it). Under the field checklist for both tables, I have set the
format to yes/no with checkbox option

Both forms are using tables as the underlying data. The primary key is
function group with one to many relationship (ie one function group
can have many components associated with it).

My main idea was if a user chooses to select a particular function
group by checking it, all the components with that function group will
be selected (ie their respective checkboxes will be checked
automatically. It will be quite tedious to check every single
components (~100) with that particular function group).


Regards,
Zam



On Nov 14, 12:50 am, "Al Campagna" wrote:
2wheels,
First, please don't delete the previous messages within a thread. We
need
to see the flow of the problem.
And... if a responder top posts or bottom posts... please follow suit
when you reply.
Otherwise the problem determination sequence will get totally screwed up.

Something doesn't appear to make sense...
1. Describe your main form checkbox/checkboxes in detail, and give
examples.
Do you have an option group with multiple check boxes? Or, are the check
boxes each a
separate field value?
2. How is the subform related to the subform? Please show some
sample
subform
records... both the fields that might get checked, as well as some that
wont.
(Just the fields we need for this problem...)
Try... "Here's what I have, and here's what I want to do."
--
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."

"2wheels" wrote in message

...

Hi Al,

Below is the description of my forms:

Main form fields (Types of function, Checkbox)
Sub form fields (Components, Types of function, Checkbox)

The primary key in the main form is Types of function. I cannot make
the checkbox in the main form to be the primary key due to non unique
values. Hence the problem mentioned in previous post. The logic is
when I select a type of function in the main form, all components in
the subform with same type of functions will be selected. Appreciate
your prompt response. Thanks

Regards,
Zam

Hi Al,

Sorry for the late response. I only have time to work on the database
today.

I have tried to use update query. It works fine on its own but when I
try to run it in the form (as per your solution), the sub form is not
updated properly. It only updates when I checked another function
group.

Below is the macro I am using to run the update query in the main
form:

OpenQuery Query Name, datasheet view, edit mode
RunCommand Run
RunCommand Refresh


Regards,
Zam


On Nov 15, 4:32*am, "Al Campagna" wrote:
2wheels,
* * We really needed to see some concrete examples... and some actual
control names. *Maybe an example just using 2 checkboxes on the main
form, with a few "these would be checked... these would not" example
subform fields.

My main idea was if a user chooses to select a particular function
group by checking it, all the components with that function group will
be selected...

* * The fact that you indicate there is a FunctionGroup and a Checklist
field
on the main form, AND on the subform too... would lead me to believe that
the table/s design or form design may be incorrect.

* * But let's set that aside right now...
* * I can give you "ball-park" solution...
* * Assuming the subform checkboxes are True/False Boolean...

* * On the AfterUpdate event of your main form Group checkbox, run an Update
query
against the subform table that changes all the Component checkboxes to
TRUE... for those
Components related to the Main form Group value selected.
--
* * 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."

"2wheels" wrote in message

...



Hi Al,

I am sorry for the confusion in replying to posts.

In the main form/functions table, the fields a function group and
checklist
In the sub form/components table, the fields a components, function
group and checklist)
There is no option group with multiple checkboxes (I am not familiar
with it). Under the field checklist for both tables, I have set the
format to yes/no with checkbox option

Both forms are using tables as the underlying data. The primary key is
function group with one to many relationship (ie one function group
can have many components associated with it).

My main idea was if a user chooses to select a particular function
group by checking it, all the components with that function group will
be selected (ie their respective checkboxes will be checked
automatically. It will be quite tedious to check every single
components (~100) with that particular function group).

Regards,
Zam

On Nov 14, 12:50 am, "Al Campagna" wrote:
2wheels,
* * First, please don't delete the previous messages within a thread. *We
need
to see the flow of the problem.
* * And... if a responder top posts or bottom posts... please follow suit
when you reply.
Otherwise the problem determination sequence will get totally screwed up.

* * Something doesn't appear to make sense...
* * 1. Describe your main form checkbox/checkboxes in detail, and give
examples.
Do you have an option group with multiple check boxes? *Or, are the check
boxes each a
separate field value?
* * 2. How is the subform related to the subform? *Please show some
sample
subform
records... both the fields that might get checked, as well as some that
wont.
(Just the fields we need for this problem...)
* * Try... "Here's what I have, and here's what I want to do."
--
* * 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."

"2wheels" wrote in message

....

Hi Al,

Below is the description of my forms:

Main form fields (Types of function, Checkbox)
Sub form fields (Components, Types of function, Checkbox)

The primary key in the main form is Types of function. I cannot make
the checkbox in the main form to be the primary key due to non unique
values. Hence the problem mentioned in previous post. The logic is
when I select a type of function in the main form, all components in
the subform with same type of functions will be selected. Appreciate
your prompt response. Thanks

Regards,
Zam- Hide quoted text -

- Show quoted text -

2wheels,
Try a Refresh just before you run the query.
If you check a few boxes, and then run the query, the values you just
changed may not have updated into the table yet.

I just tested a macro that runs an update query, and it works.
I think our macro code is the same...
I used my own object names... you use yours...
RunCommand
Refresh
OpenQuery
qryUpdatePrintNone
Datasheet
Edit
RunCommand
Refresh
--
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."

"2wheels" wrote in message
...
Hi Al,

Sorry for the late response. I only have time to work on the database
today.

I have tried to use update query. It works fine on its own but when I
try to run it in the form (as per your solution), the sub form is not
updated properly. It only updates when I checked another function
group.

Below is the macro I am using to run the update query in the main
form:

OpenQuery Query Name, datasheet view, edit mode
RunCommand Run
RunCommand Refresh


Regards,
Zam


On Nov 15, 4:32 am, "Al Campagna" wrote:
2wheels,
We really needed to see some concrete examples... and some actual
control names. Maybe an example just using 2 checkboxes on the main
form, with a few "these would be checked... these would not" example
subform fields.

My main idea was if a user chooses to select a particular function
group by checking it, all the components with that function group will
be selected...

The fact that you indicate there is a FunctionGroup and a Checklist
field
on the main form, AND on the subform too... would lead me to believe that
the table/s design or form design may be incorrect.

But let's set that aside right now...
I can give you "ball-park" solution...
Assuming the subform checkboxes are True/False Boolean...

On the AfterUpdate event of your main form Group checkbox, run an Update
query
against the subform table that changes all the Component checkboxes to
TRUE... for those
Components related to the Main form Group value selected.
--
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."

"2wheels" wrote in message

...



Hi Al,

I am sorry for the confusion in replying to posts.

In the main form/functions table, the fields a function group and
checklist
In the sub form/components table, the fields a components, function
group and checklist)
There is no option group with multiple checkboxes (I am not familiar
with it). Under the field checklist for both tables, I have set the
format to yes/no with checkbox option

Both forms are using tables as the underlying data. The primary key is
function group with one to many relationship (ie one function group
can have many components associated with it).

My main idea was if a user chooses to select a particular function
group by checking it, all the components with that function group will
be selected (ie their respective checkboxes will be checked
automatically. It will be quite tedious to check every single
components (~100) with that particular function group).

Regards,
Zam

On Nov 14, 12:50 am, "Al Campagna" wrote:
2wheels,
First, please don't delete the previous messages within a thread. We
need
to see the flow of the problem.
And... if a responder top posts or bottom posts... please follow suit
when you reply.
Otherwise the problem determination sequence will get totally screwed
up.

Something doesn't appear to make sense...
1. Describe your main form checkbox/checkboxes in detail, and give
examples.
Do you have an option group with multiple check boxes? Or, are the
check
boxes each a
separate field value?
2. How is the subform related to the subform? Please show some
sample
subform
records... both the fields that might get checked, as well as some that
wont.
(Just the fields we need for this problem...)
Try... "Here's what I have, and here's what I want to do."
--
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."

"2wheels" wrote in message

...

Hi Al,

Below is the description of my forms:

Main form fields (Types of function, Checkbox)
Sub form fields (Components, Types of function, Checkbox)

The primary key in the main form is Types of function. I cannot make
the checkbox in the main form to be the primary key due to non unique
values. Hence the problem mentioned in previous post. The logic is
when I select a type of function in the main form, all components in
the subform with same type of functions will be selected. Appreciate
your prompt response. Thanks

Regards,
Zam- Hide quoted text -

- Show quoted text -

Hi Al,

It works fine now! How a simple line can do wonders
Thank you very much for your help. I really appreciate it.
Btw do you know any sites where I can brush up on my database skills?
I am limited due to my poor vba skills.


Regards,
Zam

On Nov 19, 2:28*am, "Al Campagna" wrote:
2wheels,
* * Try a Refresh just before you run the query.
* * If you check a few boxes, and then run the query, the values you just
changed may not have updated into the table yet.

* * I just tested a macro that runs an update query, and it works.
* * I think our macro code is the same...
* * I used my own object names... you use yours...
* * * * RunCommand
* * * * * * Refresh
* * * * OpenQuery
* * * * * * qryUpdatePrintNone
* * * * * * Datasheet
* * * * * * Edit
* * * * RunCommand
* * * * * * Refresh
--
* * 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."

"2wheels" wrote in message

...
Hi Al,

Sorry for the late response. I only have time to work on the database
today.

I have tried to use update query. It works fine on its own but when I
try to run it in the form (as per your solution), the sub form is not
updated properly. It only updates when I checked another function
group.

Below is the macro I am using to run the update query in the main
form:

OpenQuery Query Name, datasheet view, edit mode
RunCommand Run
RunCommand Refresh

Regards,
Zam

On Nov 15, 4:32 am, "Al Campagna" wrote:



2wheels,
We really needed to see some concrete examples... and some actual
control names. Maybe an example just using 2 checkboxes on the main
form, with a few "these would be checked... these would not" example
subform fields.

My main idea was if a user chooses to select a particular function
group by checking it, all the components with that function group will
be selected...

The fact that you indicate there is a FunctionGroup and a Checklist
field
on the main form, AND on the subform too... would lead me to believe that
the table/s design or form design may be incorrect.

But let's set that aside right now...
I can give you "ball-park" solution...
Assuming the subform checkboxes are True/False Boolean...

On the AfterUpdate event of your main form Group checkbox, run an Update
query
against the subform table that changes all the Component checkboxes to
TRUE... for those
Components related to the Main form Group value selected.
--
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."

"2wheels" wrote in message

...

Hi Al,

I am sorry for the confusion in replying to posts.

In the main form/functions table, the fields a function group and
checklist
In the sub form/components table, the fields a components, function
group and checklist)
There is no option group with multiple checkboxes (I am not familiar
with it). Under the field checklist for both tables, I have set the
format to yes/no with checkbox option

Both forms are using tables as the underlying data. The primary key is
function group with one to many relationship (ie one function group
can have many components associated with it).

My main idea was if a user chooses to select a particular function
group by checking it, all the components with that function group will
be selected (ie their respective checkboxes will be checked
automatically. It will be quite tedious to check every single
components (~100) with that particular function group).

Regards,
Zam

On Nov 14, 12:50 am, "Al Campagna" wrote:
2wheels,
First, please don't delete the previous messages within a thread. We
need
to see the flow of the problem.
And... if a responder top posts or bottom posts... please follow suit
when you reply.
Otherwise the problem determination sequence will get totally screwed
up.

Something doesn't appear to make sense...
1. Describe your main form checkbox/checkboxes in detail, and give
examples.
Do you have an option group with multiple check boxes? Or, are the
check
boxes each a
separate field value?
2. How is the subform related to the subform? Please show some
sample
subform
records... both the fields that might get checked, as well as some that
wont.
(Just the fields we need for this problem...)
Try... "Here's what I have, and here's what I want to do."
--
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."

"2wheels" wrote in message

...

Hi Al,

Below is the description of my forms:

Main form fields (Types of function, Checkbox)
Sub form fields (Components, Types of function, Checkbox)

The primary key in the main form is Types of function. I cannot make
the checkbox in the main form to be the primary key due to non unique
values. Hence the problem mentioned in previous post. The logic is
when I select a type of function in the main form, all components in
the subform with same type of functions will be selected. Appreciate
your prompt response. Thanks

Regards,
Zam- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

2Wheels,
I guess I'm old school on that. Just a reasonablly good beginner
to intermediate reference book for your version of Access, and plenty of
"sweat equity." Just get in there and build apps. Fun apps for yourself
(Address book, CD/DVD Library, Time Tracker/Billing, etc...), and just
build and experiment... build and experiment.

Use the newsgroups when you need specific help, but... also...take some
time each day to look over other folk's questions and see the responses.
You'll be surprised at the things you'll pick up... even if you might not be
dealing
with those particular issues at the time.
The NGs are a very valuable resource...

Q: "How do I get to Carnegie Hall?"
A: "Practice... practice... practice... :-D
--
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."

"2wheels" wrote in message
...
Hi Al,

It works fine now! How a simple line can do wonders
Thank you very much for your help. I really appreciate it.
Btw do you know any sites where I can brush up on my database skills?
I am limited due to my poor vba skills.


Regards,
Zam

On Nov 19, 2:28 am, "Al Campagna" wrote:
2wheels,
Try a Refresh just before you run the query.
If you check a few boxes, and then run the query, the values you just
changed may not have updated into the table yet.

I just tested a macro that runs an update query, and it works.
I think our macro code is the same...
I used my own object names... you use yours...
RunCommand
Refresh
OpenQuery
qryUpdatePrintNone
Datasheet
Edit
RunCommand
Refresh
--
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."

"2wheels" wrote in message

...
Hi Al,

Sorry for the late response. I only have time to work on the database
today.

I have tried to use update query. It works fine on its own but when I
try to run it in the form (as per your solution), the sub form is not
updated properly. It only updates when I checked another function
group.

Below is the macro I am using to run the update query in the main
form:

OpenQuery Query Name, datasheet view, edit mode
RunCommand Run
RunCommand Refresh

Regards,
Zam

On Nov 15, 4:32 am, "Al Campagna" wrote:



2wheels,
We really needed to see some concrete examples... and some actual
control names. Maybe an example just using 2 checkboxes on the main
form, with a few "these would be checked... these would not" example
subform fields.

My main idea was if a user chooses to select a particular function
group by checking it, all the components with that function group will
be selected...

The fact that you indicate there is a FunctionGroup and a Checklist
field
on the main form, AND on the subform too... would lead me to believe
that
the table/s design or form design may be incorrect.

But let's set that aside right now...
I can give you "ball-park" solution...
Assuming the subform checkboxes are True/False Boolean...

On the AfterUpdate event of your main form Group checkbox, run an Update
query
against the subform table that changes all the Component checkboxes to
TRUE... for those
Components related to the Main form Group value selected.
--
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."

"2wheels" wrote in message

...

Hi Al,

I am sorry for the confusion in replying to posts.

In the main form/functions table, the fields a function group and
checklist
In the sub form/components table, the fields a components, function
group and checklist)
There is no option group with multiple checkboxes (I am not familiar
with it). Under the field checklist for both tables, I have set the
format to yes/no with checkbox option

Both forms are using tables as the underlying data. The primary key is
function group with one to many relationship (ie one function group
can have many components associated with it).

My main idea was if a user chooses to select a particular function
group by checking it, all the components with that function group will
be selected (ie their respective checkboxes will be checked
automatically. It will be quite tedious to check every single
components (~100) with that particular function group).

Regards,
Zam

On Nov 14, 12:50 am, "Al Campagna" wrote:
2wheels,
First, please don't delete the previous messages within a thread. We
need
to see the flow of the problem.
And... if a responder top posts or bottom posts... please follow suit
when you reply.
Otherwise the problem determination sequence will get totally screwed
up.

Something doesn't appear to make sense...
1. Describe your main form checkbox/checkboxes in detail, and give
examples.
Do you have an option group with multiple check boxes? Or, are the
check
boxes each a
separate field value?
2. How is the subform related to the subform? Please show some
sample
subform
records... both the fields that might get checked, as well as some
that
wont.
(Just the fields we need for this problem...)
Try... "Here's what I have, and here's what I want to do."
--
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."

"2wheels" wrote in message

...

Hi Al,

Below is the description of my forms:

Main form fields (Types of function, Checkbox)
Sub form fields (Components, Types of function, Checkbox)

The primary key in the main form is Types of function. I cannot
make
the checkbox in the main form to be the primary key due to non
unique
values. Hence the problem mentioned in previous post. The logic is
when I select a type of function in the main form, all components
in
the subform with same type of functions will be selected.
Appreciate
your prompt response. Thanks

Regards,
Zam- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -