By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,176 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

Looping recordset for a report with specific criteria

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

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Report_Open(Cancel As Integer)
  3.     Dim Loc_No As String
  4.     Dim stLinkCriteria As String
  5.     Dim rst As ADODB.Recordset
  6.     Dim sLocation As String
  7.     Dim strEmail As String
  8.  
  9.     sLocation = Me![Loc No]
  10.  
  11.     Set rst = New ADODB.Recordset
  12.     rst.Open "SELECT Email " & _
  13.              "FROM tblContacts dc inner join tblContactsLocations dcl on dc.ID = dcl.ID " & _
  14.              "WHERE dcl.[Loc No] = '" & sLocation & "'", _
  15.              CurrentProject.Connection, adOpenForwardOnly
  16.         Do Until rst.EOF
  17.             strEmail = strEmail & rst("Email") & "; "
  18.         rst.MoveNext
  19.         Loop
  20.  
  21.     strEmail = Left(strEmail, Len(strEmail) - 2)
  22.  
  23.     rst.Close
  24.     Set rst = Nothing
  25.  
  26. End Sub
  27.  
  28.  
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.
Nov 10 '08 #1
Share this Question
Share on Google+
8 Replies


Expert Mod 2.5K+
P: 2,545
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
Nov 16 '08 #2

P: 93
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.
Nov 18 '08 #3

Expert Mod 2.5K+
P: 2,545
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
Nov 18 '08 #4

P: 93
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.
Nov 19 '08 #5

Expert Mod 2.5K+
P: 2,545
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

Expand|Select|Wrap|Line Numbers
  1. Public strEmail as String
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  2.   me![name of textbox] = strEmail 
  3. End Sub
-Stewart
Nov 19 '08 #6

P: 93
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.
Nov 20 '08 #7

Expert Mod 2.5K+
P: 2,545
I suspect you're on the wrong track here.

Expand|Select|Wrap|Line Numbers
  1. slocation = Me![Loc No]
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:

Expand|Select|Wrap|Line Numbers
  1. slocation = InputBox ("Enter a location to match")
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

Expand|Select|Wrap|Line Numbers
  1. slocation = Me![name of your combo]
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
Nov 20 '08 #8

P: 93
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.
Nov 21 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.