Save to Multiple Reports to PDF and AutoName using DLookup

[Brilstern]

Q: I am getting "invalid use of null" error. Whats wrong?!?

The goal in this command is to first create a individual report "rptECR" for each SSN in tblBand Members. Next, save each report as a PDF and name each PDF the expression [location] and save into selected folder strMonth.

tblBand Members

Field                      Type
    Rank                       Text
    FName                      Text
    LName                      Text
    MI                         Text
    SSN                        Text PK
    MOS                        Text
    EAS                        Date/Time
    Instrument                 Text
    Date Checked In            Date/Time
    Supply Rep                 Text

qryMember

Field                      Type
        Rank                       Text
        FName                      Text
        LName                      Text
        MI                         Text
        SSN                        Text PK
        MOS                        Text
        EAS                        Date/Time
        Instrument                 Text
        Date Checked In            Date/Time
        Supply Rep                 Text
        Location: [rank] & " " & [lname] & ", " & [fname]

Archive Button

Private Sub cmdArchive_Click()
 
Dim cdb As DAO.Database
Dim strFilter As String
Dim strCurrentPath As String
Dim strReportFile As String
Dim strReport As String
Dim strFilter2 As String
Dim strInputFileName As String
Dim strmonth As String
Set cdb = CurrentDb
strFilter = "[SSN] = ' & !SSN & '"
strmonth = Me.MonthSelect
strCurrentPath = Application.CurrentProject.Path
strReportFile = DLookup(Expr:="[location]", Domain:="[qrymember]", Criteria:=strFilter)
strReportFile = Replace(strCurrentPath & "\strmonth & ' ECR'\%N.PDF", "%N", strReportFile)
strReport = "rptECR"
 
   With cdb.TableDefs("[tblBand Members]").OpenRecordset(dbOpenTable)
   Do While Not .BOF And Not .EOF
     Call DoCmd.OpenReport(ReportName:="rptECR", View:=Access.AcView.acViewReport, WhereCondition:=strFilter)
       Debug.Print "Report and path: " & strReportFile
       DoCmd.OutputTo objecttype:=acOutputReport, objectname:=strReport, outputformat:=acFormatPDF, outputfile:=strReportFile
     Call .MoveNext
   Loop
 End With
 
     DoCmd.Close acReport, "rptECR"
 
End Sub

Below are links to previous questions that aided in the building of this VBA:
Save to PDF File Name AutoCreate
Print Report for each Filtered Record
Any ideas?

Sgt B

[TheSmileyCoder]

It would have been very helpfull, if you had posted which line gives the error.

Now I am guessing that you are getting the error on line 23, and I am guessing the cause is that strReportFile is Null. The underlying cause for this is lines 12 and 15.
Line 12 and 15:

strFilter = "[SSN] = ' & !SSN & '" 
strReportFile = DLookup(Expr:="[location]", Domain:="[qrymember]", Criteria:=strFilter) 
 

The problem here is that the dlookup function gets passed a string that makes no sense to it. It is basicly trying to find any record in which the field [SSN] exactly matches the string LITERAL ' & !SSN & '. For more unstanding, try below line 12 to add:

Msgbox strFilter

I am going to guess that the command is executed from a form in which you have a textbox called SSN. The correct syntax in that case would be:

strFilter = "[SSN] = '" & Me.SSN & "'" 

Again, try to add a msgbox strFilter below after you have made the modifications to get a greate understanding of what is going on.


Whenever you encounter an error such as this one, the best approach is to try to find out which of your variables are null, and then try to determine why it is null.

[NeoPa]

Indeed Stevan, as Smiley says, including a line number with the error message is very much easier for others to work with than using highlights such as Bold, or even comments, in the code itself. That said of course, it's another well-asked question.

I'll follow it, but I'm working from my phone ATM so cannot contribute much now (not that I need to I suspect, as Smiley has you covered).

[Brilstern]

OK gentlemen,

I have taken your input into consideration but I am a little lost here. the below code is my newest change to try and accommodate but I am still not sure how to treat one section. Line 15 is where I am getting the Null problem. I do know why but I don't know how to direct it. This code is a mix of an individual PDF creator that saves one report as a PDF and saves the name as the [location] 'a combobox that I use to choose who to select; and a loop that NeoPa helped me work to print an individual report for each SSN in tblBand Members. What I have attempted is to use them together but of course the naming attempt has me in a bind. I don't know how to associate each name with each PDF as they save. Which is where the error is. What is the correction to line 12 to correctly associate the name with?

Dim cdb As DAO.Database
Dim strFilter As String
Dim strCurrentPath As String
Dim strReportFile As String
Dim strReport As String
Dim strFilter2 As String
Dim strInputFileName As String
Dim strmonth As String
Set cdb = CurrentDb
strFilter = "[SSN] = ' & !SSN & '"
strFilter2 = "[location] = ' & !Location & '"
strmonth = Me.MonthSelect
strCurrentPath = Application.CurrentProject.Path
 MsgBox strFilter2
 strReportFile = DLookup(Expr:="[LOCATION]", Domain:="[qrymember]", Criteria:=strFilter2)
 strReportFile = Replace(strCurrentPath & "\strmonth & ' ECR'\%N.PDF", "%N", strReportFile)
strReport = "rptECR"
 
   With cdb.TableDefs("[tblBand Members]").OpenRecordset(dbOpenTable)
   Do While Not .BOF And Not .EOF
   Call DoCmd.OpenReport(ReportName:="rptECR", View:=Access.AcView.acViewReport, WhereCondition:=strFilter)
   Debug.Print "Report and path: " & strReportFile
   DoCmd.OutputTo objecttype:=acOutputReport, objectname:=strReport, outputformat:=acFormatPDF, outputfile:=strReportFile
   Call .MoveNext
   Loop
   End With
 
DoCmd.Close acReport, "rptECR"

Thx Gents,

Sgt B

[NeoPa]

When you use a reference to an item that starts with a dot (.) or a bang (!) it must be within code designated by a line starting with a With .

Your lines #10 and #11 seem to be referring to a recordset which hasn't been introduced at that point (See line #19). They also seem to be failing as they assign a simple literal string instead of building a result string from literal strings mixed with a variable. When moved, they should be more like :

strFilter = "[SSN] = '" & !SSN & "'"
strFilter2 = "[location] = '" & !Location & "'"

[Brilstern]

OK, this is obviously way above my head because I am kinda lost. Would taking out the criteria in the Dlookup LN 15 help. I tried this and I get OutputTo canceled.

Sgt B

[NeoPa]

It's difficult to explain, as there are really so many misunderstandings and confusions evident in your code.

Let's look at one though, and then I would suggest you reconsider asking your question without reference to your code. In fact, you shouldn't even be posting code that hasn't passed the compile test first (See Before Posting (VBA or SQL) Code).

Your Code :

1. strFilter = "[SSN] = ' & !SSN & '"
   where !SSN = "ABC" leaves strFilter set to :
   [SSN] = ' & !SSN & '

2. strReportFile = Replace(strCurrentPath & "\strmonth & ' ECR'\%N.PDF", "%N", strReportFile)
   where strCurrentPath = "C:", strmonth = "Feb" and the original of strReportFile = "ABC" leaves strReportFile set to :
   C:\strmonth & ' ECR'\ECR.PDF

Fixed Code :

1. strFilter = "[SSN] = '" & !SSN & "'"
   where !SSN = "ABC" leaves strFilter set to :
   [SSN] = 'ABC'

2. strReportFile = Replace(strCurrentPath & "\" & strmonth & " ECR\%N.PDF", "%N", strReportFile)
   where strCurrentPath = "C:", strmonth = "Feb" and the original of strReportFile = "ABC" leaves strReportFile set to :
   C:\Feb ECR\ABC.PDF

[Brilstern]

NeoPa,

When I use

strFilter = "[SSN] = '" & !SSN & "'"

I get "Invalid or Unqualified Resource" compile error.

But after all of this I have been doing more reading on DLookup and it doesn't seem to be to friendly when trying to loop it.

How does it know what [location] to put on each PDF? I mean how does it all work. I am really having trouble here.

[NeoPa]

This leads into what I was saying about how wrong your code is. It's full of problems and confusions. This is why I offered the suggestion in the last post to re-present your question a different way, as the code makes a poor basis from which to ask about it.

!SSN makes no sense as :

1. There is no related With statement.
2. The recordset it relates to hasn't even been opened yet.

This isn't the only issue with the code, so I expect you can start to see why I made the suggestion. You can continue to pick at individual lines, but it'll be a long journey that way.

PS. Don't let this dishearten you. You still have a lot going for you in as much as you ask a better question than most of our experienced members. Appreciate also, that we frequently get questions from members asking us to unravel code they've managed to put together from multiple sources. It's a lot harder than it appears. Fine if you are already able to put the code together for yourself anyway, but if you need the examples in order to do it, then cobbling the disparate code together is always going to be a struggle.

[Brilstern]

NeoPa,

I am soon realizing that putting this code together is much more difficult than I imagined. This is one of my first attempts on piecing together such a complex code. I don't want to scrap it because I know the code works without the naming function. I took out the dlookup and it works fine but you have to individually name each PDF. Do you have any ideas on a better way to attack this. I appreciate all the help and constructive criticism. I understand your basis and frustration as I have trouble understanding the simple stuff sometimes.

Sgt B

[NeoPa]

I'm sure I could help, but I have to have a clear framework to start from. I don't remember all your questions in detail (I deal with a number of threads every day), so I need you to specify your requirement clearly in words if you can. I need to know particularly what information is available to start with and what is required as a result.

[Brilstern]

OK, I really appreciate it. I will be in in the morning and and let you know

[NeoPa]

Just a heads-up - Friday is a busy day for me this week and I may not respond immediately. Weekends often allow me to catch up though. It just means you may have to wait till Monday to try it out.

[Brilstern]

OK so the break down!

I am trying to make a report for each [SSN] in [tblBand Members] and then convert them all to PDF. As they convert to pdf i am trying to name them [Rank LName, Fname]. The source table is below.

        Field                      Type
        Rank                       Text
        FName                      Text
        LName                      Text
        MI                         Text
        SSN                        Text PK
        MOS                        Text
        EAS                        Date/Time
        Instrument                 Text
        Date Checked In            Date/Time
        Supply Rep                 Text

Sgt B

[NeoPa]

That seems clear enough Stevan. I'll look into this before Monday and post some code that should do the trick.

[Brilstern]

Got it working with the code below! Yay ME. Ok so I still have one issue though. In LN 23 i have the name of the PDF. Currently it is just the last name of the Marine. I want it to look like LN 11 "RANK & ' ' & LNAME & ', ' & FNAME". Any ideas

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strPath As String
Dim strFileName As String
Dim strReport As String
Dim strCurrentPath As String
Dim strmonth As String
Dim strPDFNAme As String
 
strPDFNAme = "RANK & ' ' & LNAME & ', ' & FNAME"
strmonth = Me.MonthSelect
strCurrentPath = Application.CurrentProject.Path
strSQL = "SELECT DISTINCT SSN, RANK, LNAME, FNAME FROM [TBLBAND MEMBERS]"
strPath = "" & strCurrentPath & "\" & strmonth & " ECR\"
strReport = "rptECR"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
MsgBox strPath
With rs
   If .RecordCount > 0 Then
      Do Until .EOF
         strFileName = .Fields!LName & ".pdf"
         DoCmd.OpenReport strReport, acViewPreview, , "[SSN] = '" & !SSN & "'", acHidden
         Call DoCmd.OutputTo(objecttype:=acOutputReport, objectname:=strReport, outputformat:=acFormatPDF, outputfile:=strPath & strFileName)
         DoEvents
         DoCmd.Close acReport, strReport
         .MoveNext
      Loop
   End If
End With
 
rs.Close
Set rs = Nothing
Set db = Nothing
DoCmd.Close acForm, "frmPDFSave"

Thank you both so much for the help, i am understanding it a little better now.

Sgt B

[NeoPa]

1. Empty string not necessary in line #15 :
   

   strPath = strCurrentPath & "\" & strmonth & " ECR\"

2. Line #19 not required after all working.
3. Lines #21 and #30 not required and misleading. Do Until .EOF handles this properly as an empty recordset returns .EOF and .BOF as True. There are some situations where .RecordCount is not set until after this point (or at all in some circumstances).
4. strPDFName and strFileName are both for the same purpose so one of them should be ditched.
5. Whichever is ditched line #11 is useless and can go.
6. Line #23 changes to :
   

   (whichever variable is used) = !RANK & " " & !LNAME & ", " & !FNAME.PDF

Otherwise all should be fine. Nice attempt by the way. Much closer to the mark and quite recognisable as to what it's trying to do.

[Brilstern]

Ok, when I do this it gives me error "Invalid or Unqualified Resource" compile error on LN 23. It highlights !Rank

Sgt B

[Brilstern]

NeverMind. I forgot to re add a folder in the path that I created this afternoon. simple mistake. Everything is all good!!

[NeoPa]

Stevan Bias:
Everything is all good!!

That's what I like to hear! Good for you :-)