hyperlink from form query to form (1 Viewer)

[sambucaman]

I have a form built from a search query, using Like "*" & [Search by Name] & "*"

with the results being listed in as a form (in datasheet view)

example, a search for Smith, would yield a results list similar to;

AM12456, steve smith, 1 high st, london
TD23646, helen smithson, 33 the street, birmingham
AM15545, bob shoesmith, 181 west road, glasgow

etc (where the first column is my reference and primary key)

Is there a way I can create a hyperlink in each of the results, so that I can click the reference to open my main customer form?

Appreciate any help or ideas.

Thanks

 

[missinglinq]

Not a hyperlink, but you can use the DoubleClick event of one of the Textboxes on the Datasheet View Form to open the Customer Form to the matching Record:

In your Datasheet Form

Private Sub AnyTextboxName_DblClick(Cancel As Integer)

DoCmd.RunCommand acCmdSaveRecord

If Not IsNull(Me.PKFieldName) Then
  DoCmd.OpenForm "CustomerForm", , , , , , Me.PKFieldName
 Else
  MsgBox "A PK Field Must Be Entered First!"
 End If
End Sub

In the Customer Form

Private Sub Form_Load()

Dim rst As Recordset

If Not IsNull(Me.OpenArgs) Then
 Set rst = Me.RecordsetClone
 
 rst.FindFirst "[PKFieldName] = '" & Me.OpenArgs & "'"   ' Use this for a Text PKField
 'rst.FindFirst "[PKFieldName] = " & Me.OpenArgs          ' Use this for a Numeric PKField
  If Not rst.NoMatch Then
      Me.Bookmark = rst.Bookmark
   Else
    DoCmd.GoToRecord , , acNewRec
    Me.PKFieldName = Me.OpenArgs
   End If

rst.Close
Set rst = Nothing

End If

End Sub

You'll need to replace all names (AnyTextboxName, PKFieldName and CustomerForm) with the actual names of your objects.

Notice these two lines:

 rst.FindFirst "[PKFieldName] = '" & Me.OpenArgs & "'"   ' Use this for a Text PKField

'rst.FindFirst "[PKFieldName] = " & Me.OpenArgs          ' Use this for a Numeric PKField

The first line is used if the PKField is defined as a Text Datatype, and is currently how the above code is set up.

The second line of code needs to be used if PKField is defined as a Number Datatype. If yours is defined as a Number, use an apostrophe to rem out the first (Text) line and delete the apostrophe from the second (Numbers) line.

Also note that this is boilerplate code I have archived and is set up create a new record in the second form, if none exists. This code will do nothing if you always have a matching record in the Customers Form, so will never fire. I just didn't take the time to take it out of the code. And who knows, you may need the added function at a later date!

Linq ;0)>

 

[sambucaman]

thanks missingling!

Ive actually figured out how to do it, using

Private Sub CustomerID_Click()
On Error GoTo Err_Command40_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Customers"

stLinkCriteria = "[CustomerID]=" & "'" & Me![CustomerID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria


Exit_Command40_Click:
Exit Sub

Err_Command40_Click:
MsgBox Err.Description
Resume Exit_Command40_Click
End Sub

The only extra thing id like now, is for the search form to close on click (as the main form opens)
I take it i need a

DoCmd.Close

somewhere, any idea where i should put it?

 

[missinglinq]

Private Sub CustomerID_Click()
On Error GoTo Err_Command40_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Customers"

stLinkCriteria = "[CustomerID]=" & "'" & Me![CustomerID] & "'"

[B]DoCmd.Close[/B]

DoCmd.OpenForm stDocName, , , stLinkCriteria


Exit_Command40_Click:
Exit Sub

Err_Command40_Click:
MsgBox Err.Description
Resume Exit_Command40_Click
End Sub