Loop through query for information

[Dinger_80]

I am using Access 2010. I have a database that on a form uses a multiselect listbox. That part works just fine. The list box is for selecting additional people to email. Now I have had no luck with returning just the email address that are in a hidden column (the persons actual name is seen and "selected"). The names come from a separate table and is used as a forgien key. On that same table are the indivuals email addresses. What I did was loop through to get all of the ID numbers I am getting from the list box (the ID numbers are stored in the table that the form is based on). Once I have all of the ID Numbers I thought that maybe there was a way to retrieve all of the email address associated with the ID Numbers. This is what I have so far. I know that AllQuery returns the first email address from the list box. I just have no idea if the query is returning more than one record, or if it is how to then go to the next record. I have tried a few things with little to no success. Any advice on how to move forward or if there is a better method all together is all appreciated.

Dim ListItem As Variant
Dim AllItems As String
Dim AllQuery As String
For Each ListItem In Me.EmailAdditionEgineers.ItemsSelected
    AllItems = AllItems & Me.EmailAdditionEgineers.ItemData(ListItem) & " or "
Next ListItem
AllItems = Left(AllItems, Len(AllItems) - 3)
AllQuery = DLookup("EmailAddress", "AdditionalEmailRequestQuery", "[ID] = " & AllItems) & ";"

 

[GinaWhipp]

Perhaps this will help...

http://www.baldyweb.com/multiselect.htm

You should be able to modify to use with a DoCmd.SendObject statement, assuming that is what you are doing.

 

[Dinger_80]

Maybe I wasn't being that clear as to the issue i am having. What I am trying to do is allow people to email additional people beyond what the email is already set up to do. I am only returning the value of the ID Numbers. I am attempting to query those ID Numbers, in a query that would also return the corresponding email addresses. The issue is not getting the email address I need. I tried to use this to get the email address of multiple people and it only returned the email address of the first person in the multiselect list box, more than a dozen times. I want to be able to return the email address of the people selected, as I can handle things from there, I just don't know how to get that information.

For Each ListItem In Forms!TestRequestForm!EmailAdditionEgineers.ItemsSelected
    AllItems = AllItems & Forms!TestRequestForm!EmailAdditionEgineers.ItemData(ListItem) & " or "
Next ListItem
AllItems = Left(AllItems, Len(AllItems) - 3)
For Each AllEmail In CurrentDb.QueryDefs()
    AllQuery = AllQuery & DLookup("EmailAddress", "AdditionalEmailRequestQuery", "[ID] = " & AllItems) & ";"
Next AllEmail

 

[GinaWhipp]

The DLookup statement is only going to grab one eMail not a list. You need some kind of loop. Shee this for an example of what I'm talking about...

http://www.access-diva.com/vba16.html

 

[Dinger_80]

Thank you for your assistance in this and for putting it in code that makes so much sense. I sometimes get lost and don't understand what I need to modify. The problem I am having which may have been an issue all along I am not sure, is that I can't get the query to return what I am looking for. I switch my query parameters to a function and call that as the criteria. It just seems that regaurdless of how I try and put the criteria into the query it doesn't seem to return the information I am want. Below is what I have for the Function that I call out. I have used "," where the "or" is and that doesn't help either. I think after this it should be working. Thank you again for making this easy enough for me to understand what is going on.

For Each ListItem In Forms!TestRequestForm!EmailAdditionEgineers.ItemsSelected
    AllItems = AllItems & Forms!TestRequestForm!EmailAdditionEgineers.ItemData(ListItem) & " Or "
Next ListItem
AllItems = Left(AllItems, Len(AllItems) - 3)

 

[GinaWhipp]

What query is the query returning and what should it return? And what is the SQL of the query?

 

[Dinger_80]

The way I set up the query was to get all of the ID Numbers from the multiselect list box. With that information I would have just the ID Numbers and Email address of the individuals that are also being emailed. Which if it did that I believe would work in the previous example you showed earlier.

SELECT CrouseHindsPersonalTable.ID, CrouseHindsPersonalTable.EmailAddress
FROM CrouseHindsPersonalTable
WHERE (((CrouseHindsPersonalTable.ID)=AdditionalEmail()));

The AdditionalEmail is as follows. That seems to return the ID Numbers, so I don't know why the query doesn't return the corresponding records. As I said I have modified the code below a few times, to no avail, to see if the records would show up in the query. So I don't understand why the query doesn't return the results when if I type in the results it works fine and the code below seems to return the same value that the query doesn't perofom as expected.

For Each ListItem In Forms!TestRequestForm!EmailAdditionEgineers.ItemsSelected
    AllItems = AllItems & Forms!TestRequestForm!EmailAdditionEgineers.ItemData(ListItem) & " Or "
Next ListItem
AllItems = Left(AllItems, Len(AllItems) - 3)

 

[GinaWhipp]

Queries, by themselves, don't Loop, so I would imagine it's going for the first match and stops right there. You'll notice in the example I posted I grab the eMails by Looping thru the query via VBA, again, that is because the query by itself won't Loop.

I am assuming that query is the RecordSource for a Form that you open? Or, better question, what does that query feed?

 

[Dinger_80]

I know that the query doesn't itself loop, I just figured when the function AdditionalEmail was called just the final result was returned to the query. So lets say the function runs and gets the result of 3, 15 , 17. That only those results would be returned. Then using what you provided, using the query as the recordset that it would loop through all the records in the query. All of the records are on one table. So there is no relationship or joins required. The query should just be looking for the ID Number based on the multiselect list box. Even if I set the Criteria of the Query to the Multiselect Box it wont return any values when multiple selections have been made. SiI have even tried to use just SQL to replace the AdditionalEmailRequestQuery in the code below.

Set rs = CurrentDb.OpenRecordset("AdditionalEmailRequestQuery")

To better explain what is going on, on a larger picture. The form is used by engineers to gather information on a test they want done. At the end of the form the option to email additional engineers is available (I thought I had it working but I was wrong).This is something that will vary from user to user and time to time. When the form is completed they click a button called submit test request. A lot of things happen but what relates to this is an email is sent to the lab supervisor notifying him of the test the engineer has submitted. I am trying to make it so that other engineers recieve the same email, as it provides information, to include a hyperlink to a destination on the server where all results will be found after the test. By having the ability to click on other engineers names it saves them from forwarding the email to the other engineers after they submit a test.

 

[GinaWhipp]

Okay, well, you can't call a Query from the Form's Module that references a Module in the Form. Everything including the query needs to be behind the form. The sample I provided does give you that ability though you might have to make a few modifications.

I know you *think* it should work but it won't and I really don't want to see you go bald. Review the link I posted, play with that.

 

[Dinger_80]

Well I kind of took what you said and made it work. Basically I had the DLook up be looped to constantly get the new email address when the ID was sent individually to the query. Here is the code I settled on and got the results I needed.

Dim ListItem As Variant
Dim AllEmail As String
Dim iCount As Integer
Dim oItem As Variant
iCount = 0
            
If Forms!PreviousTestRequestForm!EmailAdditionEgineers.ItemsSelected.Count <> 0 Then
    For Each oItem In Forms!PreviousTestRequestForm!EmailAdditionEgineers.ItemsSelected
        If iCount = 0 Then
            ListItem = ListItem & Forms!PreviousTestRequestForm!EmailAdditionEgineers.ItemData(oItem)
            AllEmail = DLookup("EmailAddress", "AdditionalEmailRequestQuery", "[ID] = " & ListItem)
            iCount = iCount + 1
        Else
            ListItem = Forms!PreviousTestRequestForm!EmailAdditionEgineers.ItemData(oItem)
            AllEmail = AllEmail & "; " & DLookup("EmailAddress", "AdditionalEmailRequestQuery", "[ID] = " & ListItem)
            iCount = iCount + 1
        End If
    Next oItem
Else
    MsgBox "No one was selected for who to email. Please select a Certs Engineer before continuing.", vbExclamation
    Exit Function
End If

 

[GinaWhipp]

The only thing that counts is you got it to work the way you need it... Great Job!