How to call a new function from converted macro

[imrosie]

Hello,

I 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:

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:
Here is the code: ( text )
"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 a filter (copied from the other db):

(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
SELECT DISTINCTROW Customers.FirstName & " " & [LastName] AS [Customer Name], Orders.OrderID, Orders.OrderDate
Thanks so much...
Rosie

[Denburt]

You have posted multiple questions in one post and that can make things a little confusing for both of us. I know how it is though. Hit a bump and work on the next piece till you get your next idea to try etc.

Lets see if I can be of some assistance.
(I am pretty sure you are referring to VBA and not VB)

I don't know if your table is set up so that order date is actually just a date or dat/time etc. so I will go with date only?

If (Forms!PrintPreviewInvoices!PayFilter = "Today") Then
DoCmd.ApplyFilter , "[OrderDate]=Dateserial(Year(Date()),Month(Date()) ,Day(Date()))"
End If
 

Does that help at all? The Dateserial function is a very useful function and can help alleviate a lot of issues. For Instance the last day of the previous month can be something like:

If (Forms!PrintPreviewInvoices!PayFilter = "Today") Then
DoCmd.ApplyFilter , "[OrderDate]=Dateserial(Year(Date()),Month(Date()) ,1-1)"
End If
 

Previous month:

If (Forms!PrintPreviewInvoices!PayFilter = "Today") Then
DoCmd.ApplyFilter , "[OrderDate]=Dateserial(Year(Date()),Month(Date())-1,Day(Date())"
End If
 

Let us know how it goes. once you get this we can work on your next question.

[imrosie]

Hello Denburt,

Here's what I did and additional info:

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
 

The form (PrintPreviewInvoices) is based on a query called OrderList (Orders, Customers tables), utilizes 3 fields; OrderDate, OrderID, CustomerID. The form is filtered like this

Year([OrderDate])*12+ DatePart("m",[OrderDate])=Year(Date())*12 + DatePart("m", Date())-1

:

Order By OrderDate (not real important, just fyi)
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.

What's happening now is that 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...I can't figure it out.

Also noticed, that the Navigation buttons aren't connected to the Orders list displayed. 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 time and effort.

thanks
Rosie


However, I'm still not doing something right because I'm experiencing the following on this form.

[Denburt]

Just reviewing the filters I think that they should work, at least for the most part. There are some things you may want to look at and that is the actual form design, such as nav buttons visible etc..

Also noticed, that the Navigation buttons aren't connected to the Orders list displayed. 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 time and effort.

That sounds like you are viewing the form as a continuous form thus forced to use a scroll bar to go from record to record. It sounds like you are looking for your form to be a single form and you navigate the records using buttons.

As far as the filters go when I face an issue such as what you are describing I manually put in a filter make sure it works then use that in my VBA code.

Are you familiar with debugging the app? If not you can use a msgbox to enter the filter and see what info your filter is out putting.

"(Year([OrderDate])=Year(Date()) And Month([OrderDate])=Month(Date()) And Day([OrderDate])=Day(Date()))"

That statement looks O.K. for a filter from what I see but your application of the applyfilter has changed is now skewed although should still be O.K.

You have two parts to the applyfilter method the first refers to a query you would like to use for a filter and the second part is a where statement with out the word where. If you use both then that method will or should take and use the latter where portion and not the named filter (query).

DoCmd.ApplyFilter "PayFilter", "(Year([OrderDate])=Year(Date()) And Month([OrderDate])=Month(Date()) And Day([OrderDate])=Day(Date()))"

I know I haven't answered your original question but it sounds like you have several things you need to look at such as the single form mode and see if that makes a difference.

Let me know I will be here.

[imrosie]

Just reviewing the filters I think that they should work, at least for the most part. There are some things you may want to look at and that is the actual form design, such as nav buttons visible etc..


That sounds like you are viewing the form as a continuous form thus forced to use a scroll bar to go from record to record. It sounds like you are looking for your form to be a single form and you navigate the records using buttons.

As far as the filters go when I face an issue such as what you are describing I manually put in a filter make sure it works then use that in my VBA code.

Are you familiar with debugging the app? If not you can use a msgbox to enter the filter and see what info your filter is out putting.

"(Year([OrderDate])=Year(Date()) And Month([OrderDate])=Month(Date()) And Day([OrderDate])=Day(Date()))"

That statement looks O.K. for a filter from what I see but your application of the applyfilter has changed is now skewed although should still be O.K.

You have two parts to the applyfilter method the first refers to a query you would like to use for a filter and the second part is a where statement with out the word where. If you use both then that method will or should take and use the latter where portion and not the named filter (query).

DoCmd.ApplyFilter "PayFilter", "(Year([OrderDate])=Year(Date()) And Month([OrderDate])=Month(Date()) And Day([OrderDate])=Day(Date()))"

I know I haven't answered your original question but it sounds like you have several things you need to look at such as the single form mode and see if that makes a difference.

Let me know I will be here.

Hi Denburt,

I saw this in helps:
Sub ApplyFilter([FilterName], [WhereCondition])
Member of Access.DoCmd

I thought that 'DoCmd.ApplyFilter' meant use PayFilter (which is actually the name of my combo box using row source type 'Value List' and row source - "Today";"This Week";"Last Week";"This Month";"Last Month";"All"...

Does this mean I need to make a query and column for each of the filters?..

[Denburt]

O.K. Rosie from the top, lets start here: from Post #3

Also noticed, that the Navigation buttons aren't connected to the Orders list displayed. I can't navigate through the list. Must use the scroll bar. What have I overlooked?

Post #4

That sounds like you are viewing the form as a continuous form thus forced to use a scroll bar to go from record to record. It sounds like you are looking for your form to be a single form and you navigate the records using buttons.

Does that help resolve your issue with losing your buttons etc?

Also can you filter by using the toolbar and if so does everything look right?

As far as creating a query for each filter I would say no your filter strings should work.

[imrosie]

O.K. Rosie from the top, lets start here: from Post #3


Post #4


Does that help resolve your issue with losing your buttons etc?

Also can you filter by using the toolbar and if so does everything look right?

As far as creating a query for each filter I would say no your filter strings should work.

Hi Denburt,
They still don't seem to be working...Here the direction I've gone with the code:

Private Sub PayFilter_AfterUpdate()
Dim SQL As String
Dim PayFilter As String
 
SQL = "SELECT Customer Name, OrderID, OrderDate FROM OrderList;"
Select Case Forms![2-PrintPreviewInvoices]!PayFilter.Value
    Case "Today"
        PayFilter = " Where (Year([OrderDate])=Year(Date()) And Month([OrderDate])=Month(Date()) And Day([OrderDate])=Day(Date()))"
    Case "This Week"
       PayFilter = " Where DatePart(""ww"",[OrderDate])=DatePart(""ww"",Date()) and Year([OrderDate]) = Year(Date())"
    Case "Last Week"
        PayFilter = " Where Year([OrderDate])* 53 + DatePart(""ww"", [OrderDate]) = Year(Date())* 53 + DatePart(""ww"", Date()) - 1"
    Case "This Month"
        PayFilter = " Where Year([OrderDate]) = Year(Now()) And Month([OrderDate]) = Month(Now())"
    Case "Last Month"
        PayFilter = " Where Year([OrderDate])*12+ DatePart(""m"",[OrderDate])=Year(Date())*12 + DatePart(""m"", Date())-1"
     Case "All"
      PayFilter = ""
    End Select
Debug.Print SQL & PayFilter
Me.thelistbox.RowSource = "Select CustomerID, OrderID, OrderDate FROM OrderList;" & PayFilter
End Sub
 

I've got an Immediate window so I can see what's wrong....I keep getting a blank screen after I select a value.

It bares repeating, sorry......but there are 2 controls only on the Form. 1 is the listbox control and the other is the 'PayFilter' control. The Listbox, simply displays the list of Orders in the control based on query called OrderList.

PayFilter is the control with the Value list for All, Today, This Week, Last Week, etc. The After Update event above is with the PayFilter control. The rational was that when you selected a Value from the Value list, After Update event would kick in and apply the filter to Orderlist. the problem is, when you open the form, the listbox is already there, for all to see. You can actually scoll up and down through the list. However, in this case, the two controls seem disjointed.

Its as though the one control has already been populated with a list by design, before a value has even been selected; the PayFilter control on the other hand (after update event) supposedly 'filters' what is shown in the listbox window, but that doesn't happen. The entire list of 2159 records, still shows.

help please, thanks

[Denburt]

O.K. I think it should work but look at your last post line 21:

Me!thelistbox.RowSource = "Select CustomerID, OrderID, OrderDate FROM OrderList" & PayFilter

I removed the colon after orderList. The Colon represents the end of a query (after your "where" statement and isn't usually needed anyhow.

Try that and let me know. Oh and although I'm not sure you may need to requery the listbox after reseting the rowsource.

Me.thelistbox.requery

[imrosie]

Well Well Well,,,,,,

It's working,,,,it's actually working. My mistake on the colon.....I added the requery and Wow, it's working. thanks so much Denburt.

take care.
Rosie

[Denburt]

My pleasure glad we could get it going.