Macro

[Sigmund]

All Case List

As you can see from the attached form, I have two records in my database that was built to track the Workers' Compensation injuries of my employees.  Jane Doe is listed twice, her IW ID is the same, but her Case ID is different. She has one Employee Details form which captures data like name, dob, IW ID, etc.  Within the Employee Details form, there are tabs with subforms.  One Tab called "Cases" includes a subform(Case Details) that includes details about a particular injury.   In Jane's case, she has had to injuries(broken leg in 2009 and a sprained ankle in 2010), therefore she has two records in the Case Details subform.  What I would like to be able to do is this . .  If I click "Open" next to Jane Doe with the Case ID of 36(ankle), I want it to open Jane's Employee Detail form with the subform(Case Detail)that displays the details of her injury for Case ID 36(ankle).  However, when I run my current macro, it opens the correct Employee Detail form, but opens the subform(Case Detail) to the first record, which is Case ID 35(leg).  Is there a way to write a macro to open the form with the subform opening to the record I want to view?

[Birr]

No, this can't be done with macros. Macros are too limiting. But it can be done with VBA. I've had a similar problem but the record I wanted was buried in two subforms.  There are a couple of ways to do this. Neither is very easy. One possibility is to filter the subform by the record chosen in the All Case form. When you click on Open on All Case, you run code like this:

DoCmd.OpenForm "frmEmpDetails",,,"[EmployeeID] = " & Me.EmployeeID,,Me.CaseID

What this does is open the Details form to the Employee's record. It also passes the CaseID to to the form as part of the OpenArgs. You then put code in the On Open event of frmEmpDetails like so:

If Not IsNull(Me.OpenArgs) Then

strFilter = "[CaseID] = " & Me.Openargs

Me.subformname.SetFocus

Me.Filter = strFilter

DoCmd.RunCommand acCmdApplySortFilter

End If

This will then filter the subform to the CaseID.

An alternative is to populate a hidden, unbound control on the main form with the OpenArgs value. Use a query as the Recordsource for the subform and set the criteria for for the query to:

Like Forms!mainformname!txtCaseID & "*"

this will return just the matching case number or all cases for that employee. Disclaimer the code I posted above is "aircode" and may not be syntactically correct, but it should give you the direction you need. Also remember to use your own names for objects.

Hope this helps,

Scott

Microsoft Access MVP 2007

Author: Microsoft Office Access 2007 VBA