Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-02-28
10
Medium Priority
?
313 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
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
 
LVL 77

Accepted Solution

by:
peter57r earned 2000 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

670 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