go to record based on query selected

[v.bon]

Hello there, I am trying to switch the record selected on my main form to the one which has been selected in a sub form datasheet. Such that, as the user selects a record in the subform it will identify the Primary Key field and use that to change the record displayed in the main form.

Below is the code I currently have.

Private Sub Form_Current()
If Not IsNull(Me.Student_Name) Then
Dim RecordValue As String
MsgBox Me.Student_Name 'Only used to test the value returned'
'DoCmd.GoToRecord acDataForm, "Student", acGoTo, Me.Student_Name
End If

Currently the retrieval of the PK(Student Name) works perfectly, i just need to be able to now change the record displayed on the main form.

Any help will be greatly appreciated.

 

[pbaldy]

You can adapt the bookmark code here to do that:

http://www.baldyweb.com/Bookmark.htm

basically everything but the OpenForm.

 

[v.bon]

I keep getting a runtime error 3077 related to the line:

rs.Findfirst "[Student_Name]=" & RecordValue

Would you know why?

 

[Minty]

I suspect the student name is a text field so you need to escape the string with ' single quotes.

rs.Findfirst "[Student_Name]=[COLOR="Red"]'[/COLOR]" & RecordValue[COLOR="red"] & "'"[/COLOR]

 

[pbaldy]

What is the data type of that field? If it's text:

rs.Findfirst "[Student_Name]=" & Chr(34) & RecordValue & Chr(34)

 

[v.bon]

Thank you for that syntax correction! Such a silly mistake to make.
I keep getting an error message saying record doesn't exist but not too sure why.

Private Sub Form_Current()
If Not IsNull(Me.Student_Name) Then
Dim RecordValue As String
Dim rs As Object

RecordValue = Me.Student_Name

Set rs = Forms!Student.RecordsetClone
rs.Findfirst "[Student Name]= '" & RecordValue & "'"

If rs.NoMatch Then

Else
Forms!Student.Bookmark = rs.Bookmark 'ERROR OCCURS HERE'
End If
MsgBox Me.Student_Name
End If

Set rs = Nothing
End Sub

 

[pbaldy]

Can you attach the db here?