Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using strWhere after the Fact (Access 2003)

    Edited by HansV to provide link to post - it's very easy (see <!help=19>Help 19<!/help>) and saves the reader unnecessary work.

    Based on code put together via previous <post:=688,770>post 688,770</post:> and much, much help from Hans, I eventually ended up with the following codes at the end of the routine which performed perfectly:

    '=========================================
    ' This is where all the Strings are combined
    '=========================================
    strWhere = strWhereFunction & strWhereCustomer & strWhereProduct & strWhereService
    If Not strWhere = "" Then
    strWhere = Mid(strWhere, 5)
    If DCount("*", "qry 01 CN - 00 - AGM", strWhere) = 0 Then
    MsgBox ("Your combination of choices couldn't find any records." & vbNewLine & " " & vbNewLine & " Please click OK then Red Button"), vbInformation, " No Records Found !!"
    Else
    Me.Filter = strWhere
    Me.FilterOn = True
    End If
    End If



    There are three things which I'm, without much success , trying to do at this point:

    Display a subForm on my Parent Search form which will display a group of fields using the same source as the Parent search form query, but displaying only those records selected via the str Where created in the above code.

    Use the strWhere to produce various reports via Report Command buttons on the form and before exiting the form.

    And, to be able to "Save" the selection that created the strWhere for future use.

    I'd also like a pony, but you can't have everything.

    Thanks in advance for any help with this,
    Cheers,
    Andy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using strWhere after the Fact (Access 2003)

    a) Let's say that the record source of the parent form is qryParent. I'm aware that its actual name will be different, this is just for illustration purposes.
    Create a subform based on qryParent. Let's say that you name this subform sbfFiltered and that this is also the name of the subform as a control on the main form.
    Use the following lines to change the record source of the subform:
    <code>
    If strWhere = "" Then
    Me.sbfFiltered.Form.RecordSource = "qryParent"
    Else
    Me.sbfFiltered.Form.RecordSource = "SELECT * FROM qryParent WHERE " & strWhere
    End If
    </code>
    You can create a report based on qryParent. Let's say that you name the report rptData. Open it with the following line from your parent form:
    <code>
    DoCmd.OpenReport ReportName:="rptData", View:=acViewPreview, WhereCondition:=strWhere
    </code>
    c) Do you want to save the individual conditions, or strWhere, or the actual data selected by strWhere?

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using strWhere after the Fact (Access 2003)

    Hans,

    Thanks for the responses as always. I just got back and haven't had a chance to apply your suggested, and I'm sure will be accurate responses. With respect to c) the ideal situation is to save and hopefully name the conditions so that they could be used in the future. The data will change each time, but a variety of conditions could be reused if created and named correctly.

    Thanks again and off I go into your code,
    Cheers,
    Andy

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using strWhere after the Fact (Access 2003)

    You could create a table tblConditions with the following fields:

    ConditionID: autonumber (the primary key)
    Description: text or memo (a short description of the condition)
    QueryName: text (the name of the query - or table - for which the condition can be used)
    Condition: memo (the value of the strWhere variable)
    UserName: string (optional, the name of the user who stored this condition in the table)

    You could write code to create a new record in this table and to fill its fields, and code to display the list of conditions in a combo box or list box and let the user retrieve one.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using strWhere after the Fact (Access 2003)

    Hans,

    Part A was awesome. I actually renamed my queries, forms, etc. to match your code first to make sure all was well. And it was. I found it unusual to have both the subform and the control name the same, but it surely did work.

    Part B is giving me a problem and probably because I may not have described the situation correctly. My thought was to run through the filtration process, verify the accuracy of the selection on the subform, and, while the records were still filtered, click on a command button to run the report. When I tried this using your code:

    DoCmd.OpenReport ReportName:="rpt 01 CN - 16 Functions", View:=acViewPreview, WhereCondition:=strWhere

    I got a Compile Error: Variable not defined with the strWhere highlighted. It appears that once I'm out of the whole selection procedure, I can't refer back to that strwhere. When I inserted the DoCmd line inside of the procedure, the report ran correctly. That, however, in real life wouldn't work for me. I have to be able to refer to the strWhere within the procedure from one or more command buttons.

    I may, as I often have, misconstrued your instructions. I hope.

    I haven't gotten to your followup message re Part C and didn't want to until I got these other items clarified.

    Thanks and
    Cheers,
    Andy

  6. #6
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using strWhere after the Fact (Access 2003)

    Hans,

    While I was dealing some more with the Part B the report portion, I took a look at your suggested approach to create saved queries. This is something that I won't get at until tomorrow, but it looks really interesting. I'm just curious as to why no one seems to be doing this sort of "Save the Really Neat Variable Query 'til Later". thing like I am. OK, I just read the last sentence and can understand why.
    Cheers,
    Andy

  7. #7
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using strWhere after the Fact (Access 2003)

    <hr>I got a Compile Error: Variable not defined with the strWhere highlighted. It appears that once I'm out of the whole selection procedure, I can't refer back to that strwhere. When I inserted the DoCmd line inside of the procedure, the report ran correctly. That, however, in real life wouldn't work for me. I have to be able to refer to the strWhere within the procedure from one or more command buttons.
    <hr>

    I suspect your problem is scope. You need to declare strWhere as a module level or possibly project level variable as opposed to procedure level. Module level declarations are at the top of the module and project level declarations are in a standard module declared as Public strWhere as String.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using strWhere after the Fact (Access 2003)

    What SteveH says...

  9. #9
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using strWhere after the Fact (Access 2003)

    Steve & Hans,

    OK, I'm back in the barrel.

    Thank you for the direction pointers. I'm sure that I'll be doing some Forum prowling before I get these suggestions applied.

    You'll be the second and third to know if I have a problem.

    Thanks as always for your help
    Cheers,
    Andy

  10. #10
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using strWhere after the Fact (Access 2003)

    Hans,

    Just a quick note to let you know that I just noticed the Teeny Weeny Linking HowTo Note at the top of my original post.

    Thank you, I promise to do gooder in the future,
    Cheers,
    Andy

  11. #11
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using strWhere after the Fact (Access 2003)

    I guess it takes big pictures to make things clearer for my elderly eyes.

    I should have more clearly illustrated my original question by showing you where I did my declarations?

    Private Sub butSelect2_Click()
    Dim varItem As Variant
    Dim strWhereFunction As String
    Dim strWhereCustomer As String
    Dim strWhereProduct As String
    Dim strWhereService As String
    Dim strWhere As String

    This is the strWhere that works marvelously throughout this butSelect2 procedure, but to reiterate my problem, it was when I tried to refer to it in:

    Private Sub prvReport_Click()
    DoCmd.OpenReport ReportName:="rptInvoice", View:=acViewPreview, WhereCondition:=strWhere
    End Sub

    That I ran into my "Compile Error: Variable not defined" problem. I think what you two patient gentlemen are trying to drum into my head is that my:

    Dim strWhere As String

    has to placed elsewhere in order for it to work inside and outside of butSelect2 routine.

    My apologies for appearing dense, but I did go back through the Forum to try and find something similar, but lucky me, I appear to be the only one that doesn't understand these procedures.

    I'd appreciate further patience and insites,
    Cheers,
    Andy

  12. #12
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using strWhere after the Fact (Access 2003)

    Go to the very top of the module (before any procedures) (which it calls General Declarations) - and cut and paste your 'Dim strWhere as String' declaration there.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  13. #13
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using strWhere after the Fact (Access 2003)

    Steve,

    I won't even begin to tell you where I tried to do what you suggested, however in the wrong, way too-complicated, format.

    <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15> <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15> <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

    That was just way too easy.

    Thanks most kindly,
    Cheers,
    Andy

  14. #14
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using strWhere after the Fact (Access 2003)

    Steve,

    Just to impose one more time.

    This is another one of those cases where I think I have all the correct pieces to the puzzle, but can't quite fit them together.

    In this scenario, strWhereInvDate is referring to a date field, F11_Invoice_Date.
    What I'm obviously not doing is applying the formatting correctly.

    I have

    Const conJetDate = "#mm/dd/yyyy#" 'The format expected for dates in a JET query string.

    Ready and waiting to be referred to.

    But just not applying it in,


    For Each varItem In Me.pickInvoiceDate.ItemsSelected
    strWhereInvDate = strWhereInvDate & ", " & Chr(34) & _
    Me.pickInvoiceDate.ItemData(varItem) & Chr(34)
    Next varItem
    If Not strWhereInvDate = "" Then
    strWhereInvDate = Mid(strWhereInvDate, 3)
    strWhereInvDate = "AND F11_Invoice_Date In (" & strWhereInvDate & ")"
    End If

    I'd appreciate your help with this,
    Cheers,
    Andy

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using strWhere after the Fact (Access 2003)

    You shouldn't use Chr(34) here - this is the double quote character " needed around text values, not around date values. Change

    strWhereInvDate = strWhereInvDate & ", " & Chr(34) & _
    Me.pickInvoiceDate.ItemData(varItem) & Chr(34)

    to

    strWhereInvDate = strWhereInvDate & ", " & _
    Format(Me.pickInvoiceDate.ItemData(varItem), conJetDate)

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •