Hyperlink to another record in access

Question

I'm running Access 2003 and in my dbase I've got a parent and subform.  In the subform I'm wondering if it is possible to add a new field (hyperlink datatype) called "associated project" where the user can enter a project ID (from another parent form record) and it becomes a hyperlink.  Once the hyperlink is created and then selected it would pull up the record.  I guess it is like using the Find command except that the hyperlinked field would just search for that record and retrieve it. 

Basically, is it possible to enter a project ID in a hyperlinked field that resides in the subform and when selected it goes to the parent record that has the project ID?

Some records we use are related in some way to other projects and we'd like a way to be above to reference those that are in another field and then possible be able to move to that record when the hyperlinked field is selected. 

If there is a better/easier way to do this please feel free to suggest.

Answer 1

Add your field and put in the ID of the parent record then you can use this code in the "on click" or "on double-click" events

testfld1 is the control I used in the subform - ID is the primary key of the parent form's data source

 

    Dim rs As Object

    Set rs = Me.Parent.Recordset.Clone
   
    rs.FindFirst "[ID] = " & testfld1
    If Not rs.EOF Then Me.Parent.Bookmark = rs.Bookmark

Answer 2

Below is what I entered and I'm assuming you still wanted me to make the backend table field a hyperlink datatype or should it be a text field?

Dim rs As Object
Set rs = Me.EGM_Main_frm.Recordset.Clone

rs.FindFirst "[mgcid]=" & assocproject
If Not rs.EOF Then Me.EGM_Main_frm.Bookmark = re.Bookmark

A compile error occurred saying "method or data member not found" about the parent form name.  Does that have something to do with the underscores in the name?

---

TAK

Answer 3

It should be what ever type the ID is in the parent form.

as for the compile error use me.parent.... (not the actual name of the parent form, but the keyword parent

Answer 4

My parent form field is a number field in the underlying table.  This field is where my project number is generated and I've got the field formated to where the project number looks something like this format:  1-12-10-0123

When I click the subform field to open it I get the error about not being recognized.  I tried entering the project numbers without the dashes but that didn't work either.  I don't want to change the parent field to a text field from a number field becasue it would affect the project number creation (I tested it and it didn't work right).

Just so I'm clear on how this would function if working properly, when I'm in the subform field (assocproject) and I enter a project number that I'd like to reference in the created field (assocproject) and I go back to the subform record (whenever) and click (I have it as double click) on the field it would change parent records and show the record that was referenced in the subform field.  Is this correct?

---

TAK

Answer 5

The code I provided works, but the values I used for my test was the key field of the parent table.  If you use some sort of concatenation of fields then put that same concatenation in the query for your parent form's data source and then add a control to the parent form (it can be invisible) and use that control from your subform.

Answer 6

I added a new field in the underlying query called mgcidsearch and the expression I used to pull the applicable field data to create the project id is as follows:

SELECT [EGM_Main_tbl.numCompanyNumber] & "-" & Format([mgcrec],"mm") & "-" & Format([mgcrec],"yy") & "-" & Format([egmid]

I added this new query field to the parent form and I kept it visible (for now) just to see it's the same as the field already in the parent form (and it is).  I went back into the subform field "assocproject" and inserted the following code into the double click event:

Private Sub assocproject_DblClick(Cancel As Integer)
Dim rs As Object
Set rs = Me.Parent.Recordset.Clone
rs.FindFirst "[mgcidsearch] = " & assocproject
If Not rs.EOF Then Me.Parent.Bookmark = rs.Bookmark

End Sub

When I enter a project id in the assocproject field and double click nothing happens.  My assocproject field in the underlying table is a text field (in case that matters).  Any suggestions?

 

---

TAK

Answer 7

if it's text then you need to modify this line rs.FindFirst "[mgcidsearch] = " & assocproject

to

rs.FindFirst "[mgcidsearch] = '" & assocproject & "'"

to add single quotes around the value of assocproject

Answer 8

you might also try

rs.FindFirst "[mgcidsearch] = '" & me.assocproject & "'"

assocproject should be the name of the control on your subform

Answer 9

Thanks for the help!  I tried both of the last two suggestions and they each worked great!! Again Thank you very much.

---

TAK

Answer 10

I'm glad it helped.  If you mark the answers as answered it is helpful to others who have the same question to find a solution.

Answer 11

Done!

---

TAK

Answer 12

mark my proposed answers as the answer to your question please.  That way others can see which answer solved the problem.

Answer 13

Since it's a combination of answers to get it working for me I just checked the ones that applied.

---

TAK

Answer 14

Thanks.  - I'm glad you got it to work