Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Location
    USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating a Loop (Access2k/VBA)

    I have a report with an underlying query. In order to print the report, we need to type in an individuals name to query his/her individual information. Since there are only about 10 names, I would like to create a command button that when pressed, prints the report for each individual and runs the query.

    I am extremely new to VBA and am unable to write the code. Can someone please help me?

    Thanks <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  2. #2
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a Loop (Access2k/VBA)

    Edited by Charlotte to eliminate horizontal scrolling

    One important thing to remember is computers are really
    stupid and you're not. So, even though you are new to VBA,
    you knew enough to know that VBA holds the answer to your
    problem...

    Here's an easily understood loop you might find useful:

    <pre>Dim strName as string
    Dim strQry as string

    Do While True 'It rhymes, so I like it.

    'Prompt for names. How about using the simple Inputbox
    strName = InputBox(Prompt:="Enter Name: ", Title:= "Report")
    if strName = "" then
    exit do
    else

    'Reset the query using the strName variable
    'then requery the database with

    strQry = select bla bla bla from database, etc.
    me.recordsource = strQry

    'I just learned about the above line from Share Sargent
    'over at the Access forum (thanks again Shane!)
    endif
    Loop
    </pre>


    The loop will run the report until the user presses [Enter] in which case the loop is exited.

    Put this code in the Open event of the Report.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Creating a Loop (Access2k/VBA)

    I would use a frontend form. Have this form contain a combo box which has all your individuals in it. I'm guessing (hoping?) that you have some sort of ID# associated with each individual, rather than relying on just a name. Anyway, the combobox can hide the # and only display the names. A print button on the form will then run the report based on the value in the combo box. It utilizes the WHERE option of the OpenReport method, like this:
    DoCmd.OpenReport "YourReportName", acViewNormal, , _
    "ID=" & cboIndividual
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Star Lounger
    Join Date
    Mar 2001
    Location
    USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a Loop (Access2k/VBA)

    Kevin,

    I guess I was not complete clear on what I am trying to accomplish. What I am trying to do, is not have to enter each individuals name to generate their report. Is this possible?

  5. #5
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a Loop (Access2k/VBA)

    Hi Julie:
    If I understand you correctly, you've built the query and report and just want to isolate each of the 10 individuals. If that's the case all you need to do is set sorting and grouping options in the detail section of the report.

  6. #6
    Star Lounger
    Join Date
    Mar 2001
    Location
    USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a Loop (Access2k/VBA)

    Brian,

    I need the report to generate for each of the partners, when I use the sorting and grouping options, it groups it as one large report. Am I doing something wrong?

  7. #7
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a Loop (Access2k/VBA)

    I don't think you're doing anything wrong, maybe just missing the idea. If you group your report on "partner", add a group header (and maybe footer), place the partner specific information such as name etc. in the group header, report details in the detail section. Set "Keep together" to Whole Group, I think you'll accomplish what you want.

  8. #8
    Star Lounger
    Join Date
    Mar 2001
    Location
    USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a Loop (Access2k/VBA)

    Brian,

    You rock!! It works.

    Thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •