Solved

MS Access Save as File

Posted on 2016-09-22
5
30 Views
Last Modified: 2016-09-22
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,
0
Comment
Question by:Jack Marley
  • 2
  • 2
5 Comments
 
LVL 13

Expert Comment

by:John Tsioumpris
Comment Utility
I guess a small sample is needed..but i don't like the rst2 getting all the records without any criteria...
0
 

Author Comment

by:Jack Marley
Comment Utility
I've attached my database for reference.
Learn5.accdb
0
 
LVL 22

Expert Comment

by:Ferruccio Accalai
Comment Utility
You're doing a loop in rst but rst2 stay alway at the first record
0
 
LVL 22

Accepted Solution

by:
Ferruccio Accalai earned 500 total points
Comment Utility
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
 

Author Comment

by:Jack Marley
Comment Utility
Spot on. Thanks for solving it for me :)
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now