Save to Multiple Reports to PDF and AutoName using DLookup

Stevan Bias

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
qryMember
Archive Button
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

Feb 1 '12 #1

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:
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:
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:
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.

Feb 1 '12 #2

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).

Feb 1 '12 #3

Stevan Bias

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?


Thx Gents,

Sgt B

Feb 1 '12 #4

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 :

Feb 1 '12 #5

Stevan Bias

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

Feb 1 '12 #6

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. Expand|Select|Wrap|Line Numbers

   1. strFilter = "[SSN] = ' & !SSN & '"
   2. where !SSN = "ABC" leaves strFilter set to :
   3. [SSN] = ' & !SSN & '
2. Expand|Select|Wrap|Line Numbers

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

Fixed Code :

1. Expand|Select|Wrap|Line Numbers

   1. strFilter = "[SSN] = '" & !SSN & "'"
   2. where !SSN = "ABC" leaves strFilter set to :
   3. [SSN] = 'ABC'
2. Expand|Select|Wrap|Line Numbers

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

Feb 1 '12 #7

Stevan Bias

NeoPa,

When I use
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.

Feb 1 '12 #8

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.

Feb 1 '12 #9

Stevan Bias

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

Feb 2 '12 #10

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.

Feb 2 '12 #11

Stevan Bias

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

Feb 2 '12 #12

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.

Feb 2 '12 #13

Stevan Bias

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.

Sgt B

Feb 3 '12 #14

NeoPa

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

Feb 3 '12 #15

Stevan Bias

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

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

Sgt B

Feb 3 '12 #16

NeoPa

1. Empty string not necessary in line #15 :
   
   Expand|Select|Wrap|Line Numbers

   1. 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 :
   
   Expand|Select|Wrap|Line Numbers

   1. (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.

Feb 3 '12 #17

Stevan Bias

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

Sgt B

Feb 3 '12 #18

Stevan Bias

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

Feb 3 '12 #19

NeoPa

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

Feb 3 '12 #20