Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Aug 2007
    Posts
    102

    Unanswered: How to use a converted macro (to function)

    have a form(PrintPreviewInvoices) that displays past invoices (in a listbox). I added a combo box control in order to filter timeframes;(today, this week, last week, this month, last month and all).
    I copied this macro filter from another db and then converted it to a function module (called PrintPreviewInvoices) in VB. I'm trying to properly set it up to be used as an 'On Change' event, but not real sure how to get the results I want. I'm sure how to call it. What I"ve tried so far isn't working. VB put the converted macro into a General area in the forms code:
    Code:
    PrintPreviewInvoices_PayFilter___On_Change()
    If (Forms!PrintPreviewInvoices!PayFilter = "Today") Then
            DoCmd.ApplyFilter "", "(Year([OrderDate])=Year(Date()) And Month([OrderDate])=Month(Date()) And Day([OrderDate])=Day(Date()))"
        End If
        If (Forms!PrintPreviewInvoices!PayFilter = "This Week") Then
            DoCmd.ApplyFilter "", "(Year([OrderDate])=Year(Date()) And DatePart(""ww"",[OrderDate],0)=DatePart(""ww"",Date(),0))"
        End If
        If (Forms!PrintPreviewInvoices!PayFilter = "Last Week") Then
            DoCmd.ApplyFilter "", "(Year([OrderDate])*53+DatePart(""ww"",[OrderDate],0)=(Year(Date())*53+DatePart(""ww"",Date(),0)-1))"
        End If
        If (Forms!PrintPreviewInvoices!PayFilter = "This Month") Then
            DoCmd.ApplyFilter "", "(Year([OrderDate])=Year(Date()) And Month([OrderDate])=Month(Date()))"
        End If
        If (Forms!PrintPreviewInvoices!PayFilter = "Last Month") Then
            DoCmd.ApplyFilter "", "(Year([OrderDate])=Year(Date()) And Month([OrderDate])=(Month(Date())-1))"
        End If
        If (Forms!PrintPreviewInvoices!PayFilter = "All") Then
            DoCmd.ShowAllRecords
        End If
     
     
    PrintPreviewInvoices_PayFilter___On_Change_Exit:
        Exit Function
     
    PrintPreviewInvoices_PayFilter___On_Change_Err:
        MsgBox Error$
        Resume PrintPreviewInvoices_PayFilter___On_Change_Exit
     
    End Function
    My combo box control (value list) is called PayFilter:
    Code:
    "All";"Today";"This Week";"Last Week";"This Month";"Last Month"
    In the Main form "PrintPreviewInvoices", I have record source as the query ('OrdersList') which supports the 'listbox' (displays past invoices)...and here is the filter (copied from the other db):
    Code:
    (Year([OrderDate])=Year(Date()) And Month([OrderDate])=Month(Date()) And Day([OrderDate])=Day(Date()))
    I"m not sure I need to keep this with the new function module in VB.
    Also, in addition to the use of a filter, is there a way to search through the listbox for a particular customer. I know I would need an unbound combo box for that as well; I tried many times, but couldn't connect the search combo with the Orderslist. Here's that SQL
    Code:
    SELECT DISTINCTROW Customers.FirstName & " " & [LastName] AS [Customer Name], Orders.OrderID, Orders.OrderDate
    Any ideas???

    I tried this (but giving me compile error - expected variable or procedure, not module)
    Code:
    Private Sub PayFilter_Change()
    call PrintPreviewInvoices_mod(PayFilter.Value)
    End Sub

    Thanks so much...
    Last edited by gvee; 08-29-07 at 04:29. Reason: Tidied up with [CODE] tags

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Your first block of code mixes a sub and a function - I doubt that will even run!
    Also, you should probably take a look at CASE statements to help tidy up your "if" code.
    Code:
    Select Case Forms!PrintPreviewInvoices!PayFilter
        Case "Today"
            DoCmd.ApplyFilter "", "(Year([OrderDate])=Year(Date()) And Month([OrderDate])=Month(Date()) And Day([OrderDate])=Day(Date()))"
        Case "This Week"
            DoCmd.ApplyFilter "", "(Year([OrderDate])=Year(Date()) And DatePart(""ww"",[OrderDate],0)=DatePart(""ww"",Date(),0))"
    End Select
    If you want to make this a function then you probably want something like this
    Code:
    Function exampleFunction(argPayFilter As String, argOrderDate As Date)
    
        Select Case argPayFilter
            Case "Today"
                DoCmd.ApplyFilter "", "(Year([argOrderDate])=Year(Date()) And Month([argOrderDate])=Month(Date()) And Day([argOrderDate])=Day(Date()))"
            Case "This Week"
                DoCmd.ApplyFilter "", "(Year([argOrderDate])=Year(Date()) And DatePart(""ww"",[argOrderDate],0)=DatePart(""ww"",Date(),0))"
        End Select
    
    End Function
    And then call the function like so:
    Code:
    Call exampleFunction(PayFilter.Value, Now())
    George
    Home | Blog

  3. #3
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    George's example is good - you could also use the afterupdate event of the combo box to trigger the event

    If me!mycombo = "All" then
    me!listbox.rowsource = "Select * from mytable"
    me!listbox.requery
    end if

    If these are records on a form - (continuous form I would hope) then do it this way
    If me!mycombo = "All" then
    me.recordsource = "Select * from mytable"
    me.requery
    end if
    Dale Houston, TX

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Using the case statement, (or ElseIf's would do, I suppose) of course
    George
    Home | Blog

  5. #5
    Join Date
    Aug 2007
    Posts
    102

    Set up a case statement, but filter still not working

    Hi GeorgeV and AXSProg,

    btw, I haven't tried your code (AXSProg) until I can get the 'PayFilter case statement working.

    Georgev, here's what I did based on your suggestion:
    Code:
    Private Sub PayFilter_Change()
    Select Case Forms!PrintPreviewInvoices!PayFilter
        Case "Today"
            DoCmd.ApplyFilter "PayFilter", "(Year([OrderDate])=Year(Date()) And Month([OrderDate])=Month(Date()) And Day([OrderDate])=Day(Date()))"
            Case "This Week"
            DoCmd.ApplyFilter "PayFilter", "DatePart(""ww"",[OrderDate])=DatePart(""ww"",Date()) and Year([OrderDate]) = Year(Date())"
        Case "Last Week"
            DoCmd.ApplyFilter "PayFilter", "Year([OrderDate])* 53 + DatePart(""ww"", [OrderDate]) = Year(Date())* 53 + DatePart(""ww"", Date()) - 1"
        Case "This Month"
            DoCmd.ApplyFilter "PayFilter", "Year([OrderDate]) = Year(Now()) And Month([OrderDate]) = Month(Now())"
        Case "Last Month"
            DoCmd.ApplyFilter "PayFilter", "Year([OrderDate])*12+ DatePart(""m"",[OrderDate])=Year(Date())*12 + DatePart(""m"", Date())-1"
            Case "All"
            DoCmd.ShowAllRecords
        End Select
    I'm still doing something wrong....Here's what I'm experiencing on this form.
    The form (PrintPreviewInvoices) is based on a query OrderList (Orders, Customers tables), uses 3 fields, OrderDate, OrderID, CustomerID. The form is filtered:
    Code:
    Year([OrderDate])*12+ DatePart("m",[OrderDate])=Year(Date())*12 + DatePart("m", Date())-1
    and Ordered by OrderDate

    On PrintPreviewInvoices are 2 controls and 3 command buttons:
    1st control is the 'PayFilter' using the 'On Change' Select/case statement above and 'row source' Value list ("Today";"This Week";"Last Week";"This Month";"Last Month";"All")

    2nd control is the actual listbox displaying all past order (called 'thelistbox) it uses a the row source, OrderList (same as form). Maybe that is a part of the problem.

    The cmd buttons are preview, print and cancel...No worry there.

    What's happening now is when I select a timeframe to filter on (either today, this week or last month) for some reason, the form goes blank....all the controls disappear. This does happen when I select All, last week or this month.....weird)...I played with those filters, been out to the Microsoft website....trying to make sure I have them set correctly...Can't figure it out.

    Also noticed, that the Navigation buttons aren't connected to the Orders' list. I can't navigate through the list. Must use the scroll bar. What have I overlooked? I know this is a mouthful...thanks so much for your effort.

    thanks
    imrosie

  6. #6
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    No case statement needed of filter needed with my code

    based on the combo box you requery the recordsource to meet the criteria
    Dale Houston, TX

  7. #7
    Join Date
    Aug 2007
    Posts
    102
    Hi axsprog,

    I'm not clear on how your code can do this...seems like a table (me.recordsource = "Select * from mytable") is required...I'm trying to map in my mind what you're referring to there...
    I'm a 'Newbie' so if you go too far over my head,,,I'm lost.

    I think you're referring to my 'OrderList' query, as a table?

    OK, just tried your code this way:
    Private Sub PayFilter_AfterUpdate()
    If Me!PayFilter = "All" Then
    Me!thelistbox.RowSource = "OrderList"
    Me!thelistbox.Requery
    End If
    End Sub

    It didn't work,,,,there's no filters anywhere. They were in the case/select statement. I'm not clear on what to do here.

    My filters aren't working properly it seems. Where, in your code, do the values for "All", "Today", "This Week", "Last Week",,,,etc. come from? thanks, but I'll go try it...I'm sure I'm missing something.
    imrosie
    Last edited by imrosie; 08-29-07 at 20:57.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I think Dale is implying that "DoCmd.ApplyFilter" is not a good way of doing things... and I agree!
    Code:
    Dim SQL As String
    Dim WhereClause As String
    
    SQL = "SELECT col1, col2, col3 FROM MyTable"
    
    Select Case Forms!PrintPreviewInvoices!PayFilter
        Case "Today"
            WhereClause = " WHERE year(SomeColumn) = year(Now()) AND .... "
        ...
            ...
        Case "All"
            WhereClause = ""
    End Select
    
    Me.MyListBox.RowSource = SQL & WhereClause
    George
    Home | Blog

  9. #9
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    Thank You Geo

    he needs to READ the pseudo-code

    rowsource = a select statement

    not criteria in an unidentified fieldname

    if me!combobox = "All" then
    me!listboxname.rowsource = "select * from mytable"
    me!listboxname.requery
    end if

    works for me --- I probably do that type of coding 2 - 4 times a day.

    I learned in 2.0 that working with filters sucks (can I say that?)

    too much overhead (thinking) in off and on and when to engage - who can filter, blah blah blah

    I really do not think I used filter methods in the last 10 years - however I do realize that for newbies and people who are not code heavy - they serve their purpose.
    Dale Houston, TX

  10. #10
    Join Date
    Aug 2007
    Posts
    102
    Hi GeorgeV,
    I obviously misunderstood AXSProg's response....Here is what I believe you're telling me to do.. I'm not sure if the 'whereclause'. is correct. This is a little confusing to me, since the payfilter combo box has the value list and the following sub would be run after update (suggested by AXSProg)..Orderlist is the query which PrintPreviewInvoices is built upon, not one table.I would suspect that PayFilter(?, with the Value list) should be declared???

    Code:
    Private Sub PayFilter_AfterUpdate()
    Dim SQL As String
    Dim PayFilter As String
    SQL = "Customer Name, OrderID, OrderDate FROM OrderList"
    Select Case Forms!PrintPreviewInvoices!PayFilter
        Case "Today"
            PayFilter ="(Year([OrderDate])=Year(Date()) And Month([OrderDate])=Month(Date()) And Day([OrderDate])=Day(Date()))"
            Case "This Week"
           PayFilter ="DatePart(""ww"",[OrderDate])=DatePart(""ww"",Date()) and Year([OrderDate]) = Year(Date())"
        Case "Last Week"
            PayFilter="Year([OrderDate])* 53 + DatePart(""ww"", [OrderDate]) = Year(Date())* 53 + DatePart(""ww"", Date()) - 1"
        Case "This Month"
            PayFilter="Year([OrderDate]) = Year(Now()) And Month([OrderDate]) = Month(Now())"
        Case "Last Month"
            PayFilter= "Year([OrderDate])*12+ DatePart(""m"",[OrderDate])=Year(Date())*12 + DatePart(""m"", Date())-1"
            Case "All"
            DoCmd.ShowAllRecords
        End Select
    End Sub
    Me.thelistbox.RowSource = SQL & PayFilter
    End Sub
    I tried this and with each selection of a value in the drop done list, the 'listbox' goes totally empty, nothing shows up as before...so I am still missing the big picture? thanks for your help.
    Last edited by imrosie; 08-30-07 at 09:12.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I don't see the word "WHERE" used in your above code.

    Stick a
    Code:
    Debug.Print SQL & PayFilter
    'OR
    MsgBox SQL & PayFilter
    Before you set the RowSource.
    (Debug.Print displays the result in the "Immediate window")

    And Dale - you're more than entitled to say that filters suck... I'd agree with you
    George
    Home | Blog

  12. #12
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    try this as a start
    make stored queries that display the data of all filter types
    qryAll
    qryCriteriaSomething
    qryCriteriaSomethingElse

    AfterUpdate of the combobox (which displays the Criteria - All, CriteriaSomething, CriteriaSomethingElse)

    If me!mycombobox = "All" then
    Me!mylistbox.rowsource = "qryAll"
    Me!mylistbox.requery
    elseif
    me!mycombobox = "CriteriaSomething" then
    Me!mylistbox.rowsource = "qryCriteriaSomething"
    Me!mylistbox.requery
    elseif
    me!mycombobox = "CriteriaSomethingElse" then
    Me!mylistbox.rowsource = "qryCriteriaSomethingElse"
    Me!mylistbox.requery
    end if

    If the requery is for a form and not a listbox
    instead of me!mylistbox.rowsource use...me.recordsource
    and the requery will be me.requery
    Dale Houston, TX

  13. #13
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    Oh I forgot - Good Morning people
    Dale Houston, TX

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Dale
    make stored queries that display the data of all filter types
    No, don't!
    Dynamic is the way to go here!
    George
    Home | Blog

  15. #15
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    "try this as a start " - to see how it works - only
    Dale Houston, TX

Posting Permissions

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