Change Selected Form Record by clicking Hyperlinked Sub-form Textbox

ChuckRobert

Board Regular
Joined
Feb 26, 2009
Messages
64
I have a single form called “frm_Projects_UpdateProject” that uses a variety of controls to filter the main form query, generating a list of projects that meet certain criteria. The Primary Key is ProjectRef (an autonumber).

To the right side of this form, I have a small subform that lists all projects that are contained within this filtered group, in an abbreviated way.

The textbox On Click function for this hyperlinked subform uses “ProjectRefLinked” to get the matching reference number I want pulled up on the main form.

Using MsgBox Forms!frm_Projects_UpdateProject!frm_ProjectsFilterQuery_HyperlinksSubform.Form.ProjectRefLinked confirms the correct reference number is being requested.

Using MsgBox Forms!frm_Projects_UpdateProject![ProjectRef] confirms the current main form reference number.

I have tried a variety of methods, but have not been able to get the main form to select the record that matches the reference number clicked on the subform. Any suggestions, would be greatly appreciated!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
SOLVED!
I found a reference to the DoCmd.FindRecord function that reminded me to .SetFocus on the field to be searched first. Link...Ho to goto to a specific record using the Docomd.GoToRecord - Microsoft Access / VBA

This worked for me, and hope it may help others:
Forms!frm_Projects_UpdateProject![ProjectRef].SetFocus
DoCmd.FindRecord Forms!frm_Projects_UpdateProject!frm_ProjectsFilterQuery_HyperlinksSubform.Form.ProjectRefLinked.Value, , False, , True, , False
 
Last edited:
Upvote 0
UPDATE...

It appears I was a bit too hasty, as using the code above only allowed me to have the main form display the requested record if I was working down (not up) the subform textboxes. I'm not sure why...

Perhaps there is a better way, but to solve this I created a variable, re-queried the form, then used the variable to select the correct record. It is now working as intended.

Private Sub ProjectCombined_Click()

Dim thisProjectRef As String: thisProjectRef = Forms!frm_Projects_UpdateProject!frm_ProjectsFilterQuery_HyperlinksSubform.Form.ProjectRefLinked.Value

Forms!frm_Projects_UpdateProject.Requery

If (thisProjectRef <> Forms!frm_Projects_UpdateProject![ProjectRef]) Then

' Set focus on the field to be searched
Forms!frm_Projects_UpdateProject![ProjectRef].SetFocus

DoCmd.FindRecord thisProjectRef, , False, , True, , False

End If

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top