Pass selection in multi-field listbox to query-based subform (repo

[LeAnne]

As I first posted back on 1/7/2009, I'm working on a database of stream
cleanup events held by volunteer organizations. I have an unbound form with
a combo box, and an unbound listbox (which shows 3 fields) that displays
results based on the selection in the cbo. The user selects the name of the
organization from the cbo, and the listbox displays all cleanup events that
organization has done (fields: eventID (hidden), which is the Bound column,
stream name, watershed, and cleanup date). I'd like for the user to click on
a listbox selection to open a subform, CleanupDetails, based on a crosstab
query, to display the eventID (again, the bound column) plus trash types
(tires, bulk items, mixed recyclables etc.) and amounts collected for the
eventID of the selected cleanup event in the listbox. Seems like this should
be easy...But I haven't done this in so long. I tried to filter the xtab
query which is the Recordsource for the subform based on the selected value
in the listbox:

TRANSFORM First(CleanupDetails.TrashCount) AS FirstOfTrashCount
SELECT CleanupEvents.EventID
FROM CleanupEvents INNER JOIN (CleanupDetails INNER JOIN Lookup_TrashTypes
ON CleanupDetails.TrashTypeID = Lookup_TrashTypes.TrashTypeID) ON
CleanupEvents.EventID = CleanupDetails.EventID
WHERE (((CleanupDetails.EventID)=[Forms]![TestForm2]![List2]))
GROUP BY CleanupEvents.EventID
ORDER BY CleanupEvents.EventID, Lookup_TrashTypes.TrashType
PIVOT Lookup_TrashTypes.TrashType;

But I keep getting an error message that the Jet db engine doesn't recognize
[Forms]![TestForm2]![List2] as a valid fieldname or expression. And I can't
link the master/child fields of the listbox because the form is unbound.
Clearly I'm missing something vital. Any help would be MUCH appreciated.

 

[boblarson]

1. If it is a multi select listbox then you can't reference the listbox this
way. If a listbox is set to multi select, then the value property is null -
always.

2. You would need to iterate through its selected items to build the string
to replace in your where clause.

 

[LeAnne]

Hi bob, thanks for replying.

The listbox has multiple <i>fields</i>, but it isn't a multi-select listbox.
I want users to select 1 cleanup event at a time, not multiple events. I
suppose I could've used a combo box instead of a list box for this control,
but my users were having issues with selecting the desired event from a
dropdown combo...it seemed easier to choose & click from a plain list.
Perhaps what's needed is a relatively simple event procedure to launch the
SQL of the xtab query based on the ItemSelected property? If so, any air code
you could rough up to get me started would be very much appreciated, as it's
been nearly 2 years since I've been anywhere near the VB editor, and I've
forgotten almost everything I ever knew about VBA.

Thanks!

1. If it is a multi select listbox then you can't reference the listbox this
way. If a listbox is set to multi select, then the value property is null -
always.

2. You would need to iterate through its selected items to build the string
to replace in your where clause.
--
Bob Larson
Free MS Access Tutorials and Samples at http://www.btabdevelopment.com

__________________________________

As I first posted back on 1/7/2009, I'm working on a database of stream
cleanup events held by volunteer organizations. I have an unbound form with
a combo box, and an unbound listbox (which shows 3 fields) that displays
results based on the selection in the cbo. The user selects the name of the
organization from the cbo, and the listbox displays all cleanup events that
organization has done (fields: eventID (hidden), which is the Bound column,
stream name, watershed, and cleanup date). I'd like for the user to click on
a listbox selection to open a subform, CleanupDetails, based on a crosstab
query, to display the eventID (again, the bound column) plus trash types
(tires, bulk items, mixed recyclables etc.) and amounts collected for the
eventID of the selected cleanup event in the listbox. Seems like this should
be easy...But I haven't done this in so long. I tried to filter the xtab
query which is the Recordsource for the subform based on the selected value
in the listbox:

TRANSFORM First(CleanupDetails.TrashCount) AS FirstOfTrashCount
SELECT CleanupEvents.EventID
FROM CleanupEvents INNER JOIN (CleanupDetails INNER JOIN Lookup_TrashTypes
ON CleanupDetails.TrashTypeID = Lookup_TrashTypes.TrashTypeID) ON
CleanupEvents.EventID = CleanupDetails.EventID
WHERE (((CleanupDetails.EventID)=[Forms]![TestForm2]![List2]))
GROUP BY CleanupEvents.EventID
ORDER BY CleanupEvents.EventID, Lookup_TrashTypes.TrashType
PIVOT Lookup_TrashTypes.TrashType;

But I keep getting an error message that the Jet db engine doesn't recognize
[Forms]![TestForm2]![List2] as a valid fieldname or expression. And I can't
link the master/child fields of the listbox because the form is unbound.
Clearly I'm missing something vital. Any help would be MUCH appreciated.

 

[boblarson]

If it isn't a multi-select listbox then the way you are referencing it seems
to be correct in my glance at it. Make sure that you are referencing the
correct item and that it isn't a subform you are trying to reference instead
of on a main form.

If it is a subform you are referencing the control on, you would use

[Forms]![YourMainFormNameHere]![YourSubformCONTAINERnameHere].[Form].[List2]

if List2 is the name of the listbox. I would rename it to something
meaningful though for ease of reading things in the future.
--
Bob Larson
Free MS Access Tutorials and Samples at http://www.btabdevelopment.com

__________________________________

Hi bob, thanks for replying.

The listbox has multiple <i>fields</i>, but it isn't a multi-select listbox.
I want users to select 1 cleanup event at a time, not multiple events. I
suppose I could've used a combo box instead of a list box for this control,
but my users were having issues with selecting the desired event from a
dropdown combo...it seemed easier to choose & click from a plain list.
Perhaps what's needed is a relatively simple event procedure to launch the
SQL of the xtab query based on the ItemSelected property? If so, any air code
you could rough up to get me started would be very much appreciated, as it's
been nearly 2 years since I've been anywhere near the VB editor, and I've
forgotten almost everything I ever knew about VBA.

Thanks!

1. If it is a multi select listbox then you can't reference the listbox this
way. If a listbox is set to multi select, then the value property is null -
always.

2. You would need to iterate through its selected items to build the string
to replace in your where clause.
--
Bob Larson
Free MS Access Tutorials and Samples at http://www.btabdevelopment.com

__________________________________

As I first posted back on 1/7/2009, I'm working on a database of stream
cleanup events held by volunteer organizations. I have an unbound form with
a combo box, and an unbound listbox (which shows 3 fields) that displays
results based on the selection in the cbo. The user selects the name of the
organization from the cbo, and the listbox displays all cleanup events that
organization has done (fields: eventID (hidden), which is the Bound column,
stream name, watershed, and cleanup date). I'd like for the user to click on
a listbox selection to open a subform, CleanupDetails, based on a crosstab
query, to display the eventID (again, the bound column) plus trash types
(tires, bulk items, mixed recyclables etc.) and amounts collected for the
eventID of the selected cleanup event in the listbox. Seems like this should
be easy...But I haven't done this in so long. I tried to filter the xtab
query which is the Recordsource for the subform based on the selected value
in the listbox:

TRANSFORM First(CleanupDetails.TrashCount) AS FirstOfTrashCount
SELECT CleanupEvents.EventID
FROM CleanupEvents INNER JOIN (CleanupDetails INNER JOIN Lookup_TrashTypes
ON CleanupDetails.TrashTypeID = Lookup_TrashTypes.TrashTypeID) ON
CleanupEvents.EventID = CleanupDetails.EventID
WHERE (((CleanupDetails.EventID)=[Forms]![TestForm2]![List2]))
GROUP BY CleanupEvents.EventID
ORDER BY CleanupEvents.EventID, Lookup_TrashTypes.TrashType
PIVOT Lookup_TrashTypes.TrashType;

But I keep getting an error message that the Jet db engine doesn't recognize
[Forms]![TestForm2]![List2] as a valid fieldname or expression. And I can't
link the master/child fields of the listbox because the form is unbound.
Clearly I'm missing something vital. Any help would be MUCH appreciated.

 

[LeAnne]

Bob, thanks for your continued help. I apologize if I'm just being dense,
but I still can't seem to make the reference to the list box (which is on the
main form, not a subform) work... still getting the "not a valid fieldname or
expression" MsgBox. I tried another solution... I gave up on the subform
approach & decided to open the CleanupDetails form as a popup using this
event attached to the DblClick event of the list box (List2):

Private Sub List2_DblClick(Cancel As Integer)
If Not IsNull(List2) Then
DoCmd.OpenForm "sf_GetCleanupDetails", , "[qryGetCleanupDetails.EventID]=" & _
"'" & Me.List2.Column(0) & "'"
End If

End Sub

....which seems to do almost everything I want...except filter the
CleanupDetails form (now a popup). The form continues to display details for
ALL Cleanup events, regardless of the selection in the list box.*exhales
noisily* Having spent more than a week on this one thing already, I'm about
ready to give up entirely.

(Is this really such a radical thing to want to be able to do?!? Rhetorical
question, pleaz ignore.)

If it isn't a multi-select listbox then the way you are referencing it seems
to be correct in my glance at it. Make sure that you are referencing the
correct item and that it isn't a subform you are trying to reference instead
of on a main form.

If it is a subform you are referencing the control on, you would use

[Forms]![YourMainFormNameHere]![YourSubformCONTAINERnameHere].[Form].[List2]

if List2 is the name of the listbox. I would rename it to something
meaningful though for ease of reading things in the future.
--
Bob Larson
Free MS Access Tutorials and Samples at http://www.btabdevelopment.com

__________________________________

Hi bob, thanks for replying.

The listbox has multiple <i>fields</i>, but it isn't a multi-select listbox.
I want users to select 1 cleanup event at a time, not multiple events. I
suppose I could've used a combo box instead of a list box for this control,
but my users were having issues with selecting the desired event from a
dropdown combo...it seemed easier to choose & click from a plain list.
Perhaps what's needed is a relatively simple event procedure to launch the
SQL of the xtab query based on the ItemSelected property? If so, any air code
you could rough up to get me started would be very much appreciated, as it's
been nearly 2 years since I've been anywhere near the VB editor, and I've
forgotten almost everything I ever knew about VBA.

Thanks!

1. If it is a multi select listbox then you can't reference the listbox this
way. If a listbox is set to multi select, then the value property is null -
always.

2. You would need to iterate through its selected items to build the string
to replace in your where clause.
--
Bob Larson
Free MS Access Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


:

As I first posted back on 1/7/2009, I'm working on a database of stream
cleanup events held by volunteer organizations. I have an unbound form with
a combo box, and an unbound listbox (which shows 3 fields) that displays
results based on the selection in the cbo. The user selects the name of the
organization from the cbo, and the listbox displays all cleanup events that
organization has done (fields: eventID (hidden), which is the Bound column,
stream name, watershed, and cleanup date). I'd like for the user to click on
a listbox selection to open a subform, CleanupDetails, based on a crosstab
query, to display the eventID (again, the bound column) plus trash types
(tires, bulk items, mixed recyclables etc.) and amounts collected for the
eventID of the selected cleanup event in the listbox. Seems like this should
be easy...But I haven't done this in so long. I tried to filter the xtab
query which is the Recordsource for the subform based on the selected value
in the listbox:

TRANSFORM First(CleanupDetails.TrashCount) AS FirstOfTrashCount
SELECT CleanupEvents.EventID
FROM CleanupEvents INNER JOIN (CleanupDetails INNER JOIN Lookup_TrashTypes
ON CleanupDetails.TrashTypeID = Lookup_TrashTypes.TrashTypeID) ON
CleanupEvents.EventID = CleanupDetails.EventID
WHERE (((CleanupDetails.EventID)=[Forms]![TestForm2]![List2]))
GROUP BY CleanupEvents.EventID
ORDER BY CleanupEvents.EventID, Lookup_TrashTypes.TrashType
PIVOT Lookup_TrashTypes.TrashType;

But I keep getting an error message that the Jet db engine doesn't recognize
[Forms]![TestForm2]![List2] as a valid fieldname or expression. And I can't
link the master/child fields of the listbox because the form is unbound.
Clearly I'm missing something vital. Any help would be MUCH appreciated.

 

[Bob Larson]

If you want to zip it and email it to me along with detailed instructions as
to what you want me to look at and what it should do, I will take a look.

You can can send to accessbob [a][ t] g ma i l (do t co m )

--

Thanks,

Bob Larson
Access MVP

Free Access Tutorials and Resources: http://www.btabdevelopment.com

Bob, thanks for your continued help. I apologize if I'm just being dense,
but I still can't seem to make the reference to the list box (which is on
the
main form, not a subform) work... still getting the "not a valid fieldname
or
expression" MsgBox. I tried another solution... I gave up on the subform
approach & decided to open the CleanupDetails form as a popup using this
event attached to the DblClick event of the list box (List2):

Private Sub List2_DblClick(Cancel As Integer)
If Not IsNull(List2) Then
DoCmd.OpenForm "sf_GetCleanupDetails", , "[qryGetCleanupDetails.EventID]="
& _
"'" & Me.List2.Column(0) & "'"
End If

End Sub

...which seems to do almost everything I want...except filter the
CleanupDetails form (now a popup). The form continues to display details
for
ALL Cleanup events, regardless of the selection in the list box.*exhales
noisily* Having spent more than a week on this one thing already, I'm
about
ready to give up entirely.

(Is this really such a radical thing to want to be able to do?!?
Rhetorical
question, pleaz ignore.)

If it isn't a multi-select listbox then the way you are referencing it
seems
to be correct in my glance at it. Make sure that you are referencing the
correct item and that it isn't a subform you are trying to reference
instead
of on a main form.

If it is a subform you are referencing the control on, you would use

[Forms]![YourMainFormNameHere]![YourSubformCONTAINERnameHere].[Form].[List2]

if List2 is the name of the listbox. I would rename it to something
meaningful though for ease of reading things in the future.
--
Bob Larson
Free MS Access Tutorials and Samples at http://www.btabdevelopment.com

__________________________________

Hi bob, thanks for replying.

The listbox has multiple <i>fields</i>, but it isn't a multi-select
listbox.
I want users to select 1 cleanup event at a time, not multiple events.
I
suppose I could've used a combo box instead of a list box for this
control,
but my users were having issues with selecting the desired event from a
dropdown combo...it seemed easier to choose & click from a plain list.
Perhaps what's needed is a relatively simple event procedure to launch
the
SQL of the xtab query based on the ItemSelected property? If so, any
air code
you could rough up to get me started would be very much appreciated, as
it's
been nearly 2 years since I've been anywhere near the VB editor, and
I've
forgotten almost everything I ever knew about VBA.

Thanks!

:

1. If it is a multi select listbox then you can't reference the
listbox this
way. If a listbox is set to multi select, then the value property is
null -
always.

2. You would need to iterate through its selected items to build the
string
to replace in your where clause.
--
Bob Larson
Free MS Access Tutorials and Samples at
http://www.btabdevelopment.com

__________________________________


:

As I first posted back on 1/7/2009, I'm working on a database of
stream
cleanup events held by volunteer organizations. I have an unbound
form with
a combo box, and an unbound listbox (which shows 3 fields) that
displays
results based on the selection in the cbo. The user selects the
name of the
organization from the cbo, and the listbox displays all cleanup
events that
organization has done (fields: eventID (hidden), which is the Bound
column,
stream name, watershed, and cleanup date). I'd like for the user
to click on
a listbox selection to open a subform, CleanupDetails, based on a
crosstab
query, to display the eventID (again, the bound column) plus trash
types
(tires, bulk items, mixed recyclables etc.) and amounts collected
for the
eventID of the selected cleanup event in the listbox. Seems like
this should
be easy...But I haven't done this in so long. I tried to filter the
xtab
query which is the Recordsource for the subform based on the
selected value
in the listbox:

TRANSFORM First(CleanupDetails.TrashCount) AS FirstOfTrashCount
SELECT CleanupEvents.EventID
FROM CleanupEvents INNER JOIN (CleanupDetails INNER JOIN
Lookup_TrashTypes
ON CleanupDetails.TrashTypeID = Lookup_TrashTypes.TrashTypeID) ON
CleanupEvents.EventID = CleanupDetails.EventID
WHERE (((CleanupDetails.EventID)=[Forms]![TestForm2]![List2]))
GROUP BY CleanupEvents.EventID
ORDER BY CleanupEvents.EventID, Lookup_TrashTypes.TrashType
PIVOT Lookup_TrashTypes.TrashType;

But I keep getting an error message that the Jet db engine doesn't
recognize
[Forms]![TestForm2]![List2] as a valid fieldname or expression.
And I can't
link the master/child fields of the listbox because the form is
unbound.
Clearly I'm missing something vital. Any help would be MUCH
appreciated.

 

[LeAnne]

You're most generous!! I'll need to clean it up first, remove extraneous bits
of code etc. first. I'll be in touch!

Thanks again,

LeAnne

If you want to zip it and email it to me along with detailed instructions as
to what you want me to look at and what it should do, I will take a look.

You can can send to accessbob [a][ t] g ma i l (do t co m )

--

Thanks,

Bob Larson
Access MVP

Free Access Tutorials and Resources: http://www.btabdevelopment.com

Bob, thanks for your continued help. I apologize if I'm just being dense,
but I still can't seem to make the reference to the list box (which is on
the
main form, not a subform) work... still getting the "not a valid fieldname
or
expression" MsgBox. I tried another solution... I gave up on the subform
approach & decided to open the CleanupDetails form as a popup using this
event attached to the DblClick event of the list box (List2):

Private Sub List2_DblClick(Cancel As Integer)
If Not IsNull(List2) Then
DoCmd.OpenForm "sf_GetCleanupDetails", , "[qryGetCleanupDetails.EventID]="
& _
"'" & Me.List2.Column(0) & "'"
End If

End Sub

...which seems to do almost everything I want...except filter the
CleanupDetails form (now a popup). The form continues to display details
for
ALL Cleanup events, regardless of the selection in the list box.*exhales
noisily* Having spent more than a week on this one thing already, I'm
about
ready to give up entirely.

(Is this really such a radical thing to want to be able to do?!?
Rhetorical
question, pleaz ignore.)

If it isn't a multi-select listbox then the way you are referencing it
seems
to be correct in my glance at it. Make sure that you are referencing the
correct item and that it isn't a subform you are trying to reference
instead
of on a main form.

If it is a subform you are referencing the control on, you would use

[Forms]![YourMainFormNameHere]![YourSubformCONTAINERnameHere].[Form].[List2]

if List2 is the name of the listbox. I would rename it to something
meaningful though for ease of reading things in the future.
--
Bob Larson
Free MS Access Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


:

Hi bob, thanks for replying.

The listbox has multiple <i>fields</i>, but it isn't a multi-select
listbox.
I want users to select 1 cleanup event at a time, not multiple events.
I
suppose I could've used a combo box instead of a list box for this
control,
but my users were having issues with selecting the desired event from a
dropdown combo...it seemed easier to choose & click from a plain list.
Perhaps what's needed is a relatively simple event procedure to launch
the
SQL of the xtab query based on the ItemSelected property? If so, any
air code
you could rough up to get me started would be very much appreciated, as
it's
been nearly 2 years since I've been anywhere near the VB editor, and
I've
forgotten almost everything I ever knew about VBA.

Thanks!

:

1. If it is a multi select listbox then you can't reference the
listbox this
way. If a listbox is set to multi select, then the value property is
null -
always.

2. You would need to iterate through its selected items to build the
string
to replace in your where clause.
--
Bob Larson
Free MS Access Tutorials and Samples at
http://www.btabdevelopment.com

__________________________________


:

As I first posted back on 1/7/2009, I'm working on a database of
stream
cleanup events held by volunteer organizations. I have an unbound
form with
a combo box, and an unbound listbox (which shows 3 fields) that
displays
results based on the selection in the cbo. The user selects the
name of the
organization from the cbo, and the listbox displays all cleanup
events that
organization has done (fields: eventID (hidden), which is the Bound
column,
stream name, watershed, and cleanup date). I'd like for the user
to click on
a listbox selection to open a subform, CleanupDetails, based on a
crosstab
query, to display the eventID (again, the bound column) plus trash
types
(tires, bulk items, mixed recyclables etc.) and amounts collected
for the
eventID of the selected cleanup event in the listbox. Seems like
this should
be easy...But I haven't done this in so long. I tried to filter the
xtab
query which is the Recordsource for the subform based on the
selected value
in the listbox:

TRANSFORM First(CleanupDetails.TrashCount) AS FirstOfTrashCount
SELECT CleanupEvents.EventID
FROM CleanupEvents INNER JOIN (CleanupDetails INNER JOIN
Lookup_TrashTypes
ON CleanupDetails.TrashTypeID = Lookup_TrashTypes.TrashTypeID) ON
CleanupEvents.EventID = CleanupDetails.EventID
WHERE (((CleanupDetails.EventID)=[Forms]![TestForm2]![List2]))
GROUP BY CleanupEvents.EventID
ORDER BY CleanupEvents.EventID, Lookup_TrashTypes.TrashType
PIVOT Lookup_TrashTypes.TrashType;

But I keep getting an error message that the Jet db engine doesn't
recognize
[Forms]![TestForm2]![List2] as a valid fieldname or expression.
And I can't
link the master/child fields of the listbox because the form is
unbound.
Clearly I'm missing something vital. Any help would be MUCH
appreciated.

 

[Bob Larson]

Been taking a look. It may take a bit as I think what you currently have is
a bit confusing (at least how the queries for the forms are set up). So,
keep working at it and I will do so as well. But, it may take a little more
time than I had originally figured on.

--

Thanks,

Bob Larson
Access MVP

Free Access Tutorials and Resources: http://www.btabdevelopment.com

You're most generous!! I'll need to clean it up first, remove extraneous
bits
of code etc. first. I'll be in touch!

Thanks again,

LeAnne

If you want to zip it and email it to me along with detailed instructions
as
to what you want me to look at and what it should do, I will take a look.

You can can send to accessbob [a][ t] g ma i l (do t co m )

--

Thanks,

Bob Larson
Access MVP

Free Access Tutorials and Resources: http://www.btabdevelopment.com

Bob, thanks for your continued help. I apologize if I'm just being
dense,
but I still can't seem to make the reference to the list box (which is
on
the
main form, not a subform) work... still getting the "not a valid
fieldname
or
expression" MsgBox. I tried another solution... I gave up on the
subform
approach & decided to open the CleanupDetails form as a popup using
this
event attached to the DblClick event of the list box (List2):

Private Sub List2_DblClick(Cancel As Integer)
If Not IsNull(List2) Then
DoCmd.OpenForm "sf_GetCleanupDetails", ,
"[qryGetCleanupDetails.EventID]="
& _
"'" & Me.List2.Column(0) & "'"
End If

End Sub

...which seems to do almost everything I want...except filter the
CleanupDetails form (now a popup). The form continues to display
details
for
ALL Cleanup events, regardless of the selection in the list
box.*exhales
noisily* Having spent more than a week on this one thing already, I'm
about
ready to give up entirely.

(Is this really such a radical thing to want to be able to do?!?
Rhetorical
question, pleaz ignore.)

:

If it isn't a multi-select listbox then the way you are referencing it
seems
to be correct in my glance at it. Make sure that you are referencing
the
correct item and that it isn't a subform you are trying to reference
instead
of on a main form.

If it is a subform you are referencing the control on, you would use

[Forms]![YourMainFormNameHere]![YourSubformCONTAINERnameHere].[Form].[List2]

if List2 is the name of the listbox. I would rename it to something
meaningful though for ease of reading things in the future.
--
Bob Larson
Free MS Access Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


:

Hi bob, thanks for replying.

The listbox has multiple <i>fields</i>, but it isn't a multi-select
listbox.
I want users to select 1 cleanup event at a time, not multiple
events.
I
suppose I could've used a combo box instead of a list box for this
control,
but my users were having issues with selecting the desired event
from a
dropdown combo...it seemed easier to choose & click from a plain
list.
Perhaps what's needed is a relatively simple event procedure to
launch
the
SQL of the xtab query based on the ItemSelected property? If so, any
air code
you could rough up to get me started would be very much appreciated,
as
it's
been nearly 2 years since I've been anywhere near the VB editor, and
I've
forgotten almost everything I ever knew about VBA.

Thanks!

:

1. If it is a multi select listbox then you can't reference the
listbox this
way. If a listbox is set to multi select, then the value property
is
null -
always.

2. You would need to iterate through its selected items to build
the
string
to replace in your where clause.
--
Bob Larson
Free MS Access Tutorials and Samples at
http://www.btabdevelopment.com

__________________________________


:

As I first posted back on 1/7/2009, I'm working on a database
of
stream
cleanup events held by volunteer organizations. I have an
unbound
form with
a combo box, and an unbound listbox (which shows 3 fields) that
displays
results based on the selection in the cbo. The user selects the
name of the
organization from the cbo, and the listbox displays all cleanup
events that
organization has done (fields: eventID (hidden), which is the
Bound
column,
stream name, watershed, and cleanup date). I'd like for the
user
to click on
a listbox selection to open a subform, CleanupDetails, based on
a
crosstab
query, to display the eventID (again, the bound column) plus
trash
types
(tires, bulk items, mixed recyclables etc.) and amounts
collected
for the
eventID of the selected cleanup event in the listbox. Seems
like
this should
be easy...But I haven't done this in so long. I tried to filter
the
xtab
query which is the Recordsource for the subform based on the
selected value
in the listbox:

TRANSFORM First(CleanupDetails.TrashCount) AS FirstOfTrashCount
SELECT CleanupEvents.EventID
FROM CleanupEvents INNER JOIN (CleanupDetails INNER JOIN
Lookup_TrashTypes
ON CleanupDetails.TrashTypeID = Lookup_TrashTypes.TrashTypeID)
ON
CleanupEvents.EventID = CleanupDetails.EventID
WHERE (((CleanupDetails.EventID)=[Forms]![TestForm2]![List2]))
GROUP BY CleanupEvents.EventID
ORDER BY CleanupEvents.EventID, Lookup_TrashTypes.TrashType
PIVOT Lookup_TrashTypes.TrashType;

But I keep getting an error message that the Jet db engine
doesn't
recognize
[Forms]![TestForm2]![List2] as a valid fieldname or expression.
And I can't
link the master/child fields of the listbox because the form is
unbound.
Clearly I'm missing something vital. Any help would be MUCH
appreciated.