Counter on Output File 1

[errolf]

Iam exporting an Access report to to an RTF file.
I want to rename the file on each output by an automatic increment of 1 EG Order1,Order2,Order3 and so on.
I am using the following code

Private Sub CmdExport_Click()
On Error GoTo Err_CmdExport_Click

Dim stDocName As String

stDocName = "Order"
DoCmd.OutputTo acReport, stDocName, acFormatRTF, "C:\Orders\Orders.rtf"

Exit_CmdExport_Click:
Exit Sub

Err_CmdExport_Click:
MsgBox Err.Description
Resume Exit_CmdExport_Click
Does anyone have a solution?

Errolf

 

[mgolla]

First you'll need a table, lets call it tblAutoIncrement

Add these fields to it:

RecordID as Number
CurIncrement as Number

Now add this record to it:

1,1


Ok we now have an increment table setup to use. Change your export code to this:

Private Sub CmdExport_Click()
On Error GoTo Err_CmdExport_Click

    Dim stDocName As String
    Dim intIncrement As Integer
    Dim stQuery As String

    stDocName = "Order"
    intIncrement = DLookup("[CurIncrement]","tblAutoIncrement","[RecordID]=1")

    DoCmd.OutputTo acReport, stDocName, acFormatRTF, "C:\Orders\Orders" & intIncrement & ".rtf"

    'Increment your auto increment
    DoCmd.SetWarnings False
    stQuery = "UPDATE tblAutoIncrement SET CurIncrement = " & intIncrement + 1 & " WHERE RecordID=1"
    DoCmd.RunSQL stQuery
    DoCmd.SetWarnings True

Exit_CmdExport_Click:
    Exit Sub

Err_CmdExport_Click:
    MsgBox Err.Description
    Resume Exit_CmdExport_Click

End Sub

HTH
Mike

 

[errolf]

Thank you very much for your help it works just fine.

Best Regards

Errol