Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS Access Save as File

Posted on 2016-09-22
5
Medium Priority
?
63 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 18

Expert Comment

by:John Tsioumpris
ID: 41810587
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
ID: 41810644
I've attached my database for reference.
Learn5.accdb
0
 
LVL 23

Expert Comment

by:Ferruccio Accalai
ID: 41810711
You're doing a loop in rst but rst2 stay alway at the first record
0
 
LVL 23

Accepted Solution

by:
Ferruccio Accalai earned 2000 total points
ID: 41810725
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
ID: 41810754
Spot on. Thanks for solving it for me :)
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

730 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