Open Form to Specific Record

[raweber]

I've looked around and seen a number of questions on this topic, but none of them quite get me where I want to go.

I have a master form called "frmDataEntry" and the primary key is a string field called "numStudy" and does include both letters and numbers.

I have several forms which point back to this master form, and I want to be able to have them open frmDataEntry to the same record they are currently on. I also want the same routine to close the form we're navigating away from.

This code has gotten me really close:

Private Sub btnEdit_Click()
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stLinkCriteria = "numStudy = '" & Me.numStudy & "'"
    stDocName = "frmDataEntry"
    DoCmd.Close
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    End Sub

It works if the form frmDataEntry is ALREADY open. If that form is NOT open when I press the button, it takes me to the first record in the set.

I'm so close - can anyone push me over the top?

Rob

 

[RuralGuy]

What you have should work *all* of the time. Somethintg else is going on.

 

[raweber]

I do have an On Load event in frmDataEntry to filter out certain records by default, but it is impossible for the record I want to start on to be in this filtered group. Here's the code:

Private Sub Form_Load()
    Me.Filter = "Archive = 0"               'Display only active studies by default
    Me.FilterOn = True
End Sub

Note: Archive is a check box, so when a record is archived, then Archive will = -1.

 

[RuralGuy]

The WhereCondition argument you are using just sets a Filter for the form. Thats why it works when the form is already open and the OnLoad event has run and not if the form is closed and the OnLoad event onerwrites your WhereCondition argument as the form loads.

 

[raweber]

Ahh, I had never noticed that I couldn't scroll to any other records when I used this and it worked. I guess I need another method to open the form to the specified record and still have the other records accessible.

 

[RuralGuy]

Paul has a couple of choices on his web page: http://www.baldyweb.com/

 

[raweber]

Okay, I'm trying to implement Baldy's code as such:

Private Sub btnEdit_Click()
    Dim rs As Object
    Dim strBookmark As String
 
    'set a variable to the current record
    strBookmark = Me.numStudy
 
    DoCmd.OpenForm "frmDataEntry"
    'take it to the selected record
    Set rs = Forms!frmDataEntry.RecordsetClone
    rs.FindFirst "numStudy = " & strBookmark & "'"
    Forms!frmDataEntry.Bookmark = rs.Bookmark
 
    Set rs = Nothing
 
    End Sub

Access does not like the line starting rs.FindFirst...

I've never used this before, so I'm not even sure I've adapted the code correctly. Is there an = sign missing or something?

 

[boblarson]

I've never used this before, so I'm not even sure I've adapted the code correctly. Is there an = sign missing or something?

You are missing a quote:

rs.FindFirst "numStudy = " & strBookmark & "'"

should be

rs.FindFirst "numStudy = '" & strBookmark & "'"

 

[raweber]

BOOM! Thank you!

 

[boblarson]

Oh, and also you should change your code to this:

    Dim rs As Object
    Dim strBookmark As String
 
    'set a variable to the current record
    strBookmark = Me.numStudy
 
    DoCmd.OpenForm "frmDataEntry"
    'take it to the selected record
    Set rs = Forms!frmDataEntry.RecordsetClone

    rs.FindFirst "numStudy = '" & strBookmark & "'"
 
    If rs.No Match Then
[B][COLOR=green]       ' goes to a new record if a match isn't found[/COLOR][/B]
       Forms!frmDataEntry.Recordset.AddNew
    Else
       Forms!frmDataEntry.Bookmark = rs.Bookmark
    End If
 
    Set rs = Nothing