Hyperlink on a subform?

[yes sir]

I have a form with a subform. The subform is a list of all the records in the database. I would like to add a field to the subform table/list that would hyperlink the user to the record's profile form once clicked.



I have a similar hyperlink in my database on another form and it works correctly, but for some reason, when I set the hyperlink up in my subform it doesn't direct to the correct record or it just opens the form to a blank record.

I copied and pasted the fields from the form that worked into the subform and what I explained above is what happens.

[yes sir]

I'm linking the subform to the Profile form through a field called ID. This field is on both Form's record source.


Here is what I added for an On-Click Macro:
Open Form: Prospect Form
Where Condition: [ID]=[Forms]![Rosters]![ID]

Where Roster is the name of the form with the subform. (The subform is named "SUB")

[ajetrumpet]

I'm linking the subform to the Profile form through a field called ID. This field is on both Form's record source.


Here is what I added for an On-Click Macro:
Open Form: Prospect Form
Where Condition: [ID]=[Forms]![Rosters]![ID]

Where Roster is the name of the form with the subform. (The subform is named "SUB")

remember that subforms have two very distinct attributes. the control name and the actual object name. try changing the where condition in the macro to:

[ID]=[Forms]![Rosters]![SUB].form![ID]

the other thing to note is that if you try to reference that subform directly in a macro or in code, you'll always get an error that says 'object is not loaded', or something to that effect.

so try that first. If that doesn't work use the syntax in code. I know it works there.

[yes sir]

Private Sub Txthyperlink_Click()
DoCmd.OpenForm (Prospect_Form,acNormal,,[ID]=[Forms]![Rosters]![SUB].form![ID],,acWindowNormal,
End Sub

Compile Error:
Syntax Error

It highlights the first line in yellow, don't see what is wrong with that line. Upon opening VBA, that line is automatically entered by the system. I must be referencing something incorrectly.

[yes sir]

Uploaded the db.

The Rosters Form has the subform on it. When in Design View, the field called ID with the hyperlink is having the issue.

[ajetrumpet]

It does that I think because you are not using quotes. The argment is a string Jay. And I don't think you can reference subforms in that aregument anyway. I couldbe wrong.

I didn't know you were using this argument. Sorry

[yes sir]

Private Sub Txthyperlink_Click()
DoCmd.OpenForm "Prospect Form", acNormal,,"ID = Forms!Rosters!SUB.form!ID",,acWindowNormal,
End Sub

Apparently this isn't correct either.

[slave138]

Have you tried:
DoCmd.OpenForm "Prospect Form", acNormal,,"[ID]=[Forms]![Rosters]![SUB].form![ID]",,acWindowNormal,

Also, is "SUB" what you named the form used as a subform AND/OR the subform control on [Prospect Form]? The above line is pointing to a contol on [Prospect Form] named "SUB" not a form object named "SUB".

[yes sir]

Have you tried:
DoCmd.OpenForm "Prospect Form", acNormal,,"[ID]=[Forms]![Rosters]![SUB].form![ID]",,acWindowNormal,

Also, is "SUB" what you named the form used as a subform AND/OR the subform control on [Prospect Form]? The above line is pointing to a contol on [Prospect Form] named "SUB" not a form object named "SUB".

SUB: name of subform
Rosters: name of form with the SUB (subform) on it
Prospect Form: form that opens when hyperlinked field is clicked.

Tried your code and still getting the syntax error

[yes sir]

"[ID]=[Forms]![Rosters]![SUB].form![ID]"

Obviously this part is the problem. ID is correct, and Rosters is the correct form that contains the subform. I'm just not pointing to the subform correctly.

[ajetrumpet]

Have you tried:
DoCmd.OpenForm "Prospect Form", acNormal,,"[ID]=[Forms]![Rosters]![SUB].form![ID]",,acWindowNormal,

this is not allowed, as far as I know.

[ajetrumpet]

this is how you do it jay...

(make sure to hold the SHIFT key down when opening because I have the menus turned off otherwise...)

[yes sir]

this is how you do it jay...

(make sure to hold the SHIFT key down when opening because I have the menus turned off otherwise...)

Maybe I'm not opening the db correctly. But what I'm looking for are hyperlinks on a subform that contains many records. Each record has the hyperlink. The subform part is taken care of.

[slave138]

Maybe I'm not opening the db correctly. But what I'm looking for are hyperlinks on a subform that contains many records. Each record has the hyperlink. The subform part is taken care of.

Can you post a copy of the Rosters form please?

[yes sir]

Can you post a copy of the Rosters form please?

I'll post the whole db and tell you what to look for. I just removed my last attachment, but I'll put it back up.