parameter query

I know how to create a parameter query. What I'd like to do is run a
parameter query that displays many child records of one parent record
that permits multiple parameter entries for the primary key. Can this
be done?

For example, the query returns:

ID (pk)
12345
12346
12347
12348
12349

I may only want the report to display:
12345
12348
12349


I can't see how this could be done via a parameter query.

Does anyone have any ideas? Thanks in advance!

No: you can't do that John. A parameter can only be a value; it cannot be a
mix of various values, separators, operators, ...

You could get the report you want by building a WhereCondition string to
filter the report. Here's an example:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

wrote in message
...
I know how to create a parameter query. What I'd like to do is run a
parameter query that displays many child records of one parent record
that permits multiple parameter entries for the primary key. Can this
be done?

For example, the query returns:

ID (pk)
12345
12346
12347
12348
12349

I may only want the report to display:
12345
12348
12349


I can't see how this could be done via a parameter query.

Does anyone have any ideas? Thanks in advance!

There are some 'way around', but that can be slow.

A possible one is to pass the parameter: "12345,12348,12349"

(note there is no space after the comas)


And to use the query:

.... WHERE ("," & parameter & ",") LIKE ("*," & id & ",*")


ie, use the field as a pattern and the parameter as left argument of LIKE.
(Generally we do the reverse, the field is at the left and the parameter is
the pattern, at the right, of the operator LIKE).


Vanderghast, Access MVP


"Allen Browne" wrote in message
...
No: you can't do that John. A parameter can only be a value; it cannot be
a mix of various values, separators, operators, ...

You could get the report you want by building a WhereCondition string to
filter the report. Here's an example:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

wrote in message
...
I know how to create a parameter query. What I'd like to do is run a
parameter query that displays many child records of one parent record
that permits multiple parameter entries for the primary key. Can this
be done?

For example, the query returns:

ID (pk)
12345
12346
12347
12348
12349

I may only want the report to display:
12345
12348
12349


I can't see how this could be done via a parameter query.

Does anyone have any ideas? Thanks in advance!

Michel, that's just too clever. :-)

To handle the spaces a user might enter:
PARAMETERS WotNum Text ( 255 );
SELECT ClientNum
FROM tblClient
WHERE "," & Replace([WotNum], " ", "") & "," Like "*," & [ClientNum] &
",*";

Tested with a local table of 15k clients: quite usable performance.


Thanks for posting: you've provided my fun for this evening.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message
...
There are some 'way around', but that can be slow.

A possible one is to pass the parameter: "12345,12348,12349"
(note there is no space after the comas)

And to use the query:
... WHERE ("," & parameter & ",") LIKE ("*," & id & ",*")

ie, use the field as a pattern and the parameter as left argument of LIKE.
(Generally we do the reverse, the field is at the left and the parameter
is the pattern, at the right, of the operator LIKE).

Vanderghast, Access MVP

"Allen Browne" wrote in message
...
No: you can't do that John. A parameter can only be a value; it cannot
be a mix of various values, separators, operators, ...

You could get the report you want by building a WhereCondition string to
filter the report. Here's an example:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

wrote in message
...
I know how to create a parameter query. What I'd like to do is run a
parameter query that displays many child records of one parent record
that permits multiple parameter entries for the primary key. Can this
be done?

Thanks, Michel! I was just starting to get a grip on Allen's
suggestion but now that he's enamored with yours I'm torn!

I don't know that I fully understand your suggestion. Do I still
create a form with a listbox to select the records...? I'm a bit
lost.

A possible one is to pass the parameter: *"12345,12348,12349"

Like passing a kidney stone?

And to use the query:

... WHERE ("," & parameter & ",") *LIKE ("*," & id *& ",*")

ie, use the field as a pattern and the parameter as left argument of LIKE..
(Generally we do the reverse, the field is at the left and the parameter is
the pattern, at the right, of the operator LIKE).

So I create a field in my query...yikes...I'm really lost here!

"Allen Browne" wrote in message

...



No: you can't do that John. A parameter can only be a value; it cannot be
a mix of various values, separators, operators, ...

You could get the report you want by building a WhereCondition string to
filter the report. Here's an example:
* *Use a multi-select list box to filter a report
at:
* *http://allenbrowne.com/ser-50.html

--
Allen Browne - Microsoft MVP. *Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

wrote in message
...
I know how to create a parameter query. What I'd like to do is run a
parameter query that displays many child records of one parent record
that permits multiple parameter entries for the primary key. Can this
be done?

For example, the query returns:

ID (pk)
12345
12346
12347
12348
12349

I may only want the report to display:
12345
12348
12349

I can't see how this could be done via a parameter query.

Does anyone have any ideas? Thanks in advance!- Hide quoted text -

- Show quoted text -

You can use a list box with multiple selections possible, or just ask for
the 'string' (less friendly). If you use a list box, you will have to run
over its Selected items collection to get make the said string. That
'string' is then a standard parameter for a standard Jet query with a
parameter (or for a stored procedure with MS SQL Server). Assuming you use
Jet, then you can write your query with the syntax:


.... WHERE "," &
FORMS!FormName!ControlNameHavingTheConcatenatedStr ingUsedAsParameterHere &
"," LIKE "*," & yourFieldNameHere & ",*"



That assumes your query will be launched from the User Interface ( or from
DoCmd, or as RecordSource of another form/report) , with the implied form
already open, and its implied text control, filled (the form or the control
can be invisible, but must be open).



Vanderghast, Access MVP



wrote in message
...
Thanks, Michel! I was just starting to get a grip on Allen's
suggestion but now that he's enamored with yours I'm torn!

I don't know that I fully understand your suggestion. Do I still
create a form with a listbox to select the records...? I'm a bit
lost.

A possible one is to pass the parameter: "12345,12348,12349"

Like passing a kidney stone?

And to use the query:

... WHERE ("," & parameter & ",") LIKE ("*," & id & ",*")

ie, use the field as a pattern and the parameter as left argument of LIKE.
(Generally we do the reverse, the field is at the left and the parameter
is
the pattern, at the right, of the operator LIKE).

So I create a field in my query...yikes...I'm really lost here!

"Allen Browne" wrote in message

...



No: you can't do that John. A parameter can only be a value; it cannot
be
a mix of various values, separators, operators, ...

You could get the report you want by building a WhereCondition string to
filter the report. Here's an example:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

wrote in message
...
I know how to create a parameter query. What I'd like to do is run a
parameter query that displays many child records of one parent record
that permits multiple parameter entries for the primary key. Can this
be done?

For example, the query returns:

ID (pk)
12345
12346
12347
12348
12349

I may only want the report to display:
12345
12348
12349

I can't see how this could be done via a parameter query.

Does anyone have any ideas? Thanks in advance!- Hide quoted text -

- Show quoted text -

I'm sorry. I'm struggling to wrap my brain around this! I hope you can
help because I feel like I'm almost there.

I'm going with the listbox. My design has a bit of a twist. My form
includes a combobox that queries parent records and then requeries the
listbox to the parent's child records. This works fine and it's pretty
cool as I've never really had a use for a list box before!

What I'm struggling with is the botton code. The above design results
in opening the report according to the listbox as well as the parent
record in the combobox.

Here are the control names:
cbPKWTID
lstFGID

I'm really at a loss as to how to plug all of this into the code. And
after reading Michel's suggestion and your "impreovement" to it -
well, I'm stumped. I've plugged in what my field names and report name
but could you please provide someguidance with all of the Dim's? I
suspect that my design above complicates things...?

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the
list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'rptPKWeightCalculatorASSsFGs.

'strDelim = """" 'Delimiter appropriate to field type.
See note 1.
strDoc = "Associated Finished Goods"

'Loop through the ItemsSelected in the list box.
With Me.lstFGID
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) &
""", "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen 0 Then
strWhere = "[FGIDs] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen 0 Then
strDescrip = "FinishedGoodIDs: " & Left$(strDescrip,
lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see
note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note
4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub

On Jun 26, 12:27*pm, "Allen Browne"
wrote:
Michel, that's just too clever. *:-)

To handle the spaces a user might enter:
* * PARAMETERS WotNum Text ( 255 );
* * SELECT ClientNum
* * FROM tblClient
* * WHERE "," & Replace([WotNum], " ", "") & "," Like "*," & [ClientNum] &
",*";

Tested with a local table of 15k clients: quite usable performance.

Thanks for posting: you've provided my fun for this evening.

--
Allen Browne - Microsoft MVP. *Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message

...



There are some 'way around', but that can be slow.

A possible one is to pass the parameter: *"12345,12348,12349"
(note there is no space after the comas)

And to use the query:
... WHERE ("," & parameter & ",") *LIKE ("*," & id *& ",*")

ie, use the field as a pattern and the parameter as left argument of LIKE.
(Generally we do the reverse, the field is at the left and the parameter
is the pattern, at the right, of the operator LIKE).

Vanderghast, Access MVP

"Allen Browne" wrote in message
...
No: you can't do that John. A parameter can only be a value; it cannot
be a mix of various values, separators, operators, ...

You could get the report you want by building a WhereCondition string to
filter the report. Here's an example:
* *Use a multi-select list box to filter a report
at:
* *http://allenbrowne.com/ser-50.html

wrote in message
....
I know how to create a parameter query. What I'd like to do is run a
parameter query that displays many child records of one parent record
that permits multiple parameter entries for the primary key. Can this
be done?- Hide quoted text -

- Show quoted text -

Not really clear about that, John.

You have a form with:
- a combo
- a list box
- a subform

I'm not clear about how these elements are tied together. Is the list box
multi-select? How is it influenced by the combo? How does it affect the
subform? Is it the subform's filter that you want to apply to your report?
Are the criteria in the query, or are they in the filter of the form?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
wrote in message
...
I'm sorry. I'm struggling to wrap my brain around this! I hope you can
help because I feel like I'm almost there.

I'm going with the listbox. My design has a bit of a twist. My form
includes a combobox that queries parent records and then requeries the
listbox to the parent's child records. This works fine and it's pretty
cool as I've never really had a use for a list box before!

What I'm struggling with is the botton code. The above design results
in opening the report according to the listbox as well as the parent
record in the combobox.

Here are the control names:
cbPKWTID
lstFGID

I'm really at a loss as to how to plug all of this into the code. And
after reading Michel's suggestion and your "impreovement" to it -
well, I'm stumped. I've plugged in what my field names and report name
but could you please provide someguidance with all of the Dim's? I
suspect that my design above complicates things...?

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the
list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'rptPKWeightCalculatorASSsFGs.

'strDelim = """" 'Delimiter appropriate to field type.
See note 1.
strDoc = "Associated Finished Goods"

'Loop through the ItemsSelected in the list box.
With Me.lstFGID
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) &
""", "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen 0 Then
strWhere = "[FGIDs] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen 0 Then
strDescrip = "FinishedGoodIDs: " & Left$(strDescrip,
lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see
note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note
4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub

On Jun 26, 12:27 pm, "Allen Browne"
wrote:
Michel, that's just too clever. :-)

To handle the spaces a user might enter:
PARAMETERS WotNum Text ( 255 );
SELECT ClientNum
FROM tblClient
WHERE "," & Replace([WotNum], " ", "") & "," Like "*," & [ClientNum] &
",*";

Tested with a local table of 15k clients: quite usable performance.

Thanks for posting: you've provided my fun for this evening.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message

...



There are some 'way around', but that can be slow.

A possible one is to pass the parameter: "12345,12348,12349"
(note there is no space after the comas)

And to use the query:
... WHERE ("," & parameter & ",") LIKE ("*," & id & ",*")

ie, use the field as a pattern and the parameter as left argument of
LIKE.
(Generally we do the reverse, the field is at the left and the parameter
is the pattern, at the right, of the operator LIKE).

Vanderghast, Access MVP

"Allen Browne" wrote in message
...
No: you can't do that John. A parameter can only be a value; it cannot
be a mix of various values, separators, operators, ...

You could get the report you want by building a WhereCondition string
to
filter the report. Here's an example:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

wrote in message
...
I know how to create a parameter query. What I'd like to do is run a
parameter query that displays many child records of one parent record
that permits multiple parameter entries for the primary key. Can this
be done?- Hide quoted text -

- Show quoted text -

Thanks, Allen!

Not really clear about that, John.

Now you know how I feel g

You have a form with:
- a combo
- a list box
- a subform

Not exactly. No subform.
cbPKWTID:
SELECT tblProfiles.txtProfileID, tblProfiles.Version,
tblProfiles.Description FROM tblProfiles WHERE
(((tblProfiles.Type)="PKCALC")) ORDER BY tblProfiles.txtProfileID;

lstFGID:
SELECT tblPKProfilesAssociations.ProfilesAssociations AS FGIDs,
tblProfiles.Description FROM tblProfiles INNER JOIN
tblPKProfilesAssociations ON tblProfiles.txtProfileID =
tblPKProfilesAssociations.ProfilesAssociations WHERE
(((tblPKProfilesAssociations.txtProfileID)=[Forms]!
[frmQueryPKWTCalcsFGs].[Form]![cbPKWTID])) ORDER BY
tblPKProfilesAssociations.ProfilesAssociations;

Report:
SELECT tblProfiles.txtProfileID FROM tblProfiles; (more than this but
I chopped for simplicity)
The subreport of this report has the query behind it that's filtered
by the listbox.

Therefore, the report needs to open where txtProfileID = cbPKWTID and
the subreport needs to return the filtered child records. Hope that
helps! Knowing this doesn't help me much!

I'm sorry. I'm struggling to wrap my brain around this! I hope you can
help because I feel like I'm almost there.

I'm going with the listbox. My design has a bit of a twist. My form
includes a combobox that queries parent records and then requeries the
listbox to the parent's child records. This works fine and it's pretty
cool as I've never really had a use for a list box before!

What I'm struggling with is the botton code. The above design results
in opening the report according to the listbox as well as the parent
record in the combobox.

Here are the control names:
cbPKWTID
lstFGID

I'm really at a loss as to how to plug all of this into the code. And
after reading Michel's suggestion and your "impreovement" to it -
well, I'm stumped. I've plugged in what my field names and report name
but could you please provide someguidance with all of the Dim's? I
suspect that my design above complicates things...?

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
* * 'Purpose: *Open the report filtered to the items selected in the
list box.
* * 'Author: * Allen J Browne, 2004. *http://allenbrowne.com
* * Dim varItem As Variant * * *'Selected items
* * Dim strWhere As String * * *'String to use as WhereCondition
* * Dim strDescrip As String * *'Description of WhereCondition
* * Dim lngLen As Long * * * * *'Length of string
* * Dim strDelim As String * * *'Delimiter for this field type.
* * Dim strDoc As String * * * *'rptPKWeightCalculatorASSsFGs..

* * 'strDelim = """" * * * * * *'Delimiter appropriate to field type.
See note 1.
* * strDoc = "Associated Finished Goods"

* * 'Loop through the ItemsSelected in the list box.
* * With Me.lstFGID
* * * * For Each varItem In .ItemsSelected
* * * * * * If Not IsNull(varItem) Then
* * * * * * * * 'Build up the filter from the bound column (hidden).
* * * * * * * * strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
* * * * * * * * 'Build up the description from the text in the visible
column. See note 2.
* * * * * * * * strDescrip = strDescrip & """" & .Column(1, varItem) &
""", "
* * * * * * End If
* * * * Next
* * End With

* * 'Remove trailing comma. Add field name, IN operator, and brackets..
* * lngLen = Len(strWhere) - 1
* * If lngLen 0 Then
* * * * strWhere = "[FGIDs] IN (" & Left$(strWhere, lngLen) & ")"
* * * * lngLen = Len(strDescrip) - 2
* * * * If lngLen 0 Then
* * * * * * strDescrip = "FinishedGoodIDs: " & Left$(strDescrip,
lngLen)
* * * * End If
* * End If

* * 'Report will not filter if open, so close it. For Access 97, see
note 3.
* * If CurrentProject.AllReports(strDoc).IsLoaded Then
* * * * DoCmd.Close acReport, strDoc
* * End If

* * 'Omit the last argument for Access 2000 and earlier. See note
4.
* * DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
* * Exit Sub

Err_Handler:
* * If Err.Number 2501 Then *'Ignore "Report cancelled" error.
* * * * MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
* * End If
* * Resume Exit_Handler
End Sub

On Jun 26, 12:27 pm, "Allen Browne"
wrote:



Michel, that's just too clever. :-)

To handle the spaces a user might enter:
PARAMETERS WotNum Text ( 255 );
SELECT ClientNum
FROM tblClient
WHERE "," & Replace([WotNum], " ", "") & "," Like "*," & [ClientNum] &
",*";

Tested with a local table of 15k clients: quite usable performance.

Thanks for posting: you've provided my fun for this evening.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message

...

There are some 'way around', but that can be slow.

A possible one is to pass the parameter: "12345,12348,12349"
(note there is no space after the comas)

And to use the query:
... WHERE ("," & parameter & ",") LIKE ("*," & id & ",*")

ie, use the field as a pattern and the parameter as left argument of
LIKE.
(Generally we do the reverse, the field is at the left and the parameter
is the pattern, at the right, of the operator LIKE).

Vanderghast, Access MVP

"Allen Browne" wrote in message
...
No: you can't do that John. A parameter can only be a value; it cannot
be a mix of various values, separators, operators, ...

You could get the report you want by building a WhereCondition string
to
filter the report. Here's an example:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

wrote in message
....
I know how to create a parameter query. What I'd like to do is run a
parameter query that displays many child records of one parent record
that permits multiple parameter entries for the primary key. Can this
be done?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

You have a form named frmQueryPKWTCalcsFGs.

It has a combo named cbPKWTID, with RowSource:
SELECT tblProfiles.txtProfileID,
tblProfiles.Version,
tblProfiles.Description
FROM tblProfiles
WHERE tblProfiles.Type = "PKCALC")
ORDER BY tblProfiles.txtProfileID;

It also has a listbox named lstFGID, with RowSource:
SELECT tblPKProfilesAssociations.ProfilesAssociations AS FGIDs,
tblProfiles.Description
FROM tblProfiles INNER JOIN tblPKProfilesAssociations
ON tblProfiles.txtProfileID =
tblPKProfilesAssociations.ProfilesAssociations
WHERE tblPKProfilesAssociations.txtProfileID =
[Forms]![frmQueryPKWTCalcsFGs].[Form]![cbPKWTID]
ORDER BY tblPKProfilesAssociations.ProfilesAssociations;

You open a report filtered by the combo.
The report has a subreport.
You want the subreport to show:
a) ALL records in the list box? or
b) just the SELECTED records in the mulit-select list box?

If (a), just include the combo's bound field (txtProfileID) in the
LinkMasterFields of the subreport control, with the matching field from the
subreport in LinkChildFields.

If (b), there are a few options:
1. The simplest would be to combine the subreport and main report into one,
so you can easily pass the WhereConditon string from the function you
already have.

2. It would be possible to write a VBA function that returns TRUE if the
current value is selected in the list box, else false. You call the function
in the WHERE clause of the subreport's query, passing in the ID to be
tested. This kind of thing:
Public Function IncludeProfile(ProfileID AS Variant) AS Boolean
and call it:
WHERE IncludeProfile([txtProfileID])

3. If those approaches don't work, you can build the SQL string for the
subreport's query, and assign it to the SQL property of the QueryDef:
CurrentDb.QueryDefs("Sub1Query").SQL = "SELECT ...

HTH
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

wrote in message
...
Thanks, Allen!

Not really clear about that, John.

Now you know how I feel g

You have a form with:
- a combo
- a list box
- a subform

Not exactly. No subform.
cbPKWTID:
SELECT tblProfiles.txtProfileID, tblProfiles.Version,
tblProfiles.Description FROM tblProfiles WHERE
(((tblProfiles.Type)="PKCALC")) ORDER BY tblProfiles.txtProfileID;

lstFGID:
SELECT tblPKProfilesAssociations.ProfilesAssociations AS FGIDs,
tblProfiles.Description FROM tblProfiles INNER JOIN
tblPKProfilesAssociations ON tblProfiles.txtProfileID =
tblPKProfilesAssociations.ProfilesAssociations WHERE
(((tblPKProfilesAssociations.txtProfileID)=[Forms]!
[frmQueryPKWTCalcsFGs].[Form]![cbPKWTID])) ORDER BY
tblPKProfilesAssociations.ProfilesAssociations;

Report:
SELECT tblProfiles.txtProfileID FROM tblProfiles; (more than this but
I chopped for simplicity)
The subreport of this report has the query behind it that's filtered
by the listbox.

Therefore, the report needs to open where txtProfileID = cbPKWTID and
the subreport needs to return the filtered child records. Hope that
helps! Knowing this doesn't help me much!

I'm sorry. I'm struggling to wrap my brain around this! I hope you can
help because I feel like I'm almost there.

I'm going with the listbox. My design has a bit of a twist. My form
includes a combobox that queries parent records and then requeries the
listbox to the parent's child records. This works fine and it's pretty
cool as I've never really had a use for a list box before!

What I'm struggling with is the botton code. The above design results
in opening the report according to the listbox as well as the parent
record in the combobox.

Here are the control names:
cbPKWTID
lstFGID

I'm really at a loss as to how to plug all of this into the code. And
after reading Michel's suggestion and your "impreovement" to it -
well, I'm stumped. I've plugged in what my field names and report name
but could you please provide someguidance with all of the Dim's? I
suspect that my design above complicates things...?

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the
list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'rptPKWeightCalculatorASSsFGs.

'strDelim = """" 'Delimiter appropriate to field type.
See note 1.
strDoc = "Associated Finished Goods"

'Loop through the ItemsSelected in the list box.
With Me.lstFGID
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) &
""", "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen 0 Then
strWhere = "[FGIDs] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen 0 Then
strDescrip = "FinishedGoodIDs: " & Left$(strDescrip,
lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see
note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note
4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub

On Jun 26, 12:27 pm, "Allen Browne"
wrote:



Michel, that's just too clever. :-)

To handle the spaces a user might enter:
PARAMETERS WotNum Text ( 255 );
SELECT ClientNum
FROM tblClient
WHERE "," & Replace([WotNum], " ", "") & "," Like "*," & [ClientNum] &
",*";

Tested with a local table of 15k clients: quite usable performance.

Thanks for posting: you've provided my fun for this evening.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message

...

There are some 'way around', but that can be slow.

A possible one is to pass the parameter: "12345,12348,12349"
(note there is no space after the comas)

And to use the query:
... WHERE ("," & parameter & ",") LIKE ("*," & id & ",*")

ie, use the field as a pattern and the parameter as left argument of
LIKE.
(Generally we do the reverse, the field is at the left and the
parameter
is the pattern, at the right, of the operator LIKE).

Vanderghast, Access MVP

"Allen Browne" wrote in message
...
No: you can't do that John. A parameter can only be a value; it
cannot
be a mix of various values, separators, operators, ...

You could get the report you want by building a WhereCondition string
to
filter the report. Here's an example:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

wrote in message
...
I know how to create a parameter query. What I'd like to do is run a
parameter query that displays many child records of one parent
record
that permits multiple parameter entries for the primary key. Can
this
be done?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -