D
DIH
I have a database that tracks accidents. There are about 800 employees.
Each employee in the employee table called, "tblEmployees" has an id
number (this is the primary key - called EmpNumber, data type is text).
This table is related to a table called, "tblAccidentInvestData". The
related foreign key field in this table is called, "EMPNUM".
The primary key in tblAccidentInvestData is called, "AccidentInvestID".
A main data entry form called, "frmInputAccidentInvest" has a subform
called, "subfrmAccidentInvest". This subform puts the data into the
tblAccidentInvestData table (hence the one to many relationship between
the employee and the accident investigation data).
Also, this data entry form gets opened by a main menu form that has a
combobox list of all employee's. The user selects an employee from the
combobox and clicks a command button to open to that employee in the
data entry form.
The main data entry form shows the employee's name, id number and
department (all taken from the tblEmployees table).
The form has a combobox that lets the user select another employee and
then the subform will show the record of any accidents that employee had.
I also have another form (that gets opened from either the main menu
form or the main data entry form) that will show (in read only) all the
accidents that have been entered to date. On this form is a command
button which when clicked will hide the form and reopen the
frmInputAccidentInvest form and go directly to that employee and the
correct accident report.
The following code works perfectly in all versions of Access except
2007. In 2007, the frmInputAccidentInvest will open up to an arbitrary
employee/accident report (not the correct one).
Private Sub Command21_Click()
Dim frm As Form
Dim frmSub As Form
'Hide the read only form
Me.Visible = False
'Open the form data entry form.
DoCmd.OpenForm "frmInputAccidentInvest"
Set frm = Forms("frminputaccidentinvest")
'Find the employee on the main data entry form.
With frm.RecordsetClone
.FindFirst "Empnumber = '" & Me.EMPNUM & "'"
'Move to the found record.
frm.Bookmark = .Bookmark
'Find the correct accident investigation record in the subform.
Set frmSub = frm.subfrmAccidentInvest.Form
With frmSub.RecordsetClone
.FindFirst "AccidentInvestID = " & Me.AccidentInvestID
'Move to the found record.
If .NoMatch Then
Exit Sub
Else
frmSub.Bookmark = .Bookmark
Set frmSub = Nothing
Set frm = Nothing
End If
End With
End With
End Sub
Please note that this code was discovered through some internet
searching as well as trial and error (I am just starting to learn a bit
of vba). Also remember that it works fine in all versions of Access
except 2007.
If there is anything else I need to supply for clarification please let
me know. Thanks in advance for any assistance.
Dave
Each employee in the employee table called, "tblEmployees" has an id
number (this is the primary key - called EmpNumber, data type is text).
This table is related to a table called, "tblAccidentInvestData". The
related foreign key field in this table is called, "EMPNUM".
The primary key in tblAccidentInvestData is called, "AccidentInvestID".
A main data entry form called, "frmInputAccidentInvest" has a subform
called, "subfrmAccidentInvest". This subform puts the data into the
tblAccidentInvestData table (hence the one to many relationship between
the employee and the accident investigation data).
Also, this data entry form gets opened by a main menu form that has a
combobox list of all employee's. The user selects an employee from the
combobox and clicks a command button to open to that employee in the
data entry form.
The main data entry form shows the employee's name, id number and
department (all taken from the tblEmployees table).
The form has a combobox that lets the user select another employee and
then the subform will show the record of any accidents that employee had.
I also have another form (that gets opened from either the main menu
form or the main data entry form) that will show (in read only) all the
accidents that have been entered to date. On this form is a command
button which when clicked will hide the form and reopen the
frmInputAccidentInvest form and go directly to that employee and the
correct accident report.
The following code works perfectly in all versions of Access except
2007. In 2007, the frmInputAccidentInvest will open up to an arbitrary
employee/accident report (not the correct one).
Private Sub Command21_Click()
Dim frm As Form
Dim frmSub As Form
'Hide the read only form
Me.Visible = False
'Open the form data entry form.
DoCmd.OpenForm "frmInputAccidentInvest"
Set frm = Forms("frminputaccidentinvest")
'Find the employee on the main data entry form.
With frm.RecordsetClone
.FindFirst "Empnumber = '" & Me.EMPNUM & "'"
'Move to the found record.
frm.Bookmark = .Bookmark
'Find the correct accident investigation record in the subform.
Set frmSub = frm.subfrmAccidentInvest.Form
With frmSub.RecordsetClone
.FindFirst "AccidentInvestID = " & Me.AccidentInvestID
'Move to the found record.
If .NoMatch Then
Exit Sub
Else
frmSub.Bookmark = .Bookmark
Set frmSub = Nothing
Set frm = Nothing
End If
End With
End With
End Sub
Please note that this code was discovered through some internet
searching as well as trial and error (I am just starting to learn a bit
of vba). Also remember that it works fine in all versions of Access
except 2007.
If there is anything else I need to supply for clarification please let
me know. Thanks in advance for any assistance.
Dave