Requery Method - Subform combo

M

Mary

I apologize, but after reading several posts, I'm still having trouble with
the requery method.

Main form: UpdateTask
Combobox on main form - ControlName: Activity

Sub form: AddActivitytoTask
Combobox on sub form - cboSubtask

I have the After Update event of the Activity control set to:

Private Sub Activity_AfterUpdate()
Me.cboSubtask.Requery
End Sub

I want to see only subtasks related to the Activity. I'm still seeing all
subtasks. The Subtask table has an Activity field that is related to the
ActivityType field in the ActivityType table.

Thanks, Mary
 
M

Mary

Steve,

Now I get an error "can't find the field AddActivitytoTask". That's the
name of the subform.

Thanks, Mary
 
S

Steve Schapel

Mary,

The code needs to refer to the name of the *subform control* on the main
UpdateTask form, which is not necessarily the same as the name of the
form which you use as the source for the subform control. Please check
this, and post back if it still doesn't work.
 
M

Mary

Well, I don't get the error now, but it doesn't requery either.

Does this help show what I'm doing wrong:

Private Sub cboActivity_AfterUpdate()
Me.AddActivitytoTask.Form!cboSubtask.Requery
End Sub

AddActivitytoTask is the *Name* of the subform.
cboSubtask is the *Name* of the combo box on the subform that I want to
requery.

Mainform combo:
Name: cboActivity
Control Source: Task Activity
Row Source: SELECT [Task Activity].Activity FROM [Task Activity] ORDER BY
[Task Activity].Activity;
Bound column: 1

Subfrom combo:
Control Source: Subtask
Row Source: SELECT TaskSubtasks.ActivityType, TaskSubtasks.Subtask FROM
TaskSubtasks ORDER BY TaskSubtasks.ActivityType, TaskSubtasks.Subtask;
Bound Column: 2

Thanks, Mary
 
S

Steve Schapel

Mary,

No, not really. When you say that AddActivitytoTask is the Name of the
subform, do you mean the nam,e of the form which is being used as the
subform, or do you mean the name of the box on the main form where the
subform is displayed? These are often the same as each other, but it is
not necessarily so. If they are different, it is the name of the box on
the main form where the subform is displayed that should be used in the
code.

And what is TaskSubtasks? For this to work, TaskSubtasks would need to
be a query, where the value of the cboActivity on the main form is being
referenced in the criteria of the (presumably) Task Activity field.
 
M

Mary

Sorry, on the subform combo, the bound column is 1.

Mary said:
Well, I don't get the error now, but it doesn't requery either.

Does this help show what I'm doing wrong:

Private Sub cboActivity_AfterUpdate()
Me.AddActivitytoTask.Form!cboSubtask.Requery
End Sub

AddActivitytoTask is the *Name* of the subform.
cboSubtask is the *Name* of the combo box on the subform that I want to
requery.

Mainform combo:
Name: cboActivity
Control Source: Task Activity
Row Source: SELECT [Task Activity].Activity FROM [Task Activity] ORDER BY
[Task Activity].Activity;
Bound column: 1

Subfrom combo:
Control Source: Subtask
Row Source: SELECT TaskSubtasks.ActivityType, TaskSubtasks.Subtask FROM
TaskSubtasks ORDER BY TaskSubtasks.ActivityType, TaskSubtasks.Subtask;
Bound Column: 2

Thanks, Mary


Steve Schapel said:
Mary,

The code needs to refer to the name of the *subform control* on the main
UpdateTask form, which is not necessarily the same as the name of the
form which you use as the source for the subform control. Please check
this, and post back if it still doesn't work.
Click to expand...
Click to expand...
 
S

Steve Schapel

Mary,

The bound column of the subform combo doesn't have any bearing on this
problem. The relationship between the main form combobox and the
subform combobox is the key.
 
M

Mary

They're different, but it's the name of the box. The actual form name is the
same but with spaces between each word.

TaskSubtasks is a table. I changed that to Subtasks which is a query with
the two tables that have the value of Task Activity in common. I also tried
using the same query for both combo boxes, and it still doesn't requery.


Steve Schapel said:
Mary,

No, not really. When you say that AddActivitytoTask is the Name of the
subform, do you mean the nam,e of the form which is being used as the
subform, or do you mean the name of the box on the main form where the
subform is displayed? These are often the same as each other, but it is
not necessarily so. If they are different, it is the name of the box on
the main form where the subform is displayed that should be used in the
code.

And what is TaskSubtasks? For this to work, TaskSubtasks would need to
be a query, where the value of the cboActivity on the main form is being
referenced in the criteria of the (presumably) Task Activity field.

--
Steve Schapel, Microsoft Access MVP

Well, I don't get the error now, but it doesn't requery either.

Does this help show what I'm doing wrong:

Private Sub cboActivity_AfterUpdate()
Me.AddActivitytoTask.Form!cboSubtask.Requery
End Sub

AddActivitytoTask is the *Name* of the subform.
cboSubtask is the *Name* of the combo box on the subform that I want to
requery.

Mainform combo:
Name: cboActivity
Control Source: Task Activity
Row Source: SELECT [Task Activity].Activity FROM [Task Activity] ORDER BY
[Task Activity].Activity;
Bound column: 1

Subfrom combo:
Control Source: Subtask
Row Source: SELECT TaskSubtasks.ActivityType, TaskSubtasks.Subtask FROM
TaskSubtasks ORDER BY TaskSubtasks.ActivityType, TaskSubtasks.Subtask;
Bound Column: 2

Thanks, Mary
Click to expand...
Click to expand...
 
S

Steve Schapel

Mary,

How is the subform combobox supposed to know which Task Activity you
want it to be restricted to? If I understand you correctly, the Row
Source of the subform combobox should be something like this...
SELECT TaskSubtasks.ActivityType, TaskSubtasks.Subtask
FROM TaskSubtasks
WHERE TaskSubtasks.[Task Activity]=[Forms]![UpdateTask]![Task Activity]
ORDER BY TaskSubtasks.ActivityType, TaskSubtasks.Subtask
 
M

Mary

That's what I was missing, thank you!! Works like a charm! Thanks for
taking the time to solve my problem. I really appreciate it!!

Steve Schapel said:
Mary,

How is the subform combobox supposed to know which Task Activity you
want it to be restricted to? If I understand you correctly, the Row
Source of the subform combobox should be something like this...
SELECT TaskSubtasks.ActivityType, TaskSubtasks.Subtask
FROM TaskSubtasks
WHERE TaskSubtasks.[Task Activity]=[Forms]![UpdateTask]![Task Activity]
ORDER BY TaskSubtasks.ActivityType, TaskSubtasks.Subtask

--
Steve Schapel, Microsoft Access MVP

They're different, but it's the name of the box. The actual form name is the
same but with spaces between each word.

TaskSubtasks is a table. I changed that to Subtasks which is a query with
the two tables that have the value of Task Activity in common. I also tried
using the same query for both combo boxes, and it still doesn't requery.
Click to expand...
Click to expand...
 
Top