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 |
#11
|
|||
|
|||
parameter query
On Jun 27, 11:52*am, "Allen Browne"
wrote: 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 (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. Hi, Allen. Well, I've been pecking at this a little this morning. The above is true (although I've made a couple design changes that really don't affect anything and I don't want to post them so as to not add any confusion). I followed your advice above regarding combining the report and subreport. Here's my button event: Private Sub cmdPreview_Click() On Error GoTo Err_cmdPreview_Click Dim stDocName As String Dim strWhere As String strWhere = "[PKWTID] = """ & _ Forms![frmQueryPKWTCalcsFGs]![PKWTID] & """" DoCmd.OpenReport "rptPKWeightCalculatorASSsFGs", acPreview _ , , strWhere Exit_cmdPreview_Click: Exit Sub Err_cmdPreview_Click: MsgBox Err.Description Resume Exit_cmdPreview_Click End Sub This properly returns ALL records in the list box. Now comes the tricky part that is still way over my head. So I need to plug in the following code into the button? If I'm on the right track could you possibly put it into Access for Dummies terms? Thanks!!! 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 'Name of report to open. 'strDelim = """" 'Delimiter appropriate to field type. See note 1. strDoc = "Products by Category" 'Loop through the ItemsSelected in the list box. With Me.lstCategory 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 = "[CategoryID] IN (" & Left$(strWhere, lngLen) & ")" lngLen = Len(strDescrip) - 2 If lngLen 0 Then strDescrip = "Categories: " & 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 |
#12
|
|||
|
|||
parameter query
In the first code, strWhere is set to just one field.
In the 2nd code, you need to generate something like: strWhere = "[PKWTID] IN (""a"", ""b"", ""c"")" You will do that by looping through the ItemsSelected in the list box, as in the example. I'm going to have to let you put it together and test it, John. -- 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 ... Here's my button event: Private Sub cmdPreview_Click() On Error GoTo Err_cmdPreview_Click Dim stDocName As String Dim strWhere As String strWhere = "[PKWTID] = """ & _ Forms![frmQueryPKWTCalcsFGs]![PKWTID] & """" DoCmd.OpenReport "rptPKWeightCalculatorASSsFGs", acPreview _ , , strWhere Exit_cmdPreview_Click: Exit Sub Err_cmdPreview_Click: MsgBox Err.Description Resume Exit_cmdPreview_Click End Sub This properly returns ALL records in the list box. Now comes the tricky part that is still way over my head. So I need to plug in the following code into the button? If I'm on the right track could you possibly put it into Access for Dummies terms? Thanks!!! 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 'Name of report to open. 'strDelim = """" 'Delimiter appropriate to field type. See note 1. strDoc = "Products by Category" 'Loop through the ItemsSelected in the list box. With Me.lstCategory 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 = "[CategoryID] IN (" & Left$(strWhere, lngLen) & ")" lngLen = Len(strDescrip) - 2 If lngLen 0 Then strDescrip = "Categories: " & 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 |
#13
|
|||
|
|||
parameter query
Thanks for the support, Allen!
In the first code, strWhere is set to just one field. I'm not sure what you mean by this. Are you referring to: 'Build up the filter from the bound column. strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & "," In the 2nd code, you need to generate something like: strWhere = "[PKWTID] IN (""a"", ""b"", ""c"")" I'm completely thrown by this, too. Sorry, I'm just not follwing all of this as my skills are obviously lacking! Allow me to run through the current design, etc. I'm using Access 2003. Form: frmQueryPKWTCalcsFGs_Select SELECT tblProfiles.txtProfileID AS PKWTID, tblProfiles.Description, tblProfiles.Type FROM tblProfiles WHERE (((tblProfiles.Type)="PKCALC")); Combobox: cbPKWTID SELECT tblProfiles.txtProfileID, tblProfiles.Version, tblProfiles.Description FROM tblProfiles WHERE (((tblProfiles.Type)="PKCALC")) ORDER BY tblProfiles.txtProfileID; Listbox: lstFGID SELECT tblPKProfilesAssociations.ProfilesAssociations AS FGIDs, tblProfiles.Description FROM tblProfiles INNER JOIN tblPKProfilesAssociations ON tblProfiles.txtProfileID = tblPKProfilesAssociations.ProfilesAssociations WHERE (((tblPKProfilesAssociations.txtProfileID)=[Forms]! [frmQueryPKWTCalcsFGs_Select].[Form]![cbPKWTID])) ORDER BY tblPKProfilesAssociations.ProfilesAssociations; Preview button: Private Sub cmdPreview_Click() Dim i As Integer Dim strForm As String For i = 1 To CurrentProject.AllForms.Count If CurrentProject.AllForms(i - 1).IsLoaded Then strForm = CurrentProject.AllForms(i - 1).Name If strForm "frmQueryPKWTCalcsFGs_Select" And strForm "Marzetti Main Menu" Then DoCmd.Close acForm, strForm, acSaveNo End If End If Next i 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 strFGID 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 'Name of report to open. strDelim = """" strDoc = "rptPKWeightCalculatorASSsFGs_Select" '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. strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & "," 'Build up the FGIDs from the text in the FGIDs column. strFGID = strFGID & """" & .Column(0, 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 = "[PKWTID] IN (" & Left$(strWhere, lngLen) & ")" lngLen = Len(strDescrip) - 2 If lngLen 0 Then strFGID = "FGIDs: " & Left$(strFGID, lngLen) End If End If DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strFGID 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 I'm sure I've butchered the button code and it's not clear to me at all what I've done wrong however two things happen: - If I make a selection in the listbox the report opens BUT every field is either blank or contains #Error. I placed a textbox called OpenArgs (and if you're familiar with JOURNEY's 1980's hit Open Arms you MUST chuckle) in the report and it properly displays the selected IDs. - If I leave the listbox blank the report opens and returns every PKWTID and all of their associated FGIDs. Do you see at all where I've goofed? Thanks again! |
#14
|
|||
|
|||
parameter query
Thanks for the support, Allen!
In the first code, strWhere is set to just one field. I'm not sure what you mean by this. Are you referring to: 'Build up the filter from the bound column. strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & "," In the 2nd code, you need to generate something like: strWhere = "[PKWTID] IN (""a"", ""b"", ""c"")" I'm completely thrown by this, too. Sorry, I'm just not follwing all of this as my skills are obviously lacking! Allow me to run through the current design, etc. I'm using Access 2003. Form: frmQueryPKWTCalcsFGs_Select SELECT tblProfiles.txtProfileID AS PKWTID, tblProfiles.Description, tblProfiles.Type FROM tblProfiles WHERE (((tblProfiles.Type)="PKCALC")); Combobox: cbPKWTID SELECT tblProfiles.txtProfileID, tblProfiles.Version, tblProfiles.Description FROM tblProfiles WHERE (((tblProfiles.Type)="PKCALC")) ORDER BY tblProfiles.txtProfileID; Listbox: lstFGID SELECT tblPKProfilesAssociations.ProfilesAssociations AS FGIDs, tblProfiles.Description FROM tblProfiles INNER JOIN tblPKProfilesAssociations ON tblProfiles.txtProfileID = tblPKProfilesAssociations.ProfilesAssociations WHERE (((tblPKProfilesAssociations.txtProfileID)=[Forms]! [frmQueryPKWTCalcsFGs_Select].[Form]![cbPKWTID])) ORDER BY tblPKProfilesAssociations.ProfilesAssociations; Preview button: Private Sub cmdPreview_Click() Dim i As Integer Dim strForm As String For i = 1 To CurrentProject.AllForms.Count If CurrentProject.AllForms(i - 1).IsLoaded Then strForm = CurrentProject.AllForms(i - 1).Name If strForm "frmQueryPKWTCalcsFGs_Select" And strForm "Marzetti Main Menu" Then DoCmd.Close acForm, strForm, acSaveNo End If End If Next i 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 strFGID 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 'Name of report to open. strDelim = """" strDoc = "rptPKWeightCalculatorASSsFGs_Select" '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. strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & "," 'Build up the FGIDs from the text in the FGIDs column. strFGID = strFGID & """" & .Column(0, 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 = "[PKWTID] IN (" & Left$(strWhere, lngLen) & ")" lngLen = Len(strDescrip) - 2 If lngLen 0 Then strFGID = "FGIDs: " & Left$(strFGID, lngLen) End If End If DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strFGID 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 I'm sure I've butchered the button code and it's not clear to me at all what I've done wrong however two things happen: - If I make a selection in the listbox the report opens BUT every field is either blank or contains #Error. I placed a textbox called OpenArgs (and if you're familiar with JOURNEY's 1980's hit Open Arms you MUST chuckle) in the report and it properly displays the selected IDs. - If I do NOT make a selection in the listbox the report opens and returns every PKWTID and all of their associated FGIDs. Do you see at all where I've goofed? Thanks again! |
|
Thread Tools | |
Display Modes | |
|
|