Open form based on selected record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is a repost of a question I asked in Tables and Design, but which
attracted no attention there.
I have the following tables in my database:
tblEmployees
EmployeeID (PK)
First
Last
Department
etc.

tblMonthlyEval
EvalID (PK)
EmployeeID (FK)
Subject
Date
etc.

tblEmployees is related to tblMonthlyEval one-to-many

I have the following forms:
frmListByDept
frmMonthlyEval

The MonthlyEval objects are for subject, date, etc. of monthly employee
evaluations. frmListByDept is based on qryEmployees, which is based on
tblEmployees. frmListByDept contains an unbound text box which is used to
filter by Department. The idea is that a supervisor will select the
department and see a list of all employees in that department. Each employee
is shown in a separate record in continuous form view. I have done that.
Now I would like to be able to click a command button next to an employee's
name and open frmMonthlyEval with that employee's name already filled in, and
EmployeeID bound to the appropriate field in tblMonthlyEval.
I say I want to use a command button to open the evaluation form, but that
is not required as long as I have a simple way for the supervisor to open the
form.
 
Bruce
try using the LinkCriteria method with the open event of the form
Example
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "fsubform"
stLinkCriteria = "[id]=" & Forms!frmMain.txtid

DoCmd.OpenForm stDocName, , , stLinkCriteria
 
I had something of the sort in the command button. I tried putting it in the
On Open event instead. Nothing happened. Here is the code at the command
button in frmList:

Dim stLinkCriteria As String

stLinkCriteria = "[EmployeeID]=" & "'" & Me![txtEmployeeID] & "'"

DoCmd.OpenForm "frmMonthlyEval", , , stLinkCriteria

[EmployeeID] is a field in frmMonthlyEval, and txtEmployeeID is a control in
frmList. The problem, I think, is that I have not yet created a record, so
there is no link criteria. There are no records at all when I begin. I want
to open frmMonthlyEval with the name and ID of the person in frmList already
filled in.
 
Back
Top