I need assistance loading a subForm in my Access application

Hi Experts,
I am trying to load data into a subForm in my Access application.  Here is my sub procedure where I load the data into a recordset.  Now how do I load the data into my subForm?  My subForm has the following text fields; txtName, txtBirthDate, and txtCurrent age.  Please help.  Thank in advance.  Below is my code:

Private Sub cmdSearch_Click()
    Dim strSQL As String
    Dim strSQLW As String
   
    Dim adoRS As New ADODB.Recordset
    Dim adoCN As New ADODB.Connection
    Dim adoCmd As New ADODB.Command
    Dim strItems As String
    Dim strConn As String
   
    '******* Get the assocaite info from the db *******
    strConn = modConnTools.fL50_GetConnString("FAEAGLE")
   
    With adoCN
        .ConnectionString = strConn
        .Open
    End With
     
     With adoCmd
        .CommandText = "dbo.u_TestDate_TestProc_ssp"
        .CommandType = adCmdStoredProc
         '.Parameters.Append .CreateParameter("RetVal", adInteger, adParamReturnValue)
         '.Parameters.Append .CreateParameter("AID", adBigInt, adParamInput, , lngAid)
        .ActiveConnection = adoCN
    End With
   
    'Set the cursor location to client
    With adoRS
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockBatchOptimistic
        Set .Source = adoCmd
        .Open
    End With
   
    'Disconnect the recordset
    Set adoRS.ActiveConnection = Nothing
    If adoCN.State = adStateOpen Then adoCN.Close
   
   
    Set Me.subForm.Form.Recordset = adoRS
   
    'Now load the new data
    'If Not (adoRS.EOF And adoRS.BOF) Then
           
    '    Do Until adoRS.EOF
                     
                     
                     
     '   adoRS.MoveNext
     '   Loop
   ' End If
   
   
   
ExitCode:
    If adoRS.State = adStateOpen Then adoRS.Close
    Set adoRS = Nothing
    If adoCN.State = adStateOpen Then adoCN.Close
    Set adoCN = Nothing
    Set adoCmd = Nothing
   
    'gstrArg = ""
    Exit Sub
       
ErrTrap:
    Call MsgBox(Err.Number & " " & Err.Description & " frm02 - Assocate " & " Load ", vbCritical, "Error opening form")
    Resume ExitCode
   
End Sub
mainrotorAsked:
Who is Participating?
 
CGretskiCommented:
I think your problem is that you are destroying your record set  before you then try to use data in it.

Take out the lines:
   
    'Disconnect the recordset
    Set adoRS.ActiveConnection = Nothing
    If adoCN.State = adStateOpen Then adoCN.Close
   
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.