Looping recordset for a report with specific criteria

I have a report that loops through multiple records to combine one field with multiple results into one record. The code is as follows:

I have the Code on the On Open event. The problem is the query that supplies this report ask for a specific Location Number so only that location pulls. Because the code runs first it does not have a Location number to allow the code to do what I need it to.

I hope this made sence.

re: Looping recordset for a report with specific criteria

Hi. There are a number of possible ways forward for you here. I list just two out of many, of which the first is easiest but the second is better:

1. Try the On Load event instead of On Open - the data should be available to you after loading.

2. It is really not a good idea to rely on a user-parameter query as the source of your data. Instead, take the parameter out of your query and get the user to supply the value you need by typing the value in an unbound text box on your form, or better still by selection from a combo or listbox control, then use the value supplied in the control within your code as part of a WHERE clause or filter for the data you need to summarise.

-Stewart

re: Looping recordset for a report with specific criteria

I can get it to run using the unbound field but I can't get the recordset to appear as a field on the report. I am no expert with code but I can get around when pointed in the right direction. Can you give me an idea how to get the information stored in strEmail (See Code) onto the report. Everything else works as I want it to.

re: Looping recordset for a report with specific criteria

Not really sure what you mean when you say you want the recordset to appear 'as a field on the report' - I doubt you really mean a recordset as in a DAO or ADO Recordset object of some kind. If you do, you would have to create a subreport to hold the fields concerned then set the recordsource of the subreport to the SQL for your recordset.

If you really mean you want to show the contents of variable strEmail, the variable you set by looping through the recordset, all you need to do is assign the value of that variable to the unbound textbox on your report after line 24 in post 1:

me![name of textbox] = strEmail

-Stewart

re: Looping recordset for a report with specific criteria

I just can not get this to work for some reason. I tried to add the unbound fields for my parameters and removed them from my query but did not get the correct information (Wrong client and Location popped up) so I must not have done that correctly. I have to run the report from a query because I have four tables and have to pull a specific date from a series of dates.

I did not understand what you meant earlier about On Load. That is not an option for a report that I could find.

I am going to keep working with this.

re: Looping recordset for a report with specific criteria

Apologies - I'd confused forms and reports earlier for On Load.

I cannot see why you are unable to set the value of the unbound textbox. You can try a different approach here, but in order for it to work your looping routine which sets the value of strEmail must be working. Otherwise you will simply be setting the unbound textbox to a blank value.

You could use a global variable to store the value of strEmail until it is needed. In a report, a global variable is one defined as Public in the header of the report's code module, like this

You could then remove the local Dim statement for that variable from Sub Report_Open (or whatever it now is). The global will then be storing the value as set by your recordset loop in the sub.

The value of the unbound textbox can be set in the On Format event for the section in which your unbound textbox is placed, using the global variable strEmail to pass the value to the textbox. If your unbound textbox is in the detail section you would place the assignment statement shown earlier in the Detail_Format sub:

-Stewart

re: Looping recordset for a report with specific criteria

It keeps getting stuck in debugger on the sLocation = Me![Loc No] line. It ask for the Loc No and Client number in the query and I think it is hitting this before it runs the query to get the results and doesn't know what to do with it. I am getting closer.

re: Looping recordset for a report with specific criteria

I suspect you're on the wrong track here.

will only work if there is a control called [Loc No] on your form - and I don't think that can be true or your debugger would not object.

You could ask users for a value directly:

Alternatively, you could use a combo box on your form to list the locations, then refer to the value of the combo to set slocation

Whatever way you go, this would at least provide you with a value for variable slocation that your recordset loop query can actually use within its WHERE clause (line 14 post #1).

-Stewart

re: Looping recordset for a report with specific criteria

I am working with a report and Loc No is the very first field on it. I will work on it some more today and let you know what I find.