Automated form

[David Trickett]

I have inherited an Access 97 database on a network which I am trying to control via VBA (I have been writing VBA code for Excel for ages but am *very* new to Access!).

Two of the tables in the database are "Cases" and "Workers". In the "Cases" table relevant fields are "Reference", "CaseName", "CaseworkerInitials" and "DateReportDue". In the "Workers" table relevant fields are "CaseworkerInitials" (which match those in "Cases") and "ManagerNumber".

I have written code which identifies the user, and whether he is a manager or caseworker. If a manager ManagerNumber is established.

What I have been asked to do is to provide a feature whereby if a manager opens the database he will be presented with a list of overdue reports for each of his caseworkers. The display will be optional but I imagine that this will be easy via ordinary VBA.

The logic would be:


1 For each CaseworkerInitials in "Workers" carrying this ManagerNumber get a list of References and CaseNames from Cases where DateReportDue is before today.

2 Output the list onto a form which would contain a Combo Box with the caseworkers names and a list box which would display Reference, Casename and DateReportDue for each caseworker selected from the combo box. The list should be sorted earliest date first. There should be an option to print the list for each caseworker. A real luxury would be to be able to e-mail the list to each caseworker, but I don't want to be greedy!

3. Display the form

I know this is complicated (which is why I can't figure out how to do it!), but I've got a boss swarming all over me demanding this to be done yesterday. There should be an icon for tearing ones hair out.

Thanks in advance for any help.

 

[Travis]

1. Create a query that gives you the basic information: (List of all cases that are overdue.

This query joins the Workers table and Cases table on the CaseWorkerInitials, and limits them to only cases that are due before today.

Copy and paste this to the SQL view of a Query in the QBE Grid.

SELECT Workers.ManagerNumber, Cases.Reference, Cases.CaseName, Cases.CaseworkerInitials, Cases.DateReportDue
FROM Workers INNER JOIN Cases ON Workers.CaseworkerInitials = Cases.CaseworkerInitials
WHERE (((Cases.DateReportDue)<Date()))
ORDER BY Cases.DateReportDue;



2. Use a Parent/Child form. Create a form based off of the CaseWorker table and a form based off of the Cases. Then set the Parent/Child relationship to to the CaseWorkerInitials.


Look into SendObjects or OutLook Automation for the emailing.