Subform problem

[Jack Sheet]

Hi all, Access 97

Nearly there.
I now have two tables T_Clients and T_Tasks containing autonumber primary
keys ID_Clients and ID_Tasks.
ID_Clients is duplicated in T_Tasks (Long Integer) with a one-to-many
relationship.

I have created forms F_Tasks and F_Tasks_Sub
F_Tasks collects data from T_Clients
F_Tasks_sub collects data from T_Tasks and is a subform within F_Tasks.

In F_Tasks I include a combo box to select clients as an alternative to the
navigation bar. This works fine.

In F_Tasks_Sub I include a combo box to select tasks as an alternative to
the navigation bar. This does not work, because the drop-down box does NOT
limit the selection to just those tasks for which ID_Clients matches the
selection in the parent form.

The code behind the (failing) combo box within the sub form is

Option Compare Database
Option Explicit
Private Sub Form_Current()
Combo72 = Null
End Sub
Sub Combo72_AfterUpdate()
' Find the record that matches the control.
If Len(Me![Combo72] & "") > 0 Then
Me.RecordsetClone.FindFirst "[ID_Tasks] = " & Me![Combo72]
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End Sub

This is identical to the code behind the combo box in the parent form except
that the combo box number is different and for ID_Tasks it says ID_Clients.

SO: How do I get the drop-down list in the combo box in teh subform to limit
the available options only to those records within T_Tasks for which the
ID_Clients field matches the selection in the parent form?

Currently the RowSource property is set to:
SELECT DISTINCTROW [T_Tasks].[ID_Tasks], [T_Tasks].[Task], [T_Tasks].[End]
FROM [T_Tasks];
and Limit to List is set to Yes

Thanks

 

[Jack Sheet]

Presumably the question that I should be asking is this:
In an SQL statement that specifies the RowSource property of the Combo box
in the sub-form, what is the syntax to reference the value of ID_Clients of
the selected record in the parent form?

I toyed with something like

SELECT DISTINCTROW [T_Tasks].[ID_Tasks], [T_Tasks].[Task], [T_Tasks].[End]
FROM [T_Tasks] WHERE ([T_Tasks].[ID_Clients] = [T_Clients].[ID_Clients]);

But of course that didn't have any effect (I didin't seriously expect it
to - the relationship between T_Clients.ID_Clients and T_Tasks.ID_Clients
requires that they must always be equal so the test for equality will always
result as True). I am wondering whether the value of ID_Clients in the
parent form is a local variable that cannot be passed to the subform.

 

[John Vinson]

what is the syntax to reference the value of ID_Clients of
the selected record in the parent form?

[Forms]![NameOfTheParentForm]![NameOfTheControl]


John W. Vinson[MVP]

 

[Jack Sheet]

what is the syntax to reference the value of ID_Clients of
the selected record in the parent form?

[Forms]![NameOfTheParentForm]![NameOfTheControl]


John W. Vinson[MVP]

I John, I almost got this to work. It works first time, but not second! I
explain:

To recap,
Subform contains a combo box whose purpose is to select record in subform
from a filtered list.

The RowSource property of this combo box is set as follows:

SELECT DISTINCTROW Q_Tasks_TJA.ID_Tasks, Q_Tasks_TJA.Task, Q_Tasks_TJA.End
FROM Q_Tasks_TJA
WHERE (((Q_Tasks_TJA.ID_Clients)=[Forms]![F_TJA_Tasks]![ID_Clients]));

ID_Clients is the primary key of T_Clients.
One-to-many relationship T_Clients.ID_Clients to T_Tasks.ID_Clients
Q_Tasks_TJA is a filtered subset of T_Tasks that contains also ID_Clients

PROBLEM:
The first time I select a client in the parent form, the dropdown menu of
the combo box in the subform CORRECTLY lists all of the tasks relevant to
the identified client.

Thereafter that dropdown menu appears to be fixed (until I exit and reopen
the form). Thus, if after the first selection as above I then change the
record of the parent form (ie select a new client), then the drop down menu
of the combo box in the subform INCORRECTLY continues to list the task
records of the first selected client.

Help??

Thanks

 

[Jack Sheet]

what is the syntax to reference the value of ID_Clients of
the selected record in the parent form?

[Forms]![NameOfTheParentForm]![NameOfTheControl]


John W. Vinson[MVP]

I John, I almost got this to work. It works first time, but not second! I
explain:

To recap,
Subform contains a combo box whose purpose is to select record in subform
from a filtered list.

The RowSource property of this combo box is set as follows:

SELECT DISTINCTROW Q_Tasks_TJA.ID_Tasks, Q_Tasks_TJA.Task, Q_Tasks_TJA.End
FROM Q_Tasks_TJA
WHERE (((Q_Tasks_TJA.ID_Clients)=[Forms]![F_TJA_Tasks]![ID_Clients]));

ID_Clients is the primary key of T_Clients.
One-to-many relationship T_Clients.ID_Clients to T_Tasks.ID_Clients
Q_Tasks_TJA is a filtered subset of T_Tasks that contains also ID_Clients

PROBLEM:
The first time I select a client in the parent form, the dropdown menu of
the combo box in the subform CORRECTLY lists all of the tasks relevant to
the identified client.

Thereafter that dropdown menu appears to be fixed (until I exit and reopen
the form). Thus, if after the first selection as above I then change the
record of the parent form (ie select a new client), then the drop down menu
of the combo box in the subform INCORRECTLY continues to list the task
records of the first selected client.

Help??

Thanks

I should probably have also mentioned that the Event code behind the subform
reads in full:

Option Compare Database
Option Explicit
Private Sub Form_Current()
Combo72 = Null
End Sub
Sub Combo72_AfterUpdate()
' Find the record that matches the control.
If Len(Me![Combo72] & "") > 0 Then
Me.RecordsetClone.FindFirst "[ID_Tasks] = " & Me![Combo72]
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End Sub

As I side issue, I note that the Combo box does NOT clear to Null after
selecting an item from the menu, OTHER than on the first attempt (ie prior
to changing the parent record).

 

[Jack Sheet]

On Tue, 29 Nov 2005 15:13:54 -0000, "Jack Sheet"

what is the syntax to reference the value of ID_Clients of
the selected record in the parent form?

[Forms]![NameOfTheParentForm]![NameOfTheControl]


John W. Vinson[MVP]

I John, I almost got this to work. It works first time, but not second! I
explain:

To recap,
Subform contains a combo box whose purpose is to select record in subform
from a filtered list.

The RowSource property of this combo box is set as follows:

SELECT DISTINCTROW Q_Tasks_TJA.ID_Tasks, Q_Tasks_TJA.Task, Q_Tasks_TJA.End
FROM Q_Tasks_TJA
WHERE (((Q_Tasks_TJA.ID_Clients)=[Forms]![F_TJA_Tasks]![ID_Clients]));

ID_Clients is the primary key of T_Clients.
One-to-many relationship T_Clients.ID_Clients to T_Tasks.ID_Clients
Q_Tasks_TJA is a filtered subset of T_Tasks that contains also ID_Clients

PROBLEM:
The first time I select a client in the parent form, the dropdown menu of
the combo box in the subform CORRECTLY lists all of the tasks relevant to
the identified client.

Thereafter that dropdown menu appears to be fixed (until I exit and reopen
the form). Thus, if after the first selection as above I then change the
record of the parent form (ie select a new client), then the drop down menu
of the combo box in the subform INCORRECTLY continues to list the task
records of the first selected client.

Help??

Thanks

I should probably have also mentioned that the Event code behind the subform
reads in full:

Option Compare Database
Option Explicit
Private Sub Form_Current()
Combo72 = Null
End Sub
Sub Combo72_AfterUpdate()
' Find the record that matches the control.
If Len(Me![Combo72] & "") > 0 Then
Me.RecordsetClone.FindFirst "[ID_Tasks] = " & Me![Combo72]
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End Sub

As I side issue, I note that the Combo box does NOT clear to Null after
selecting an item from the menu, OTHER than on the first attempt (ie prior
to changing the parent record).

UFF! slight correction. The combo box does NOT clear to Null EVEN ON THE
FIRST selection.
In other words, on the first attempt it successfully selects the record for
the subform but does not clear the combo box to Null.
In subsequent attempts (ie after changing the parent record) it fails on
both counts.

What I seem to require is something like the Application.Volatile command
that you get in UDFs in Excel

 

[Jack Sheet]

On Tue, 29 Nov 2005 15:13:54 -0000, "Jack Sheet"

what is the syntax to reference the value of ID_Clients of
the selected record in the parent form?

[Forms]![NameOfTheParentForm]![NameOfTheControl]


John W. Vinson[MVP]

I John, I almost got this to work. It works first time, but not
second! I
explain:

To recap,
Subform contains a combo box whose purpose is to select record in subform
from a filtered list.

The RowSource property of this combo box is set as follows:

SELECT DISTINCTROW Q_Tasks_TJA.ID_Tasks, Q_Tasks_TJA.Task, Q_Tasks_TJA.End
FROM Q_Tasks_TJA
WHERE (((Q_Tasks_TJA.ID_Clients)=[Forms]![F_TJA_Tasks]![ID_Clients]));

ID_Clients is the primary key of T_Clients.
One-to-many relationship T_Clients.ID_Clients to T_Tasks.ID_Clients
Q_Tasks_TJA is a filtered subset of T_Tasks that contains also ID_Clients

PROBLEM:
The first time I select a client in the parent form, the dropdown menu of
the combo box in the subform CORRECTLY lists all of the tasks relevant to
the identified client.

Thereafter that dropdown menu appears to be fixed (until I exit and reopen
the form). Thus, if after the first selection as above I then change the
record of the parent form (ie select a new client), then the drop down menu
of the combo box in the subform INCORRECTLY continues to list the task
records of the first selected client.

Help??

Thanks

I should probably have also mentioned that the Event code behind the subform
reads in full:

Option Compare Database
Option Explicit
Private Sub Form_Current()
Combo72 = Null
End Sub
Sub Combo72_AfterUpdate()
' Find the record that matches the control.
If Len(Me![Combo72] & "") > 0 Then
Me.RecordsetClone.FindFirst "[ID_Tasks] = " & Me![Combo72]
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End Sub

As I side issue, I note that the Combo box does NOT clear to Null after
selecting an item from the menu, OTHER than on the first attempt (ie
prior
to changing the parent record).

UFF! slight correction. The combo box does NOT clear to Null EVEN ON THE
FIRST selection.
In other words, on the first attempt it successfully selects the record
for
the subform but does not clear the combo box to Null.
In subsequent attempts (ie after changing the parent record) it fails on
both counts.

What I seem to require is something like the Application.Volatile command
that you get in UDFs in Excel

A thought: it occurs to me that it is an event in the parent form that is
misbehaving, namely, the selection of a new record in the parent form is an
event in the parent form, and it is that event that should cause the menu of
the combo box in the subform to refresh and which is failing (except on
first attempt). So it seems likely that any code to refresh the menu of the
combo box in the subform should be contained in the class module of the
parent form. Thus far, the only code residing behind the parent form is
this:

Option Compare Database
Option Explicit
Private Sub Form_Current()
Combo34 = Null
End Sub
Sub Combo34_AfterUpdate()
' Find the record that matches the control.
If Len(Me![Combo34] & "") > 0 Then
Me.RecordsetClone.FindFirst "[ID_Clients] = " & Me![Combo34]
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End Sub

(Combo34 is a combo box in the parent form)

Any ideas?

 

[Jack Sheet]

Problem now solved (separate thread)

On Tue, 29 Nov 2005 15:13:54 -0000, "Jack Sheet"

what is the syntax to reference the value of ID_Clients of
the selected record in the parent form?

[Forms]![NameOfTheParentForm]![NameOfTheControl]


John W. Vinson[MVP]

I John, I almost got this to work. It works first time, but not
second! I
explain:

To recap,
Subform contains a combo box whose purpose is to select record in subform
from a filtered list.

The RowSource property of this combo box is set as follows:

SELECT DISTINCTROW Q_Tasks_TJA.ID_Tasks, Q_Tasks_TJA.Task, Q_Tasks_TJA.End
FROM Q_Tasks_TJA
WHERE (((Q_Tasks_TJA.ID_Clients)=[Forms]![F_TJA_Tasks]![ID_Clients]));

ID_Clients is the primary key of T_Clients.
One-to-many relationship T_Clients.ID_Clients to T_Tasks.ID_Clients
Q_Tasks_TJA is a filtered subset of T_Tasks that contains also ID_Clients

PROBLEM:
The first time I select a client in the parent form, the dropdown menu of
the combo box in the subform CORRECTLY lists all of the tasks relevant to
the identified client.

Thereafter that dropdown menu appears to be fixed (until I exit and reopen
the form). Thus, if after the first selection as above I then change the
record of the parent form (ie select a new client), then the drop down
menu
of the combo box in the subform INCORRECTLY continues to list the task
records of the first selected client.

Help??

Thanks



I should probably have also mentioned that the Event code behind the subform
reads in full:

Option Compare Database
Option Explicit
Private Sub Form_Current()
Combo72 = Null
End Sub
Sub Combo72_AfterUpdate()
' Find the record that matches the control.
If Len(Me![Combo72] & "") > 0 Then
Me.RecordsetClone.FindFirst "[ID_Tasks] = " & Me![Combo72]
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End Sub

As I side issue, I note that the Combo box does NOT clear to Null after
selecting an item from the menu, OTHER than on the first attempt (ie
prior
to changing the parent record).

UFF! slight correction. The combo box does NOT clear to Null EVEN ON THE
FIRST selection.
In other words, on the first attempt it successfully selects the record
for
the subform but does not clear the combo box to Null.
In subsequent attempts (ie after changing the parent record) it fails on
both counts.

What I seem to require is something like the Application.Volatile command
that you get in UDFs in Excel

A thought: it occurs to me that it is an event in the parent form that is
misbehaving, namely, the selection of a new record in the parent form is
an event in the parent form, and it is that event that should cause the
menu of the combo box in the subform to refresh and which is failing
(except on first attempt). So it seems likely that any code to refresh
the menu of the combo box in the subform should be contained in the class
module of the parent form. Thus far, the only code residing behind the
parent form is this:

Option Compare Database
Option Explicit
Private Sub Form_Current()
Combo34 = Null
End Sub
Sub Combo34_AfterUpdate()
' Find the record that matches the control.
If Len(Me![Combo34] & "") > 0 Then
Me.RecordsetClone.FindFirst "[ID_Clients] = " & Me![Combo34]
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End Sub

(Combo34 is a combo box in the parent form)

Any ideas?

 

[Jack Sheet]

Problem now solved - thanks for pointing me in the right direction.
Outstanding assistance by all here.

what is the syntax to reference the value of ID_Clients of
the selected record in the parent form?

[Forms]![NameOfTheParentForm]![NameOfTheControl]


John W. Vinson[MVP]