469,571 Members | 1,701 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,571 developers. It's quick & easy.

How to call a new function from converted macro

imrosie
222 100+
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:

Expand|Select|Wrap|Line Numbers
  1. PrintPreviewInvoices_PayFilter___On_Change()
  2. If (Forms!PrintPreviewInvoices!PayFilter = "Today") Then
  3.         DoCmd.ApplyFilter "", "(Year([OrderDate])=Year(Date()) And Month([OrderDate])=Month(Date()) And Day([OrderDate])=Day(Date()))"
  4.     End If
  5.     If (Forms!PrintPreviewInvoices!PayFilter = "This Week") Then
  6.         DoCmd.ApplyFilter "", "(Year([OrderDate])=Year(Date()) And DatePart(""ww"",[OrderDate],0)=DatePart(""ww"",Date(),0))"
  7.     End If
  8.     If (Forms!PrintPreviewInvoices!PayFilter = "Last Week") Then
  9.         DoCmd.ApplyFilter "", "(Year([OrderDate])*53+DatePart(""ww"",[OrderDate],0)= (Year(Date())*53+DatePart(""ww"",Date(),0)-1))"
  10.     End If
  11.     If (Forms!PrintPreviewInvoices!PayFilter = "This Month") Then
  12.         DoCmd.ApplyFilter "", "(Year([OrderDate])=Year(Date()) And Month([OrderDate])=Month(Date()))"
  13.     End If
  14.     If (Forms!PrintPreviewInvoices!PayFilter = "Last Month") Then
  15.         DoCmd.ApplyFilter "", "(Year([OrderDate])=Year(Date()) And Month([OrderDate])=(Month(Date())-1))"
  16.     End If
  17.     If (Forms!PrintPreviewInvoices!PayFilter = "All") Then
  18.         DoCmd.ShowAllRecords
  19.     End If
  20.  
  21.  
  22. PrintPreviewInvoices_PayFilter___On_Change_Exit:
  23.     Exit Function
  24.  
  25. PrintPreviewInvoices_PayFilter___On_Change_Err:
  26.     MsgBox Error$
  27.     Resume PrintPreviewInvoices_PayFilter___On_Change_Exit
  28.  
  29. End Function
  30.  
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
Aug 28 '07 #1
9 1863
Denburt
1,356 Expert 1GB
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?

Expand|Select|Wrap|Line Numbers
  1. If (Forms!PrintPreviewInvoices!PayFilter = "Today") Then
  2. DoCmd.ApplyFilter , "[OrderDate]=Dateserial(Year(Date()),Month(Date()) ,Day(Date()))"
  3. End If
  4.  
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:
Expand|Select|Wrap|Line Numbers
  1. If (Forms!PrintPreviewInvoices!PayFilter = "Today") Then
  2. DoCmd.ApplyFilter , "[OrderDate]=Dateserial(Year(Date()),Month(Date()) ,1-1)"
  3. End If
  4.  
Previous month:
Expand|Select|Wrap|Line Numbers
  1. If (Forms!PrintPreviewInvoices!PayFilter = "Today") Then
  2. DoCmd.ApplyFilter , "[OrderDate]=Dateserial(Year(Date()),Month(Date())-1,Day(Date())"
  3. End If
  4.  
Let us know how it goes. once you get this we can work on your next question.
Aug 28 '07 #2
imrosie
222 100+
Hello Denburt,

Here's what I did and additional info:
Expand|Select|Wrap|Line Numbers
  1. Private Sub PayFilter_Change()
  2. Select Case Forms!PrintPreviewInvoices!PayFilter
  3.     Case "Today"
  4.         DoCmd.ApplyFilter "PayFilter", "(Year([OrderDate])=Year(Date()) And Month([OrderDate])=Month(Date()) And Day([OrderDate])=Day(Date()))"
  5.         Case "This Week"
  6.         DoCmd.ApplyFilter "PayFilter", "DatePart(""ww"",[OrderDate])=DatePart(""ww"",Date()) and Year([OrderDate]) = Year(Date())"
  7.     Case "Last Week"
  8.         DoCmd.ApplyFilter "PayFilter", "Year([OrderDate])* 53 + DatePart(""ww"", [OrderDate]) = Year(Date())* 53 + DatePart(""ww"", Date()) - 1"
  9.     Case "This Month"
  10.         DoCmd.ApplyFilter "PayFilter", "Year([OrderDate]) = Year(Now()) And Month([OrderDate]) = Month(Now())"
  11.     Case "Last Month"
  12.         DoCmd.ApplyFilter "PayFilter", "Year([OrderDate])*12+ DatePart(""m"",[OrderDate])=Year(Date())*12 + DatePart(""m"", Date())-1"
  13.         Case "All"
  14.         DoCmd.ShowAllRecords
  15.     End Select
  16.  
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
Expand|Select|Wrap|Line Numbers
  1. 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.
Aug 29 '07 #3
Denburt
1,356 Expert 1GB
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.
Aug 29 '07 #4
imrosie
222 100+
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?..
Aug 30 '07 #5
Denburt
1,356 Expert 1GB
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.
Aug 30 '07 #6
imrosie
222 100+
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub PayFilter_AfterUpdate()
  2. Dim SQL As String
  3. Dim PayFilter As String
  4.  
  5. SQL = "SELECT Customer Name, OrderID, OrderDate FROM OrderList;"
  6. Select Case Forms![2-PrintPreviewInvoices]!PayFilter.Value
  7.     Case "Today"
  8.         PayFilter = " Where (Year([OrderDate])=Year(Date()) And Month([OrderDate])=Month(Date()) And Day([OrderDate])=Day(Date()))"
  9.     Case "This Week"
  10.        PayFilter = " Where DatePart(""ww"",[OrderDate])=DatePart(""ww"",Date()) and Year([OrderDate]) = Year(Date())"
  11.     Case "Last Week"
  12.         PayFilter = " Where Year([OrderDate])* 53 + DatePart(""ww"", [OrderDate]) = Year(Date())* 53 + DatePart(""ww"", Date()) - 1"
  13.     Case "This Month"
  14.         PayFilter = " Where Year([OrderDate]) = Year(Now()) And Month([OrderDate]) = Month(Now())"
  15.     Case "Last Month"
  16.         PayFilter = " Where Year([OrderDate])*12+ DatePart(""m"",[OrderDate])=Year(Date())*12 + DatePart(""m"", Date())-1"
  17.      Case "All"
  18.       PayFilter = ""
  19.     End Select
  20. Debug.Print SQL & PayFilter
  21. Me.thelistbox.RowSource = "Select CustomerID, OrderID, OrderDate FROM OrderList;" & PayFilter
  22. End Sub
  23.  
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
Aug 30 '07 #7
Denburt
1,356 Expert 1GB
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
Aug 30 '07 #8
imrosie
222 100+
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
Aug 30 '07 #9
Denburt
1,356 Expert 1GB
My pleasure glad we could get it going.
Aug 30 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by A. Saksena | last post: by
6 posts views Thread by Xing Xu | last post: by
17 posts views Thread by Russell Shaw | last post: by
18 posts views Thread by Lars Netzel | last post: by
25 posts views Thread by paytam | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.