Get Field Values from Multiple records on a Form

[Eka1618]

Hello,

In a form called frmQueue, I have it set up to show one record at a time.
When a person closes this from by clicking btnAccept, It sends an e-mail to
one person called the Requestor. What I would like this form have do now is
also send out e-mails to those who were assigned tests.

I have a listbox called lboConductor that can only hold one list item for
each record. lboConductor has 3 fields, one being their e-mail address. I
have placed the following code in the AfterUpdate event for the listbox, but
it still will not work in my situation.

A person who is scheduling a test may go back to a previous record that he
filled out and change who he wants to conduct the test before click
btnAccept, the code that I have will only keep adding to the String that I
have to grab the e-mail addresses. At the time btnAccept is clicked, I want
it to grab each e-mail name for all the records that show up in this form and
send individual e-mails to each test conductor.

I am not sure if this is a little too complicated, but it would be ideal to
send each person who was just scheduled a confirmation of somesort. I am not
sure how to go about this.

Before looking at my code, just note that it is by no means complete, I just
started with this but then realized that It wouldn't work... I just wanted to
try and explain as best as I can what I want to do.

If anyone as any suggestions, please let me know, Thank you!

Private Sub lboConductor_AfterUpdate()
Dim varItem As Variant

For Each varItem In Me!lboConductor.ItemsSelected
eNames = eNames & Chr(34) & frm!lboConductor.Column(2, varItem) &
Chr(34) & ","
Next varItem
End Sub

 

[Ralph]

Try addding a text box to your form then use the On Click event for the list
box to add values or delete values for it.

Private Sub List1_Click()
For Each varItem In Me!lboConductor.ItemsSelected
eNames = eNames & Chr(34) & frm!lboConductor.Column(2, varItem) &
Chr(34) & ","
Next varItem
me.Text1=eNames

End Sub

 

[Eka1618]

Thanks for the tip Ralph,

I think i see where your going with this, so I"m going to try this in a
little while. Please keep in touch if i have any other questions. Thank you!

 

[Ralph]

You will probably have to add a text field to the table to store the values.
Also you will want to trim the last comma from you address list.

left(me.Text1=eNames,len(me.Text1=eNames)-1))

 

[Eka1618]

I know i'll have to trim the last comma off the e-mail list, I'm not sure if
I need another field in my table though.

I would like to create a list of some sort (like an array) that will store
not only the e-mail name, but the test number that was assigned to them. When
the user is finished, loop through the array and send an e-mail to each test
conductor with their associated test number.

I'm not sure how to set up the code to determine if a person has been
changed. For instance, if a person schedules a test conductor, that person is
added to the array. If a person goes back to this field and changes the
conductor, How can I tell the array to replace the old value with the new
value instead of just adding to the list?

I just do not want to store this information because i really only need it
for this form.
What do you think?

 

[Ralph]

I created a form with a TestID, Testee, lstConductors, and lstTemp.
I used an array to grab the values from TestID, Testee and lstConductors and
copy them to lstTemp which has 3 columns. I use a button to accomplish it.
Once the user has selected the conductors from the list they would click on
the button and copy them to lstTemp. You could use a button for the user to
remove a conductor. I am not sure how you would just store these in an array
then add or delete them. Below is my code, hope it is not too confusing.

Private Sub cmdCopy_Click()
Dim i As Variant
Dim lstArray() As Variant
Dim r As Integer
Dim s As Integer
Dim strCol1 As String
Dim strCol2 As String
Dim strCol3 As String

For Each i In Me.lstConductors.ItemsSelected
r = r + 1
Next

'get TestID (text box), Testee (text box), and lstConductors (list box)
If r > 0 Then
ReDim Preserve lstArray(r - 1, 2)
For Each i In Me.lstConductors.ItemsSelected
lstArray(s, 0) = Me.TestID
lstArray(s, 1) = Me.Testee
lstArray(s, 2) = Me.lstConductors.Column(0, i)
s = s + 1
Next

'add selections from array to lstTemp (list box)
For r = 0 To UBound(lstArray)
strCol1 = lstArray(r, 0)
strCol2 = lstArray(r, 1)
strCol3 = lstArray(r, 2)
Me.lstTmp.AddItem strCol1 & ";" & strCol2 & ";" & strCol3
Next

End If
End Sub

 

[Eka1618]

I know what you're saying here. I think that creating a button to add to this
list will not really work in my situation though and I think it might defeat
the purpose of having these e-mails generate automatically, let me explain...

My form shows one record at a time. The user fills out a bunch of data, then
hits the 'NEXT' button to go to the next record. The records that are in this
form come from a query, so there can be X amount of records, but right now
the most it could ever get to is 7 records.

Anyway, The list box that I have on my page contains the e-mail address that
I need in the 3rd column.

My list box is: [REQUESTEE],[EMP_LAST],[E_MAIL]

The Test Queue ID is in a text box on the form. Another important thing I
need to obatin is the Requestor's e-mail which is in another listbox in the
same column, but that I can get easily because in this situation, the
REQUESTOR is the same for each Test Queue ID.

The reason why I dont want them to have to click all kinds of buttons is
because they are already selecting the person when they fill out the form. It
will be to confusing for then to be like "ok i dont want this person any
more, but I want that person," and so on. Also, since each record displays
seperately, I'm not sure is listTemp would reset each time... (if any of that
made any sense)

What would be ideal is to have code that looks up each conductor, ther
e-mail and the Test Queue ID that was assigned to them and then send out the
e-mail at the time the user clicks the finish button. Now I'm thinking that
maybe I could use a query to look up these values, then use a DLookUp() or
loop through the records in that query. I'm not sure how I can do this, but
maybe I could query the test queue table (tblQueue) which holds the [QID] and
[TEST_COND] (which is the [EMP_ID] from tblEmployee). Their e-mail address is
located in tblEmployee as [E_MAIL]. I should beable to do this, I think...

I just do not know if an array will be the most efficient way to handle this
since removing people becomes an issue and the conductor may change after its
been set. I am going to try this method out today, Let me know what you
think!

Thanks again for the advice!