Loop Through Form Records 1

[Kiwiman]

I have a form "frmStmtExport", with the Record Source being a query. This continuous form lists the unique Sales Execs who are due commission payments based on teh current month sales.

I have a command button that transfers the sales execs commission data to excel, using the transferspreadsheet function. To do this I have to select each individual record in the form and click the export button.

I would like to do this automatically by looping through the records on the form, but become unstuck using recordsets.

The Tablename of the docmd.transferspreadsheet is also a query, which limits the records to the current record on the form, but my attempts at recordsets provide no results.

Any help would be appreciated.

Here is my code so far:

Private Sub EXPORTSTMT()
'Stop
On Error GoTo Err_EXPORTSTMT

Dim strSQL As String, strExtension As String, strDocName As String, strPath1 As String
Dim RS As DAO.Recordset
Dim intVal As Integer, i As Integer


'Stop

strSQL = "SELECT tblMonthStmts.SalesExecID,tblSalesExec.SalesExec " & _
         "FROM tblMonthStmts INNER JOIN tblSalesExec ON tblMonthStmts.SalesExecID = tblSalesExec.SalesExecID " & _
         "GROUP BY tblMonthStmts.SalesExecID, tblSalesExec.SalesExec"

'Query used to export the statements to Excel
strDocName = "statement"

strFileName = strFileName & "\"

strPath1 = strFileName

'
Set RS = CurrentDb.OpenRecordset(strSQL)

RS.MoveLast
RS.MoveFirst
intVal = RS.RecordCount



For i = 1 To intVal
'DoCmd.GoToRecord acDataForm, "frmStmtExport", intVal
strExtension = RS!SalesExec
strExtension = strExtension & " statement.xls"
strFileName = strPath1 & strExtension
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strDocName, strFileName, True
RS.MoveNext
Next i


RS.Close

Exit_EXPORTSTMT:
    Exit Sub

Err_EXPORTSTMT:
    MsgBox Err.Description
    Resume Exit_EXPORTSTMT

End Sub

 

[Pampers]

Can you not use transferspreadsheet with the source being the query instead, like:

Private Sub Command0_Click()

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryQuery", "C:\book1.xls"

End Sub

Pampers
Keeping it simple can be complicated

 

[Kiwiman]

I use a query as the source for the transferspreadsheet, with the data selected in the query being limited to the particular record selected on the form.

My problem being that I can't seem to get the syntax correct to move to the next record on the form once the trransfer has been done.

Form Record Source - this returns the uniques sales execs due commissions

SELECT tblSalesExec.SalesExecID, tblSOV.SalesExec
FROM tblSOV INNER JOIN tblSalesExec ON tblSOV.SalesExec = tblSalesExec.SalesExec
WHERE (((tblSOV.SOVDate)=[Forms]![frmStmtAdds]![cboSOVMonth]) AND ((tblSalesExec.Commissinable)=-1) AND ((tblSalesExec.RoleId)=1))
GROUP BY tblSalesExec.SalesExecID, tblSOV.SalesExec;

TransferSpreadsheet record source

SELECT tblStatements.*
FROM tblStatements
WHERE (((tblStatements.SalesExecID)=[Forms]![frmStmtExport]![SalesExecID]) AND ((tblStatements.Option)="Statement"));

 

[PHV]

move to the next record on the form
Forms!frmStmtExport.Recordset.MoveNext

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Pampers]

Or?
DoCmd.GoToRecord , , acNext

Pampers
Keeping it simple can be complicated

 

[Kiwiman]

PHV- you are a star so have a star. Worked a treat. So simple but for the life of me I couldn't get it