Okay, here is the code excuted when the button is activated:
Private Sub Staff_Injury_Click()
On Error GoTo Err_Staff_Injury_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Staff Injury"
stLinkCriteria = "[Institution]=" & "'" & Me![List85] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Staff_Injury_Click:
Exit Sub
Err_Staff_Injury_Click:
MsgBox Err.Description
Resume Exit_Staff_Injury_Click
End Sub
I have a table called Institutions that lists the name for each facility. In the form, the value is selected from a list box (List85).
The main form is called WEEKLY REPORT and draws from a table called WEEKLY REPORT. WEEKLY REPORT lists the information each site reports, but for which there are not multiple answers. Since, there can be multiple staff injuries (unfortunately), I have a separate table known as STAFF INJURIES.
I am not sure I have it designed properly in that the identifying fields are INSTITUTION and WEEK. In the main table/form WEEKLY REPORT, each institution has only one record per week. Each subform duplicates the INSTITUTION and WEEK fields. WEEK is date of the SUnday that starts the week in question.
I'm not sure how to set the primary keys and develop the relationships.
I know the table INSTITUTIONS can have a primary key for the field INSTITUTION because each facility is listed only once.
Is it something like:
INSTITUTIONS --- WEEKLY REPORTS ---- STAFF INJURIES
Linking the INSTITUTION field from INSTITUTIONS to the INSTITUTION field in WEEKLY REPORTS and then the INSTITUTION field in WEEKLY REPORTS to the INSTITUTION field in STAFF INJURIES?
When I co back to the record, I'd like the subform to retrieve only the records relevant to that institution and the week selected in the main form.
At some point I think I'm going to need a button, prior to the form opening, that asks me to select the institution and the week.
I did an employee database with subforms and didn't have any problem because everything used one employee ID number, but here there are two fields that make the record unique: WEEK and INSTITUTION.
When I open the subform, I'd like the INSTITUTION field and the WEEK field to default to the values in those fields in the subform. I know there is a way to do that, but I can't remember.
I used to be fairly proficient in this stuff, but haven't done anything in VB for 4 years. I have a lot of relearning to do!!!
I'm headed home now, but will check back in tonight, and will be working on it tomorrow.
Thanks!