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
|
|||
|
|||
WHERE help for button code
Thanks in advance for your help. I posted this in a previous thread
but I'm afraid that it's faded into oblivion. Two things happen when I click my preview button: - 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. I'm fairly sure that that this is a WHERE problem but I'm lost as to how to correct it. The button code follows but first a little design info. I'm using Access 2003. The button is on the following form that has the following controls. 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 |
#3
|
|||
|
|||
WHERE help for button code
Hi, Crystal!
Thanks for the response. Hi jlute (what is your name?) My name is John and I also post under JohnLute at the MS Discussion Forum however I regularly experience connectivity issues with that site and therefore post at Google under this name. "Type" is a reserved word Problem names and reserved words in Access, by Allen Brownehttp://www.allenbrowne.com/Ap****ueBadWord.html Yeah. I named it that several years ago when I didn't know better. It's never posed a problem however. it is difficult to follow the code without your database to try it on... put this statement before your OpenReport statement debug.print "strWhe " & strWhere debug.print "strFGID: " & strFGID then, press CTRL-G after you run it and see what was written to the Debug (Immediate) window chances are, that manual inspection of what you are constructing will solve the issue -- if it does not, please post what it constructed back here That's a nifty way to go about things. I gave it a whirl and this is what was in the Immediate window: strWhe strFGID: This doesn't appear to be of any help or is it saying that there's no problem? |
#4
|
|||
|
|||
WHERE help for button code
Hi John,
what it is saying is that neither variable has any value -- that means you are not passing anything to OpenReport ~~~ would you like to go through each statement of the code so that you can understand what is happening and, therefore, fix the problem? If so, read this first: Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace Warm Regards, Crystal remote programming and training * (: have an awesome day * wrote: Hi, Crystal! Thanks for the response. Hi jlute (what is your name?) My name is John and I also post under JohnLute at the MS Discussion Forum however I regularly experience connectivity issues with that site and therefore post at Google under this name. "Type" is a reserved word Problem names and reserved words in Access, by Allen Brownehttp://www.allenbrowne.com/Ap****ueBadWord.html Yeah. I named it that several years ago when I didn't know better. It's never posed a problem however. it is difficult to follow the code without your database to try it on... put this statement before your OpenReport statement debug.print "strWhe " & strWhere debug.print "strFGID: " & strFGID then, press CTRL-G after you run it and see what was written to the Debug (Immediate) window chances are, that manual inspection of what you are constructing will solve the issue -- if it does not, please post what it constructed back here That's a nifty way to go about things. I gave it a whirl and this is what was in the Immediate window: strWhe strFGID: This doesn't appear to be of any help or is it saying that there's no problem? |
#5
|
|||
|
|||
WHERE help for button code
On Jul 3, 12:40*pm, strive4peace wrote:
Hi John, what it is saying is that neither variable has any value * -- that means you are not passing anything to OpenReport ~~~ would you like to go through each statement of the code so that you can understand what is happening and, therefore, fix the problem? *If so, read this first: Access Basics 8-part free tutorial that covers essentials in Accesshttp://www.AccessMVP..com/strive4peace Thanks, Crystal. I was actually looking through your 8-part tutorial earlier today and found it very informative. I'm actually very comfortable with Access except writing/comprehending SQL and Modules and Macros. Everything else I'm fairly skilled at. My database has 450+ tables; 400+ forms and 550+ reports that are entirely my design so I've seen quite a bit! This particular challenge is difficult for me due to my poor SQL skills. I don't always understand what a long line of code such as this is doing - despite my "hack" ability to actually write some primitive code. I'll review your SQL section and get back to you! Thanks! |
#6
|
|||
|
|||
WHERE help for button code
sounds good, John
Warm Regards, Crystal remote programming and training Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace * (: have an awesome day * wrote: On Jul 3, 12:40 pm, strive4peace wrote: Hi John, what it is saying is that neither variable has any value -- that means you are not passing anything to OpenReport ~~~ would you like to go through each statement of the code so that you can understand what is happening and, therefore, fix the problem? If so, read this first: Access Basics 8-part free tutorial that covers essentials in Accesshttp://www.AccessMVP.com/strive4peace Thanks, Crystal. I was actually looking through your 8-part tutorial earlier today and found it very informative. I'm actually very comfortable with Access except writing/comprehending SQL and Modules and Macros. Everything else I'm fairly skilled at. My database has 450+ tables; 400+ forms and 550+ reports that are entirely my design so I've seen quite a bit! This particular challenge is difficult for me due to my poor SQL skills. I don't always understand what a long line of code such as this is doing - despite my "hack" ability to actually write some primitive code. I'll review your SQL section and get back to you! Thanks! |
Thread Tools | |
Display Modes | |
|
|