DEVelopers HUT

    Dim rs              As DAO.Recordset
    Dim pk              As Long
 
    pk = Me.PrimaryKeyFieldName
    Me.Requery
    Set rs = Me.RecordsetClone
    rs.FindFirst "[PrimaryKeyFieldName]=" & pk
    Me.Bookmark = rs.Bookmark
    Set rs = Nothing

'---------------------------------------------------------------------------------------
' Procedure : FrmRequery
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Requery the form to apply the chosen ordering,
'               but ensure we remain on the current record after the requery
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' frm       : The form to requery
' sPkField  : The primary key field of that form
'
' Usage:
' ~~~~~~
' Call FrmRequery(Me, "Id")
' Call FrmRequery(Me, "ContactId")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2012-Oct-19                 Initial Release
'---------------------------------------------------------------------------------------
Sub FrmRequery(frm As Form, sPkField As String)
    On Error GoTo Error_Handler
    Dim rs              As DAO.Recordset
    Dim pk              As Long
 
    pk = frm(sPkField)
    frm.Requery
    Set rs = frm.RecordsetClone
    rs.FindFirst "[" & sPkField & "]=" & pk
    frm.Bookmark = rs.Bookmark
 
Error_Handler_Exit:
    On Error Resume Next
    Set rs = Nothing
    Exit Sub
 
Error_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: FrmRequery" & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

'---------------------------------------------------------------------------------------
' Procedure : ConvertSQL2QRY
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   :
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
'
'
' Usage:
' ~~~~~~
'
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2011-04-30                 Initial Release
'---------------------------------------------------------------------------------------
Sub ConvertSQL2QRY()
    On Error GoTo Error_Handler
    Dim db               As DAO.Database
    Dim qdf              As DAO.QueryDef
'    Dim sSQL             As String
    Dim ctl              As Control
    Dim frm              As AccessObject
    Dim dbo              As AccessObject
    Dim dbp              As Object
    Dim sFrmName         As String
    Dim sFrmRS           As String
    Dim sQryRS As String
 
    Set db = CurrentDb
    Debug.Print "ConvertSQL2QRY Begin"
    Debug.Print "================================================================================"
 
    'Check Forms
    For Each frm In CurrentProject.AllForms
        sFrmName = frm.name
        DoCmd.OpenForm sFrmName, acDesign
        sFrmRS = Forms(sFrmName).RecordSource
        Debug.Print "Processing Form: " & sFrmName
        If Len(sFrmRS) > 0 And Left(sFrmRS, 4) <> "qry_" Then
            Debug.Print "   Converting Form: " & sFrmName
            If Left(sFrmRS, 7) = "SELECT " Then
                sQryRS = sFrmRS
            Else
                sQryRS = "SELECT [" & sFrmRS & "].* FROM [" & sFrmRS & "];"
            End If
            'Create a query based on the active RS and name it based on the form name for
            '   traceability
            CreateQry "qry_" & sFrmName, sQryRS
            'Change the form RS to use the newly created query
            Forms(sFrmName).RecordSource = "qry_" & sFrmName
        End If
        DoCmd.Close acForm, frm.name, acSaveYes
    Next frm
 
    Debug.Print "================================================================================"
    Debug.Print "ConvertSQL2QRY End"
 
Error_Handler_Exit:
    On Error Resume Next
    Set qdf = Nothing
    Set db = Nothing
    Set ctl = Nothing
    Set frm = Nothing
    Set dbp = Nothing
    Set dbo = Nothing
    Exit Sub
 
Error_Handler:
    LogError Err.Number, Err.Description, _
             sModName & "/ConvertSQL2QRY", _
             , True
    Resume Error_Handler_Exit
End Sub

Me!TreeviewControlName.Nodes(1).Selected = True