I use a recordsetclone of the subform to perform a FindFirst method on.
Actually, I recently built a global function to handle "standard" record
movements in a subform (first, last, previous, next, offset). Your question
prompted me to write the below function, which will move to a given ID.
You will still need to capture the ID of the edited/added record on your
detail form in order to pass to this function, but that's easily enough
accomplished if you open the detail acDialog and set visibility to False,
etc...
Here's the code, email if you need a copy that the wordwrap in the reader
doesn't screw up... I did a quick test on both Numeric and String IDs, seems
to work though admittedly I haven't pushed it through all the testing paces
yet.
'==============================================================================
' NAME: SubformGotoRecord
' AUTHOR: Jack D. Leach
' DATE: 11/6/2009
' DESC: Moves the subform to a specified record
' Function is not yet set up to handle nested subforms - 1 layer only!
' ARGS:
' frm: Form - parent form that holds the subform
' SubControlName: String - name of the subform control
' KeyFieldName: String - name of the field to search against
' bNumeric: Boolean - True if the ID is numeric, false if String ID
' vID: Variant - Numeric or String ID to move to
' RETURNS: True on no errors
' DEPENDANCIES: None
' REVISIONS:
' 1 - Initial Release (11/6/2009)
' USAGE EXAMPLE:
' If Not SubformGotoRecord(Me, "ctlsfrmList", "fldID", True, 15) Then
' MsgBox "ID Not Found!", vbCritical
' End If
'==============================================================================
'ErrStrV3.00
Public Function SubformGotoRecord( _
frm As Form, _
SubControlName As String, _
KeyFieldName As String, _
bNumeric As Boolean, _
vID As Variant _
) As Boolean
On Error GoTo Error_Proc
Dim Ret As Boolean
'=========================
Dim rs As DAO.Recordset 'clone of subform recordset
Dim sFindFirst As String 'search criteria
'=========================
'format fieldname
If Left(KeyFieldName, 1) <> "[" Then KeyFieldName = "[" & KeyFieldName
If Right(KeyFieldName, 1) <> "]" Then KeyFieldName = KeyFieldName & "]"
'build the criteria
If bNumeric Then
sFindFirst = KeyFieldName & " = " & CLng(vID)
Else
sFindFirst = KeyFieldName & " = """ & Trim(CStr(vID)) & """"
End If
'prep the rs
Set rs = frm.Controls(SubControlName).Form.RecordsetClone
'find the record
With rs
.FindFirst sFindFirst
If Not .NoMatch Then
frm.Controls(SubControlName).Form.Bookmark = rs.Bookmark
End If
End With
Ret = True
'=========================
Exit_Proc:
Set rs = Nothing
SubformGotoRecord = Ret
Exit Function
Error_Proc:
MsgBox "Error: " & Trim(Str(Err.Number)) & vbCrLf & _
"Desc: " & Err.Description & vbCrLf & vbCrLf & _
"Module: modFormOps, Procedure: SubformGotoRecord" _
, vbCritical, "Error!"
Resume Exit_Proc
Resume
End Function
hth
--
Jack Leach
www.tristatemachine.com
"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)
slickdock said:
I have a continuous form, with the fields locked. In order to edit, the user
clicks on a cmdButton in the left margin of the record they wish to edit.
This opens a form in "single record view" for editing.
When they close the single form view, I use REFRESH on the continous form to
display changes they may have made to the underlying record. That works
fine...But when they add a NEW record, I'm having trouble with REQUERY.
After they close the single form and I make the CONTINUOUS form automatically
REQUERY, how do I get the record indicator to land on the NEW record in the
continuous form? (Note: due to sort order, the new record will NOT be last
on the list, so I can't use GoToLast. But there IS an autonumber field. Is
there mkaybe a way to make it go to the record with the highest autonumber?)
THank you!
