Outputting multiple query results as VB variables

[SpiffWilkie]

I have a form that show results from a query that I need to use to
print multiple pdfs. The query usually has multiple results and I
need to print specific pdf files based on those results.
For example, if the query returns A, B, and C, I need to print A.pdf,
B.pdf, and C.pdf. I can print the files with VB code once I get the
results as variables, but I don't know how to convert the multiple
query results as multiple variables. I hope that makes sense. I am
relatively new to access and especially to VB so any help (or
alternate way to accomplish what I am doing) would be appreciated.

Thanks

 

[Steve Schapel]

Spiff,

I think I know what you mean. You can loop through a recordset based on
your query itself, or from the form. Something like this skeleton code:

Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
With rst
Do Until .EOF
<your code to print:> !YourField & ".pdf"
.MoveNext
Loop
End With

 

[SpiffWilkie]

That was exactly what I needed! Thanks. So simple I'm smacking
myself.
I have one more (I hope) question. How can I make it where I can
print the documents multiple times? At the moment, I have it set up
where I click on a button and it executes the code that was given
above. However, the button click only works once (I'm guessing since
there are no more records to go through). I'm assuming there is a
simple code that will do this?

Next time, I'll think twice before volunteering for something I know
nothing about...

Thanks,
Steve

 

[SpiffWilkie]

One more thing. This is the code that I am currently using within the
loop:
-----

Dim fileToPrint As String
strInsertType = !recordSource & ".pdf"
strExecutable = Chr$(34) & "D:\Program Files\Adobe\Acrobat
7.0\Acrobat\Acrobat.exe" & Chr$(34) & " /p /h "
strExecutable = strExecutable & Chr$(34) & "C:\" & fileToPrint &
Chr$(34)

Shell (strExecutable)
------
It's a hodgepodge of stuff I've found online. Any advice on improving
that? Once again, I am completely in the dark as far as access and
vb. All my experience is with java and this is entirely a "by the
seat of my pants" project for work.

Thanks again,
Steve

 

[Steve Schapel]

Steve,

I am not personally familiar with printing PDFs in this way. As far as
I can see, if that code works then it's fine. I can't quite figure the
relationship between fileToPrint and strInsertType, and am wondering
whether you made a typo there.

Anyway. to your earlier question, do you mean you need to print *each*
PDF file more than one copy? If so, there may be a better way, but I
think this will work...

Dim rst As DAO.Recordset
Dim RepeatCount As Integer
Dim i As Integer
Set rst = Me.RecordsetClone
RepeatCount = <whatever number>
With rst
Do Until .EOF
For i = 1 to RepeatCount
<your code to print>
Next i
.MoveNext
Loop
End With

 

[stevenamorrison]

Yup, typo. I was trying too many things at the same time and pasted
different code together.

Thanks again.

 

[SpiffWilkie]

Right now, if I click on the print button once, the pdf's print out.
However, if I click on the button again there is nothing left to
print. I can close and re-open the form but I would like to be able
to click the button as many times as needed without having to re-open
the form.

Thanks,
Steve

 

[Steve Schapel]

Steve,

Try putting this at the end of the code:
Set rst = Nothing

Why are you clicking the button multiple times? Didn't the For i=...
code idea work for you? Let's see the total code you are using now?