If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
parameter query
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! |
#3
|
|||
|
|||
parameter query
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! |
#4
|
|||
|
|||
parameter query
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? |
#5
|
|||
|
|||
parameter query
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 - |
#6
|
|||
|
|||
parameter query
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 - |
#7
|
|||
|
|||
parameter query
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 - |
#8
|
|||
|
|||
parameter query
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 - |
#9
|
|||
|
|||
parameter query
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 - |
#10
|
|||
|
|||
parameter query
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 - |
|
Thread Tools | |
Display Modes | |
|
|