I have no education in VBA. I developped some notions of it, though. Usually, I can solve the problems in my database with some copy and paste from this excellent forum.
However, this time I don't manage.
Here's what I'm trying to do: I have a form linked to a table that contains groups of emailadresses.
How do I create those groups? I have a listbox on my form based on a query that selects all the entries with emailadresses from a table with people's personal data.
It is possible to highlight various persons in the listbox, and select a button that creates a string with all the emailaddresses from those persons, and adds the string to a memo field in the form with the groups of emailaddresses.
This worked just fine, until the following problem came up: sometimes to people have the same emailaddress. I want to avoid having the same emailaddress twice in the string.
How did I try to solve that? I created a table with only one field: EmailAddress and defined that field as the primary key, no duplicates. I figured, if I could add the emailaddresses of the highlighted persons first to that table, and then build the string from that table, I would avoid having duplicates in the string.
Since I use the same table all the time, I first have to empty it.
The problem is I just don't get the code to work. Since I'm the summum of an amateur, I figured I'd post it here, with two questions:
1. Is it even worth continuing to construct the code, or should I try a whole different path?
2. If 1 is answered positively, what (all) is wrong in the code?
If 1 is answered negatively, any suggestions on how to do it?
This is the code I have so far:
Private Sub Command8_Click()
Dim f As Form
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim intCounter As Integer
Dim i As Integer
Dim i2 As Integer
Dim strlist As String
Dim strDuplicate As String
Dim strEmailAddresses As String
Set f = Forms!MultiEmailForm
Set db = CurrentDb()
Set rst = db.OpenRecordset("TempEmailListTable", dbOpenDynaset)
'Clearing the table
For i = 0 To rst.RecordCount Step 1
rst.Delete
rst.MoveNext
rst.Update
Next i
'Filling the table with the emailaddresses from the highlighted persons
For intCounter = 0 To Me.List0.ListCount Step 1
If Me.List0.Selected(intCounter) = True Then
strlist = Me.List0.Column(3, intCounter)
With rst
.AddNew
![EmailAddress] = strlist
.Update
End With
End If
Next intCounter
'Building the string
For i2 = 0 To rst.RecordCount Step 1
If strDuplicate = rst![EmailAddress] Then
rst.MoveNext
Else
strEmailAddresses = strEmailAddresses & rst![EmailAddress] & ";"
strDuplicate = rst![EmailAddress]
rst.MoveNext
End If
Next i2
rst.Close
f.Listgroupstring = strEmailAddresses
End Sub
However, this time I don't manage.
Here's what I'm trying to do: I have a form linked to a table that contains groups of emailadresses.
How do I create those groups? I have a listbox on my form based on a query that selects all the entries with emailadresses from a table with people's personal data.
It is possible to highlight various persons in the listbox, and select a button that creates a string with all the emailaddresses from those persons, and adds the string to a memo field in the form with the groups of emailaddresses.
This worked just fine, until the following problem came up: sometimes to people have the same emailaddress. I want to avoid having the same emailaddress twice in the string.
How did I try to solve that? I created a table with only one field: EmailAddress and defined that field as the primary key, no duplicates. I figured, if I could add the emailaddresses of the highlighted persons first to that table, and then build the string from that table, I would avoid having duplicates in the string.
Since I use the same table all the time, I first have to empty it.
The problem is I just don't get the code to work. Since I'm the summum of an amateur, I figured I'd post it here, with two questions:
1. Is it even worth continuing to construct the code, or should I try a whole different path?
2. If 1 is answered positively, what (all) is wrong in the code?
If 1 is answered negatively, any suggestions on how to do it?
This is the code I have so far:
Private Sub Command8_Click()
Dim f As Form
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim intCounter As Integer
Dim i As Integer
Dim i2 As Integer
Dim strlist As String
Dim strDuplicate As String
Dim strEmailAddresses As String
Set f = Forms!MultiEmailForm
Set db = CurrentDb()
Set rst = db.OpenRecordset("TempEmailListTable", dbOpenDynaset)
'Clearing the table
For i = 0 To rst.RecordCount Step 1
rst.Delete
rst.MoveNext
rst.Update
Next i
'Filling the table with the emailaddresses from the highlighted persons
For intCounter = 0 To Me.List0.ListCount Step 1
If Me.List0.Selected(intCounter) = True Then
strlist = Me.List0.Column(3, intCounter)
With rst
.AddNew
![EmailAddress] = strlist
.Update
End With
End If
Next intCounter
'Building the string
For i2 = 0 To rst.RecordCount Step 1
If strDuplicate = rst![EmailAddress] Then
rst.MoveNext
Else
strEmailAddresses = strEmailAddresses & rst![EmailAddress] & ";"
strDuplicate = rst![EmailAddress]
rst.MoveNext
End If
Next i2
rst.Close
f.Listgroupstring = strEmailAddresses
End Sub
