Looping through fields in a form and obtaining the values (1 Viewer)

[wackywoo105]

I'm using the following code to populate the columns in an excel spreadsheet with the field name from an access form.

Dim count As Integer: count = 1
For Each fld In db.TableDefs!LabOrders.Fields
oXLSheet.Cells(1, count).Value = fld.Name
count = count + 1
Next

What I also want to do is move onto the next line of the spreadsheet and input the values of those fields for the current open form. Of course the code below doesn't work. Can anyone help with what to use?

count  = 1
Dim LastRow As Integer
LastRow = oXLSheet.UsedRange.Rows.count + 1
For Each fld In db.TableDefs!LabOrders.Fields
oXLSheet.Cells(LastRow, count).Value = fld.Value
count = count + 1
Next

 

[June7]

Your code is actually pulling data from table, not form. Why not use DoCmd.TransferSpreadsheet ?

Or explore CopyFromRecordset method. Open a recordset and use CopyFromRecordset to fill cells. No loop required.

Field names from Recordset can be read and written to cells. This will require looping the Fields collection. Example:

Sub DataToExcel()
Dim rs As ADODB.Recordset, excelApp As Excel.Application, targetworkbook As Excel.Workbook, x As Integer
Set rs = New ADODB.Recordset
rs.Open "Holidays", CurrentProject.Connection, adOpenKeyset
Set excelApp = CreateObject("Excel.application", "")
excelApp.Visible = True
Set targetworkbook = excelApp.Workbooks.Open("C:\path\workbook.xlsx")
With targetworkbook.Worksheets("Sheet2")
    .Activate
    .Range("A2").CopyFromRecordset rs
    .Name = "test"
    rs.MoveFirst
    For x = 0 To rs.Fields.Count - 1
        .Cells(1, x + 1) = rs(x).Name
    Next
End With
End Sub

 

[wackywoo105]

Thanks. I've got that working but it's copying the entire table into excel each time. Is there a way to make it use only the current open form data or a particular ID number?

 

[Pat Hartman]

May I repeat June's observation?

Your code is actually pulling data from table, not form. Why not use DoCmd.TransferSpreadsheet ?

You could use a query that selects the record you are viewing on the form. OR, you can use code similar to what you have but instead of looping through a recordset column by column and row by row, just loop through the fields collection of the form.

 

[Gasman]

Why not use the form recordset or recordsetclone?

 

[Pat Hartman]

The form's field collection is ALWAYS sync'd with what you can see. The form's recordset might be positioned on a different record so the form's field collection is safer and won't ever be out of sync if you only want the one record.

 

[June7]

Thanks. I've got that working but it's copying the entire table into excel each time. Is there a way to make it use only the current open form data or a particular ID number?

Yes. Apply filter criteria in the SQL statement. Or build recordset from the form's dataset.

Dim rs As DAO.Recordset, excelApp As Excel.Application, targetworkbook As Excel.Workbook, x As Integer
Set rs = Forms!formname.Recordset
Set excelApp = CreateObject("Excel.application", "")
excelApp.Visible = True
Set targetworkbook = excelApp.Workbooks.Open("C:\path\workbook.xlsx")
With targetworkbook.Worksheets("Sheet2")
    .Activate
    .Range("A2").CopyFromRecordset rs
    .Name = "test"
    rs.MoveFirst
    For x = 0 To rs.Fields.Count - 1
        .Cells(1, x + 1) = rs(x).Name
    Next
End With

 

[wackywoo105]

Yes. Apply filter criteria in the SQL statement. Or build recordset from the form's dataset.

Dim rs As DAO.Recordset, excelApp As Excel.Application, targetworkbook As Excel.Workbook, x As Integer
Set rs = Forms!formname.Recordset
Set excelApp = CreateObject("Excel.application", "")
excelApp.Visible = True
Set targetworkbook = excelApp.Workbooks.Open("C:\path\workbook.xlsx")
With targetworkbook.Worksheets("Sheet2")
    .Activate
    .Range("A2").CopyFromRecordset rs
    .Name = "test"
    rs.MoveFirst
    For x = 0 To rs.Fields.Count - 1
        .Cells(1, x + 1) = rs(x).Name
    Next
End With

Sorry to be a pain. But if using the code below how would I get it to use only 1 record which is the currently open form record?

Dim LastRow As Long
LastRow = oXLSheet.UsedRange.Rows.count + 1
      
Dim rs As ADODB.Recordset, x As Integer
Set rs = New ADODB.Recordset
rs.Open "LabOrders", CurrentProject.Connection, adOpenKeyset
With oXLBook.Worksheets(1)
    .Activate
    .Range("A" & LastRow).CopyFromRecordset rs
    .Name = "test"
    rs.MoveFirst
    For x = 0 To rs.Fields.count - 1
        .Cells(1, x + 1) = rs(x).Name
    Next
End With

 

[June7]

In first example using ADODB do something like:

rs.Open "SELECT * FROM sometable WHERE ID = " & Me.ID, CurrentProject.Connection, adOpenKeyset

In second example using DAO do something like:

Set rs = CurrentDb.OpenRecordset("SELECT * FROM sometable WHERE ID = " & Me.ID)

 

[Pat Hartman]

If you are only writing ONE record to the spreadsheet, WHY do you need to move to a new row?