Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-02-28
10
Medium Priority
?
317 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

972 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