Solved

how to create a Tabbed Form Filtered  By Month and year in MS Access

Posted on 2011-02-28
10
299 Views
Last Modified: 2012-06-27
I want to create a Tabbed  form in a MS Access database called “Payroll per month”. When this form opens it will have data for only the user opening it by prompting the user to type in their number. The form should contain 12 tabs named from September 2010 – August 2011. The data fields  in the tabs are “Account #” and “Expense_Amt”. The tabs should filter the data by Date (Month) from the following query.
SELECT [Payroll Per Month].NAME, [Payroll Per Month].PERSON_NBR, [Payroll Per Month].EXP_ACCOUNT, [Payroll Per Month].EXPENSE_AMOUNT, [Payroll Per Month].PERIOD
FROM [Payroll Per Month]
WHERE ((([Payroll Per Month].PERSON_NBR)=[Enter Person Number]));

Each tab will have a total of the Expense Amt for all the accounts for that month. Attached is a  copy of the Table – Payroll Per Month converted to Excel.
Can you help me in achieving this goal as far as creating the tabs on the form to filter the data by month date and totaling them. Thanks

Payroll-Per-Month-Table.xlsx
0
Comment
Question by:Chrisjack001
  • 5
  • 5
10 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 34999127
I have created a sample which indicates how you might do this.
The sample has 2 pages.

On the form you have to supply the person_nbr value in the box at the top.
Then clcik the button.

The code behind the button could be in a form load event if you were able to supply the person_nbr in some other way (through a public variable, for example).

I changed some of the values in the table  so that different answers are produced for the two months.

The code relies on the textboxes to hold the results being called txtP0 for the first tab, txtP1 for the second tab and so on.
Database11.mdb
0
 

Author Comment

by:Chrisjack001
ID: 34999380
Your Idea looks great as far as the totals go but it should also display all the Account #s and the Expense amout before the total. I may not have made myself clear enough. Thanks for your help. Looking forward to your reply
0
 

Author Comment

by:Chrisjack001
ID: 35001782
I keep getting this error that is attached. Am I suppose to add another field to my table called ID because I noticed you had that field added to the table you sent me. Would it be possible to use the parameter I already have in my query prompting for the Person number to execute your query and also display the Account numbers and Amount in dataview format on the tabs. I am new to all this database and SQL stuff so I really appreciate all your help. Also attached is a screen print of what I was trying to accomplish on the form with your suggestion and what I already had.
Error1.docx
0
 
LVL 77

Expert Comment

by:peter57r
ID: 35005692
If you are saying that you want to see a list of all the records for each month on each tab, then in my opinion that is not a good idea.

To display the list on one tab for one month you would have to use a subform on that tab which is fine but for twelve tabs you would need to use 12 subforms and that could bring your form to a standstill.

You should consider having a main form that has a drop down list containing the months a nd use a single subform to display the records - in fact I'm not sure you would even need a subform at all if you set your main form up as a continuous form as I have done in the attached example.
You would use the month in the combo as a filter.  This would have an additional advantage in that you could build the list of months from the data and would not have to define them in advance.  See qryDates in the example.
Database11.mdb
0
 

Author Comment

by:Chrisjack001
ID: 35006335
I really like your suggestion. I noticed that you have another field added to the table called ID and the dates are in DD MM YYYY format unlike the MM DD YYYY format I had them in. Does that mean I have to change my table to resemble yours? Will it be possible for the users name to display on the form after they enter their person number? Thanks for your help. I already like what I see. I just need to convince my boss this is the better solution than the Tabs she wanted me to create. Looking forward to your reply.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 35007116
The ID field got added automatically when I imported the spreadsheet data.  It isn't required, although it does serve the purpose of identifying each record uniquely.
The spreadsheet also had a lot of used but empty rows which were imported into the table.  So the rowsource query for the combo includes a test to exclude them.  If your data is already cleanly in Access then you wouldn't need that test.
You can display the dates in whatever format you wish.  Again it looks like that is something Access added to the field definition doing the import.

I would emphasise that one big advantage of this approach is that the months just appear in the dropdown list.  You don't have any maintenance of tab captions so it will work for ever.

As for usernames then in my example I have assumed that you have identified the user in some way already, in accordance with your original question.  I haven't considered how you get the id number into the form.
You should be aware though that Access2007/2010 offers very little in terms of data security/privacy.  You will have to do a lot of work if you really need to keep users from seeing each others data.  
If you don't really mind if users see each others data then you can convert the textbox on the form to a combo and include the personid and their name in the dropdown list and let them select.

0
 

Author Closing Comment

by:Chrisjack001
ID: 35007745
Peter was very helpful, patient and professional. He has been a very exceptional expert. Thank you very much
0
 
LVL 77

Expert Comment

by:peter57r
ID: 35008290
Thanks for your kind words.

You got me on a good day. I don't normally get involved in anything that requires me to do more than a couple of lines for an answer.

Best of luck with your app.
0
 

Author Comment

by:Chrisjack001
ID: 35012035
Peter in regards to your method is there anyway when you open the form in Form view to let the data change with the same Person ID when you change the Date. Apparently the way it is now when you open the form with your selection of ID and date it seems you cannot view another month data by just selecting a another month. You have to get out completely and start all over. Can you please advise if its possible to change the data for the same user by just changing the month/year. How can I do that. Thanks once again for your help.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 35015013
In the example I posted you can select anything and change the month selection as often as you like.
Agian in my example, if you change the person , nothing happens until a month is selected.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now