Move to a related record

  • Thread starter Thread starter Access::Student
  • Start date Start date
A

Access::Student

Hi,
I'm new to Access 2007, coming from Filemaker and I'm trying to replicate
some functionality.

I've got two tables, tasks and employees. Tasks has 3 foreign keys fields
that relate to employees.

I'm on a form from Tasks that has those three fields in it. I want to create
a button beside each of the fields to take me to a Peoples form corresponding
to the contents of that field. I'm not quite sure how to do this... What
combination of macros do I need?

Thank you for any help.
 
Access::Student said:
Hi,
I'm new to Access 2007, coming from Filemaker and I'm trying to replicate
some functionality.

I've got two tables, tasks and employees. Tasks has 3 foreign keys fields
that relate to employees.

I'm on a form from Tasks that has those three fields in it. I want to create
a button beside each of the fields to take me to a Peoples form corresponding
to the contents of that field. I'm not quite sure how to do this... What
combination of macros do I need?

Thank you for any help.
Click to expand...


Well I feel dumb now, figured it out...
 
As I recall, Filemaker offers a "flat" data model (?like Excel).

Access is a relational database, and "expects" well-normalized data.

You are there, we are not, so "how" will depend on "what". What is the
relationship between Tasks and Employees?

For example, I work in a shop where more than one employee could be assigned
a task, and one employee could be assigned more than one task. This makes
for a "many-to-many" relationship in your data, requiring a third table in
Access to resolve this.

If your situation is similar, consider a structure something like:

tblEmployee
EmployeeID
FirstName
...

tblTask
TaskID
TaskTitle
TaskDescription
...

trelAssignment
AssignmentID
EmployeeID
TaskID
AssignmentDate
...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
In the future, someone else may face the same thing you did. If you'll post
back the solution you found, it may help those folks ...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
So I just used the wizard to create a button that has the following code:

Private Sub Goto_From_Click()
On Error GoTo Err_Goto_From_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "PEOPLE"
stLinkCriteria = "[kp_people_id]=" & Me![kf_from_id]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Goto_From_Click:
Exit Sub
Err_Goto_From_Click:
MsgBox Err.Description
Resume Exit_Goto_From_Click
End Sub

This works but I still have one problem. It finds the record, but it also
applies a filter. I don't want there to be a filter. I tried
"DoCmd.ShowAllRecords" but it loses focus on the record I want. I could try
and remember the record name in a variable and then use "DoCmd.GoToRecord"
but that doesn't seem right.

Any idea what I should do?
 
Back
Top