Looping recordset for a report with specific criteria

[clloyd]

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

Private Sub Report_Open(Cancel As Integer)
    Dim Loc_No As String
    Dim stLinkCriteria As String
    Dim rst As ADODB.Recordset
    Dim sLocation As String
    Dim strEmail As String
    
    sLocation = Me![Loc No]
    
    Set rst = New ADODB.Recordset
    rst.Open "SELECT Email " & _
             "FROM tblContacts dc inner join tblContactsLocations dcl on dc.ID = dcl.ID " & _
             "WHERE dcl.[Loc No] = '" & sLocation & "'", _
             CurrentProject.Connection, adOpenForwardOnly
        Do Until rst.EOF
            strEmail = strEmail & rst("Email") & "; "
        rst.MoveNext
        Loop
        
    strEmail = Left(strEmail, Len(strEmail) - 2)
    
    rst.Close
    Set rst = Nothing

End Sub

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.

[Stewart Ross]

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

[clloyd]

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.

[Stewart Ross]

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

[clloyd]

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.

[Stewart Ross]

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

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:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  me![name of textbox] = strEmail 
End Sub

-Stewart

[clloyd]

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.

[Stewart Ross]

I suspect you're on the wrong track here.

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:

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

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

[clloyd]

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.