Outputting multiple query results as VB variables

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
0
SpiffWilkie
3/4/2008 2:22:00 AM
access 16762 articles. 3 followers. Follow

7 Replies
719 Views

Similar Articles

[PageSpeed] 38

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

-- 
Steve Schapel, Microsoft Access MVP

SpiffWilkie@gmail.com wrote:
> 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
0
Steve
3/4/2008 4:27:36 AM
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

On Mar 3, 10:27 pm, Steve Schapel <scha...@mvps.org.ns> wrote:
> 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
>
> --
> Steve Schapel, Microsoft Access MVP
>
> SpiffWil...@gmail.com wrote:
> > 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

0
SpiffWilkie
3/4/2008 6:17:55 AM
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


On Mar 3, 10:27 pm, Steve Schapel <scha...@mvps.org.ns> wrote:
> 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
>
> --
> Steve Schapel, Microsoft Access MVP
>
> SpiffWil...@gmail.com wrote:
> > 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

0
SpiffWilkie
3/4/2008 6:27:28 AM
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

-- 
Steve Schapel, Microsoft Access MVP

SpiffWilkie@gmail.com wrote:
> 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.
> 
0
Steve
3/4/2008 7:15:39 AM
Yup, typo.  I was trying too many things at the same time and pasted
different code together.

Thanks again.

On Mar 4, 1:15 am, Steve Schapel <scha...@mvps.org.ns> wrote:
> 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
>
> --
> Steve Schapel, Microsoft Access MVP
>
> SpiffWil...@gmail.com wrote:
> > 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.

0
stevenamorrison
3/4/2008 7:42:45 AM
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

On Mar 4, 1:15=A0am, Steve Schapel <scha...@mvps.org.ns> wrote:
> Steve,
>
> I am not personally familiar with printing PDFs in this way. =A0As far as
> I can see, if that code works then it's fine. =A0I 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? =A0If so, there may be a better way, but I
> think this will work...
>
> =A0 =A0Dim rst As DAO.Recordset
> =A0 =A0Dim RepeatCount As Integer
> =A0 =A0Dim i As Integer
> =A0 =A0Set rst =3D Me.RecordsetClone
> =A0 =A0RepeatCount =3D <whatever number>
> =A0 =A0With rst
> =A0 =A0 =A0 Do Until .EOF
> =A0 =A0 =A0 =A0 =A0For i =3D 1 to RepeatCount
> =A0 =A0 =A0 =A0 =A0 =A0 <your code to print>
> =A0 =A0 =A0 =A0 =A0Next i
> =A0 =A0 =A0 .MoveNext
> =A0 =A0 =A0 Loop
> =A0 =A0End With
>
> --
> Steve Schapel, Microsoft Access MVP
>
>
>
> SpiffWil...@gmail.com wrote:
> > One more thing. =A0This is the code that I am currently using within the=

> > loop:
> > -----
>
> > =A0 =A0 =A0 Dim fileToPrint As String
> > =A0 =A0 =A0 strInsertType =3D !recordSource & ".pdf"
> > =A0 =A0 =A0 strExecutable =3D Chr$(34) & "D:\Program Files\Adobe\Acrobat=

> > 7.0\Acrobat\Acrobat.exe" & Chr$(34) & " /p /h "
> > =A0 =A0 =A0 strExecutable =3D strExecutable & Chr$(34) & "C:\" & fileToP=
rint &
> > Chr$(34)
>
> > =A0 =A0 =A0 =A0Shell (strExecutable)
> > ------
> > It's a hodgepodge of stuff I've found online. Any advice on improving
> > that? =A0Once again, I am completely in the dark as far as access and
> > vb. =A0All my experience is with java and this is entirely a "by the
> > seat of my pants" project for work.- Hide quoted text -
>
> - Show quoted text -

0
SpiffWilkie
3/5/2008 2:35:22 AM
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?

-- 
Steve Schapel, Microsoft Access MVP

SpiffWilkie@gmail.com wrote:
> 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.
0
Steve
3/5/2008 7:25:21 AM
Reply:

Similar Artilces:

Deserialize an XML file with multiple namespaces...
Hopefully I can ask this question without regaling you kind people in too many gory details. Here's what I'm trying to do: - I have a local XML file with a .GPX extension which I downloaded from www.geocaching.com. - The file contains a bunch of elements called <WPT> - Within the WPT elements are a number of other elements <time>, <sym>, <groundspeak:name>... - The non-qualified elements come from a schema/namespace at http://www.topografix.com/GPX/ - The qualified elements come from a schema/namespace at http://www.groundspeak.com/cache - I ran xsd.exe with th...

problem with multiple SMTP domains in Exchange Server 2003
Hi there I have added a second SMTP domain to the Email Addresses (Policy) as follows: Recipients > Recipient Policies > Default Policy > Email Addresses (Policy) > New ... > SMTP Address > @xyz.com > (checked) This Exchange Organization is responsible for all mail delivery > to this address > Apply. When I do so, I no longer receive external emails on the first SMTP domain (@abc.com). Is this related to relay settings, or perhaps something else? Best regards Loane ...

Multiple Sessions of Outlook
Is it possible to run multiple Outlook sessions at the same time with different profiles? sid. No. -- Patricia Cardoza Outlook MVP www.cardozasolutions.com Author, Special Edition Using Microsoft Outlook 2003 ***Please post all replies to the newsgroups*** "Sid" <sidboswell@hotmail.com> wrote in message news:c6b901c3b9ed$98ef8e90$a601280a@phx.gbl... > Is it possible to run multiple Outlook sessions at the > same time with different profiles? > > sid. ...

Select multiple adjacent cells of multiple cells without selecting
Select multiple adjacent cells of multiple cells without selecting adjacent cells one by one. those cells looks like; |adjacent cells |cells| |some characters | A | |some characters | B | |some characters | A | |some characters | A | |some characters | B | I am trying to select adjacent cells of A cells without selecting adjacent cells one by one. Thank you for your help. Adjacent in this case, means? If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgr...

Change cell background color based on content that results from li
This could be very simple, but I will lay the groundwork first. I have a schedule spreadsheet that I import data to from a web based program. There are existing filters to remove all formatting of the data and remove that which we do not use. What remains is a non formatted sheet that other workbooks link to so we can produce daily sheets. Now the data that is linked on the other sheets may be for example the number 150. It appears throughout the sheet and I would like to color any cell that contains the number 150. The problem is I can't search for 150 because it reall...

entering multiple costs for an item
I am very new to Retail Management, and am trying to enter inventory, and create a purchase order. I can't figure out how to enter various costs for the same item from the same supplier. For example, when we purchase in lots of 50 pcs., the item cost is .50 per item (total cost of 25.00). However, if we purchase in lots of 100 pcs, the item cost is .47 per item (total cost of 47.00). We can also purchase the item in boxes of 10 (5.50 per box for a cost of .55 per item). How can I enter these costs so when I place a purchase order, the cost will be correct according to the quant...

Pull Data From Multiple Tables ????
Hi I will have 4 tables name "TblCostomers","TblVendors","TblAccounts", TblExpenses" Now i have a for name "FrmDrVouchers" that has a table "TblDrVouchers" in source. This form has two TxtBox Control name "TxtAccountNo" and "TxtAccountName" If User enters a Account No., It pulls the Account Name from Any One of these Table. I can do this if I have only one table. But tell me how can i do it while I have 4 tables for One Field of a table Thank you.. -- Message posted via AccessMonster.com http://...

Query emails coming to a specific domain
Hello; We have about 5 domains on our exchange server that we receive email from. We are in the process of not renewing one of our domains but would like to check to see if email from that domain is still coming through. Is there any way to do that? I tried the Message Tracking Center with the *.domain.com but it didn't like that. It wanted a specific user. Thanks Check SMTP logs as well. -- Bharat Suneja MVP - Exchange www.zenprise.com NEW blog location: www.exchangepedia.com/blog ---------------------------------------------- <nospam@newsgroup.com> wrote in message news:...

view multiple public calendars
We have seven public folders set up as calendar for room appointments. How can we view them side by side as shared calendars? The only way I can think of implementing this is to set up 7 user accounts and share their calendars. -- Posted via a free Usenet account from http://www.teranews.com version of outlook? if 2003 or 2007, add them to the public folder favorites and they'll be in the calendar list. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipst...

vb script for opening notepad
Hi, I am a newbie to vb...Can somebodyprovide me a simple vb script for opening notepad in vista using windows shell Thanks VB baby "Melvin" <whereismelvin@gmail.com> wrote in message news:41a15213-b062-41f5-8525-7eb95c68864a@s22g2000prd.googlegroups.com... > Hi, > > I am a newbie to vb...Can somebodyprovide me a simple vb script for > opening notepad in vista using windows shell What version of VB are you planning to use? This group is for VB classic(VB6 or lower) which is very different from VB.Net(sometimes called VB 2005/2008). If this ...

RMS--Multiple Loyalty Clubs
Does anyone make an add on to allow for multiple loyalty clubs per client? I would like to be able to set it up so that I can either print out a certificate every month and send the customer a voucher for their reward points for the particular club, or set it up so that certain levels of points trigger certificates for redemptions for certain SKUs of product vs. a flat cash credit to their account. contact me at secure payment systems for some really cool options regarding our loyalty card program that we are integrating with microsoft's retail management system right now. It's...

Calling a checkbox control with VB
Hi Is there a way I can reference a checkbox item that I can manipulate in VBA. For instance... I have a series of check boxes that I would like to change the properties of with a loop So my checkboxes are called: CheckBox1 CheckBox2 CheckBox3 etc and I want to be able to reference them like so: For i = 1 to 20 "CheckBox" & i.Value = true next i Any ideas? By the format of the name, I assume that they are control toolbox checkboxes. Dim oOLE As Object For Each oOLE In ActiveSheet.OLEObjects If TypeName(oOLE.Object) = "CheckBox" Then ...

Viewing multiple excell spreadsheets on the task bar
Why is it that when I have 3 or more excell spreadsheets open they are not all listed on my task bar the only way I can get to them is by going to the window button and selecting the one I want to see, I want to be able to ALT TAB between them and they are not listing them for me in the task bar only the current one I have open? Are you using XP? Sounds like that feature of XP which groups several windows of one application into one spot on the task bar. There may be a way to shut this off in taskbar properties. Click on the task bar and select properties. "Megan" wrote: ...

Outlook security features in VB
Hi, I am using MAPI in VB to access outlook to sent email out based the email ID in excel file. When every new email was sent, the popup box shows "A program is trying to access e-mail address you have stored in Outlook. Do you want to allow this? ...." Is there any way to turn off this feature? I am using W2k and Office XP. Thanks in advance. Wilson ...

query to make a list of products based on delivery history
I have a table which lists all deliveries made of our product. From this table, I'd like to make a list of all products. My problem is the products will have many duplicates as they can be ordered multiple times and I just want a list showing all the individual products that we offer. -- TIA Try something like: SELECT DISTINCT [our product] FROM [which list all deliveries made]; If you can't figure this out, come back with table and field names. -- Duane Hookom Microsoft Access MVP If I have helped you, please help me by donating to UCP http://www.access.hookom.net/UCP/Def...

Lookup returning incorrect results
Anybody have an idea why I keep getting incorrect lookup results. In sheet 1, I have a list of "Sales Codes" in col A. In sheet 2, I have a list of data that with column A for "Sales Codes" and column B for associated "Sales Reps". I want to insert a column in sheet 1 that contains lookup formulas to pull the correct sales rep for each sales code . Both sheets are sorted in ascending order by Sales code. My lookup formula in sheet 1, Lookup col B: =LOOKUP(A2,Sheet2!$A$2:$B$4,Sheet2!$B$2:$B$4) The returned results from the above lookup formula are incorrect....

Update Query based on Current Recordset
I have a continuous form with filtered records. I want to run an update query on a field in a table based on the current filtered records on the form. What code would I use to accomplish this task? Any help appreciated in getting me started. NEWER USER, Without a wee bit more information, like what are you trying to update, field names, table names, do you want this attached to a button or an event, etc... The best I can do is... UPDATE SomeTable SET SomeTable.SomeField= WhateverValue WHERE (((SomeTable.SomeOtherFied)=WhateverValue)); Of course, the above would be an ...

Web hyperlinks: default browser and resulting browser window size?
Hi, Am using Excel 97 (I know it is old but I like it!) with Windows XP Pro (SP2). Have a series of web hyperlinks in a worksheet, that load the current chart for a specified chart, from Yahoo Finance. For example: http://chart.finance.yahoo.com/c/1y/c/tls.ax will load the chart for the Australian telco "Telstra". All the links work just fine but have two questions: 1) My default internet browser is Firefox but the links always open Internet Explorer. Is there a way to ensure that Firefox is used? 2) The loaded chart appears in a maximised IE window. Which is a bit silly as t...

IIF Query for Numeric Values in Text Field
I’m attempting to flag interest rate spread errors and omissions in a file from a sales database using IIF statements I adapted from another database (I’m a novice at this). The field I’m querying is text format and contains both alpha and numeric values. When I test for a null value the IIF works fine, but when I test for a numeric value I get an #ERROR. Spread Error1: IIf(([Tbl 1 Eligible Closed Deposit Opps Appended]!Spread Is Null),1,0) Spread Error2: IIf(([CCC Fall 2007 Eligible Products]![IB/NIB]="NIB" And [Tbl 1 Eligible Closed Deposit Opps Appended]!Spread<4),1,0...

Using multiple databases
I have three seperate databases - all do a differnt function using a seperate database that holds all the data in tables (i.e. one uses queries to export, one to import, one for reporting only) - and I want to keep all of the databases seperate. Can I create another database that just has a form that works as a dashboard and connect to the other three to do functions. Essentially, I want to be able to select an option to open reports, import data, or export data and do it without opening the other databases seperatly (a function that each database will perform on it's own w...

Print multiple tabs on one page
How to print multiple tabs in excel onto one page in one print job? Tab1 and tab2 have different format and cannot be combined into one tab. Need to print the two worksheets one on top of each other is the same direction. Tried Report Manager but printed on two pieces of paper. Any suggestions? One way to accomplish something like this is to play around with "pictures". I'm on an XL97 machine now so, When you hold down <Shift> and hit <Edit>, you get a different dialog box .... one that contains "picture " options. So, select an entire sheet, or p...

Printing a Purchase Order to Multiple Destinations
Is it possible to print a PO to a text file on the local machine or network in addition to the local default printer? -- Pete P. It is possible to print a text file to any single location you want in addition to the local default printer. Our system currently prompts users requesting this information. Giving them their choice of 3 formats: Screen Printer File These can be selected in any combination but only one destination per format can be specified. I am hoping that this thread will explain how/if one could deliver one printed report to multiple printers. Eventually I would lik...

Can VISIO 2007 work on multiple pages of an org chart simultaneous
I created an org chart with over 100 pages using the Org Chart Wizard and Excel, I’m assigning colors to various shapes using the Data Graphic feature. To apply the colors I choose “Edit” then “select all” which selects all the shapes on the current sheet, then I select “Apply To Selected Shapes” from the Data Graphics pane, this works fine for the current sheet but is there a way to apply this to all the sheets at once. Thanks Mark, I've written a macro for you to try in my blog... See Applying Selected DataGraphic to the Whole Document - http://bvisual.spaces.live.com/...

Insert query with two left outer joins gives "Record is Deleted" m
Hi, I have built an insert query to combine data from 3 tables into one table based on a key value (RA_ID) on a form. Two of the from tables may or may not have data associated with the main from table. So I have coded the select from statement using left outer joins. When I run the query with a row in the first child table but not in the second it works fine but when I run the query with a key value that exists in the 2nd child table but not in the first I get a message saying "record is deleted". Anybody have any ideas what is causing this? Here is the query......... ...

Filter by multiple colors
In Excel 2007, I am trying to filter by two different colors. I can see how I can filter by just one color - can I filter by two colors? Thanks for your help, -- Michelle ...