VBA/Excel/Access/Word/Forms/Form Recordset

Assigning Recordsets Dynamically

 
Sub runFormNY()
    Dim con As ADODB.Connection
    Dim myRecordset As Recordset
    Dim strFrmNm As String
    
    Set myRecordset = New ADODB.Recordset
    myRecordset.CursorType = adOpenKeyset
    myRecordset.LockType = adLockOptimistic 
    Set con = New ADODB.Connection
    con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
       "Data Source=" & CurrentProject.Path &"\mydb.mdb;"
       
       myRecordset.Open "SELECT * FROM employees WHERE txtState = "NY"", con
       
       strFrmNm = "frmCustomer"
    
    DoCmd.OpenForm strFrmNm
    Set Application.Forms(strFrmNm).Recordset = myRecordset
    
    myRecordset.Close
    con.Close
    Set myRecordset = Nothing
    Set con = Nothing
End Sub