A subroutine to return user to specific row in subform

[AdamOnAccess]

Hi Gang,

I'm trying to write a public subroutine that returns a user to a specific row in a subform. Here's what I have so far...

Public Sub psubGoToRecordInSubform(ctrSubForm As Control, intID As Integer, strIDField As Object)
'returns cursor to a specific record in a subform
 
      Dim rs As Object
      Dim lngBookmark As Long
 
      'set a variable to ID
      lngBookmark = intID
 
      DoCmd.GoToControl ctrSubForm.Name
 
      'take it to the selected record
      Set rs = ctrSubForm.Form.RecordsetClone
      rs.FindFirst strIDField & " = " & lngBookmark
      ctrSubForm.Form.Bookmark = rs.Bookmark
 
      Set rs = Nothing
End Sub

The problem occurs on the second to last line:

rs.FindFirst strIDField & " = " & lngBookmark

I can't seem to get the program to pick up strIDField properly. I've tried declaring it as a string, an object. I declared it as a control and used strIDField.Name and it errored. Access message said "Actual field name" is not a recognized field. This makes me wonder it this line is doing it's job:

DoCmd.GoToControl ctrSubForm.Name

Does anyone know what I'm doing wrong? (I mean besides trying to be a programmer :) )

Thanks,
Adam

[Delerna]

Have you tried putting breakpoints on the code and running it.
When the breakpoint occurs hover you mouse over the variables to see what they contain.

Also, maybe you can try

docmd.FindRecord

[ADezii]

@AdamOnAccess
I don't play around with Bookmarks much, but couldn't you:

1. Declare a Global Variable to hold the Value of the Sub-Forms Bookmark:
   

   Public strBookmark As String

2. Set the Bookmark in the Sub-Form at some convenient point:
   

   strBookmark = Forms![Orders]![Orders Subform].Form.Bookmark

3. Later, set Focus to the Sub-Form, then return to the previously set Bookmark:
   

   Forms![Orders]![Orders Subform].SetFocus
   Forms![Orders]![Orders Subform].Form.Bookmark = strBookmark

4. I tested this on the Order Details Sub-Form of the Sample Northwind Database, and it appears to work quite well.

[Stewart Ross]

I'd use ADezii's solution - it is neat and simple.

If you want to continue with findfirst, you mention that you have tried using the Name property of the control concerned. This will only work if the control name is identical to the name of the underlying field in your recordset to which it is bound. If it is (and by default it should be, unless you have renamed the control), be careful about the use of spaces, as these will prevent Access from recognising the name of the field unless you use the rectangular brackets form as shown below, or you replace the spaces with underscores.

Using the name property of the control your expression should be something like this:

rs.FindFirst "[" & strIDField.name & "] = " & intID

You can confirm all this by stepping through your code line-by-line and using the immediate window to test values, as Delerna suggested.

I have used intID directly instead of assigning it to lngBookmark as you did; bookmarks are specific properties of recordsets and are not ordinary integers or the like. Confusing the two is not a good idea.

-Stewart

PS strIDField is currently defined as an Object. If you are passing a control to it then define it as a Control data type - at least then you will have a Name property to access, and Intellisense will be able to show you the properties of the control when you wish to see them in the immediate window.

[NeoPa]

Your use of strIDField is quite wrong and the name misleading. It should be a string variable, and the name passed. When used, for ensuring it will always work, I suggest you enclose the name value passed in [] characters to ensure that any spaces or other questionable characters in the name do not cause the SQL engine to get confused.

Also, As the word Bookmark has a special meaning in Access I would avoid usage of it for a simple ID variable. Perhaps simply passing the ID in as lngID in the first place would tidy up your code and remove this misleading variable entirely.

Public Sub psubGoToRecordInSubform(ctrSubForm As Control, _
                                   lngID As Long, _
                                   strIDField As String)
...
rs.FindFirst "[" & strIDField & "]=" & lngID