Creating a Loop (Access2k/VBA)

Topic

New Reply

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

Reply | Quote

Replies

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:

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

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.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

Brian,

You rock!! It works.

Thank you!

Reply | Quote