I am trying to create a hyperlink in a sub-form (datasheet) to open a new form based on the record clicked on. The field that I have users click on is the result of a query.
My database has two tables Events and Owners of events. My events are listed in a datasheet with the event owners listed by name. I want to click on the event owner in the data sheet and open the Owners form to that owner.
I have tried various ways to reference to appropriate OwnerID, however, I can't seem to store it. My Owner form does not open to the specified record. This is the current code.
Private Sub OwnerName_Click()
Dim recID As Integer
Dim FrmParent As Form
'How do I get the ID of this owner?
recID = Me.[EventsAll]!OwnerID
Debug.Print "recID = " & recID
Set FrmParent = Forms![EventsParent]
Debug.Print "FrmParent name = " & FrmParent.Name
DoCmd.Close acForm, FrmParent.Name
DoCmd.OpenForm "Owner", , "ID = " & recID
End Sub
Since you reported that Access objected to Me.[EventsAll]!OwnerID
in the click event procedure, and since that procedure is apparently run from the EventsAll
subform, reference the target value directly as Me!OwnerID
WhereCondition is the 4th argument to OpenForm
, but your example puts it in the 3rd position. You can include another comma in the argument list or use the argument name and eliminate the placeholder commas.
Private Sub OwnerName_Click()
Dim recID As Long
recID = Me!OwnerID
Debug.Print "recID = " & recID
DoCmd.OpenForm "Owner", , , "ID = " & recID
' or use the argument name like this
'DoCmd.OpenForm "Owner", WhereCondition:="ID = " & recID
DoCmd.Close acForm, "EventsParent"
End Sub
I also changed the way the parent form is closed. When you know the name of an open form you want to close, you don't have to first set an object variable; you can just give the form name to DoCmd.Close