Form/Report Help (1 Viewer)

[phamyh]

Hi,

I apologize ahead if I am posting this in the wrong area. It has been awhile since I've done anything with form/report in MS Access. I am seeking guidance on how to do this effectively and efficiently.

What I'm trying to do is build a database that would generate a report for each manager {MGR_ EMPL_ID/MGR_EMPL_NAME}. In that report each of his direct report {EMPL_ID /EMPL_NAME} would have their own page in that report.

After that, I want to send each respective manager an email with a message and his report attached and CC his VP to the email.

I have a table where all my data resides: DATA
I have another table with all my people and attributes: PEOPLE_LIST - VP_NAME, VP_EMAIL, MGR_EMPL_ID, MGR_EMPL_NAME, MGR_EMAIL, EMPL_ID, EMPL_NAME, EMPL_EMAIL.

Where should I go from here?

Thank you so much for your help!

 

[Uncle Gizmo]

I have a picture painted in my mind of your database. You have a table which lists all of your managers. You have another table that lists all of the employees and this table has a column which identifies that persons manager.

Your first step will be to create a query based on these two tables that show the manager and the managers employees related to each other in the query list.

You now need to refine this query by passing in a parameter which I suspect would be the manager ID. This ID will allow you to filter the records returned by the query to just show a single manager and the manager employees.

Once you have this query you will be able to create a report based on it which shows you the manager and a list of the managers employees in a report.

Once you've got this working you will be able to turn the query the report is based on into an SQL statement. You can then use VBA to pass this SQL into the report record source, enabling you to produce an individual report for each manager.

Now I'm not so sure about the emailing part, either generate the report email it and then generate another report and email it, or would it make sense to generate all the reports and then email them all. I suspect the first method would be best. Someone with more experience on this might be able to advise.

 

[phamyh]

yes... more guidance please... I'm a bit lost there.

 

[Uncle Gizmo]

The first step, creating a query, this is a basic function of using MS Access. If you are finding this difficult then you need to search YouTube and find a video explanation.

 

[phamyh]

The first step, creating a query, this is a basic function of using MS Access. If you are finding this difficult then you need to search YouTube and find a video explanation.

I have all the queries... I just don't know how to create the forms and the report to do what i need to do... I have forms and reports but I don't know how to get it to do what I need to do. Please give me more specific guidance as to get the commands right on the form/report...

 

[Uncle Gizmo]

Create a blank form. Name this form "frmMain". Place on this form a command button and a text box.

Select the first query, just highlight it where it appears in the column on the left of the screen and now press one of the buttons in the ribbon that creates a form. You should now have a form based on the query. Name this form "sfrmOne" Now open the form in design view change it to form that shows in datasheet view. You may need to find YouTube videos or other resources to direct you how to do this.

Make sure all the forms and queries are closed. Now open frnMain in design view, drag the form "sfrmOne" on to the frnMain.

Change the name of the command button to "btnTest". Open the command button property sheet and find the on click event, press on the ellipsis and this will take you through to the code behind the form with the subroutine defined with a name something like btnTest _Click.

Place the following code in the command button subroutine:-

MsgBox " >>> " & " Testing"

save the form, press the button and report back what happens.