Friday, June 17, 2011

Getting selected fields in a Recordset from another Recordset using VBA(Visual Basic Access)


' It is a function having a parent recordset which will return another recordset having selected columns
Function InfoSheet(ByVal rst_parent As ADODB.Recordset) As ADODB.Recordset

    'Temporary recordset, which will have selected fields from parent recordset and will be returned from this function
    Dim rst_Temp As ADODB.Recordset
    Set rst_Temp = New ADODB.Recordset

    'Adding required number of Columns in temporary recordset : Datatypes must be same
    'If there is null values then it must be declared  adFldIsNullable
    With rst_Temp.Fields
        .Append "Field 1", adInteger, 4, adFldIsNullable
        .Append "Field 2", adVarChar, 50, adFldIsNullable
        .Append "Field 3", adVarChar, 25, adFldIsNullable
    End With
   
    'Add rows into recordset, having all data of those columns from parent recordset
    With rst_Temp
        .Open
        If arg_rst.RecordCount > 0 Then
            Do While Not (arg_rst.EOF)
                .AddNew
                    ![Field 1] = rst_parent .Fields.Item(0).Value
                    ![Field 2] = rst_parent .Fields.Item(1).Value
                    ![Field 3] = rst_parent .Fields.Item(6).Value
                .Update
                arg_rst.MoveNext
            Loop
        Else
            MsgBox "No Records Found"
        End If

        rst_Temp.Update
        arg_rst.MoveFirst
        'Dont close temporary recordset else data will not be available in this inside calling routine or function
    End With
   
    Set InfoSheet = rst_Temp   
End Function

No comments:

Post a Comment

My Projects

  • J2EE Online entertainment world(fully running)

My Blog List