MS Access Save as File

Hi,

I have an Access Database  with a form that will print selected reports by group. Which it does fine. But I want the saved file to have the field InvoiceNum in the file name.

Each file includes the correct "Account" field, but every file has the same "InvoiceNum" field in the filename - not the one in he individual report.

I was wondering whether anyone could see why his might be happening?? :S

Private Sub Report_Open(Cancel As Integer)
If Len(strRptFilter) <> 0 Then
     Me.Filter = strRptFilter
     Me.FilterOn = True
End If
End Sub

Private Sub Report_Close()
strRptFilter = vbNullString

End Sub


Private Sub Command2_Click()

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [Account] FROM [InvoiceDetail Query1] ORDER BY [Account];", dbOpenSnapshot)
Set rst2 = CurrentDb.OpenRecordset("SELECT DISTINCT [InvoiceNum] FROM [InvoiceDetail Query1] ORDER BY [InvoiceNum];", dbOpenSnapshot)

Do While Not rst.EOF
    strRptFilter = "[Account] = " & Chr(34) & rst![Account] & Chr(34)

    DoCmd.OutputTo acOutputReport, "InvTotal", acFormatPDF, "C:\Scripts" & "\" & rst![Account] & " - " & rst2![InvoiceNum] & ".pdf"
    DoEvents
    rst.MoveNext
Loop

rst.Close
Set rst = Nothing

End Sub

Private Sub Command4_Click()
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM Customers"
DoCmd.SetWarnings True
End Sub

Private Sub Command6_Click()
DoCmd.RunSavedImportExport "Import-Customers"
End Sub

Open in new window


Many thanks,
Jack MarleyAsked:
Who is Participating?
 
Ferruccio AccalaiConnect With a Mentor Senior developer, analyst and customer assistance Commented:
Your Command2_Click should be as follows (don't know if you really need the rst2 result, but I've left there)

Private Sub Command2_Click()

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [Account], [InvoiceNum] FROM [InvoiceDetail Query1] ORDER BY [Account];", dbOpenSnapshot)
Set rst2 = CurrentDb.OpenRecordset("SELECT DISTINCT [InvoiceNum] FROM [InvoiceDetail Query1] ORDER BY [InvoiceNum];", dbOpenSnapshot)

Do While Not rst.EOF
    strRptFilter = "[Account] = " & Chr(34) & rst![Account] & Chr(34)

    DoCmd.OutputTo acOutputReport, "InvTotal", acFormatPDF, "C:\Scripts" & "\" & rst![Account] & " - " & rst![InvoiceNum] & ".pdf"
    DoEvents
    rst.MoveNext
   
Loop

rst.Close
Set rst = Nothing

End Sub

Open in new window

1
 
John TsioumprisSoftware & Systems EngineerCommented:
I guess a small sample is needed..but i don't like the rst2 getting all the records without any criteria...
0
 
Jack MarleyAuthor Commented:
I've attached my database for reference.
Learn5.accdb
0
 
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
You're doing a loop in rst but rst2 stay alway at the first record
0
 
Jack MarleyAuthor Commented:
Spot on. Thanks for solving it for me :)
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.