Listbox help...

[cunger28]

I've read through numerous threads and have seen repeatedly that listboxes
are just a flat out pain. I'm hoping someone can help shed some light...

My scenario is this:
I need to create an email form/function. I have a form that contains data
points that need to be included in an email if escalation is required for
that particular record. I'll tackle the email part later....

Rather than the end user manually have to hunt for email addresses, I want
to import, from Exchange, my global address list. I got that done. The key
piece of that list is the Alias (Name). Single company domain, so that's not
a concern.

I want the end user to be able to select the Alias(es) they need to include
in the escalation email process from a list. I also want to be able to store
the names they selected for tracking and historical purposes.

In my head I see a textbox that is updated with the listbox selections. The
textbox would then be used as the To: variable in my mail script and also be
used to store what Alias(es) the selected to mail.

That's where I'm at, accomplishing it has me stuck.

Thanks in advance,

 

[Ken Sheridan]

Chris:

Firstly set the list box's MultiSelect property to 'Simple' or 'Extended',
whichever suits you best. The control's RowSource property will be a simple
SQL statement getting the aliases from your addresses table, e.g.

SELECT Alias FROM YourAddressesTable ORDER BY Alias;

To record the aliases selected for each record I'd suggest that you create a
table which references the form's underlying table's primary key (I'll call
it MyID and assume it’s a number data type for this example), so the table,
AliasesSelected say, would have columns such as MyID, DateTimeSelected and
Alias. The primary key of this table would be a composite one of all three
columns.

You can fill the unbound text box, txtAliasList say, with a delimited list
of aliases, and at the same time insert rows into the AliasesSelected table
with code in the event procedure of a 'confirm' button after selecting one or
more aliases from the list box, which Ill call lstAliases, like so:

Dim ctrl As Control
Dim strSQL As String
Dim strAliasList As String
Dim varItem As Variant
Dim cmd As ADODB.Command

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

Set ctrl = Me.lstAliases

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected

' add alias to list, separating them with semi-colons
strAliasList = strAliasList & ";" & ctrl.ItemData(varItem)

' build and execute SQL statement to insert
' row into AliasesSelected table
strSQL = "INSERT INTO AliasesSelected" & _
"(MyID, DateTimeSelected, Alias) " & _
"VALUES(" & Me.MyID & ", #" & _
Format(Now(),"yyyy-mm-dd hh:nn:ss") & "#, " & _
"""" & ctrl.ItemData(varItem) & """)"

cmd.CommandText = strSQL
cmd.Execute

Next varItem

' remove leading semi-colon
strAliasList = Mid(strAliasList, 2)

' assign list to text box
Me.txtAliasList = strAliasList
Else
MsgBox "No aliases selected", vbInformation, "Warning"
End If

I've delimited the list with a semi-colon in the above, but it’s a simple
task to amend the code to use another character if necessary.

Ken Sheridan
Stafford, England

 

[cunger28]

Hey Ken,

I'm definitely making some progress, so that's a plus. Thanks for the help
with that code. The problem I'm running into at this point:

List box is populating. I set up the command button with the code you
provided. Data isn't physically being written to the table AliasesSelected
though. Also, the txtAliasList is being populated, but it's populating the
ID correllating to the Alias and not the Alias itself. Here's the code I
used:

Private Sub Command2_Click()
Dim ctrl As Control
Dim strSQL As String
Dim strAliasList As String
Dim varItem As Variant
Dim cmd As ADODB.Command

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

Set ctrl = Me.lst_Alias

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected

' add alias to list, separating them with semi-colons
strAliasList = strAliasList & ";" & ctrl.ItemData(varItem)

' build and execute SQL statement to insert
' row into AliasesSelected table
strSQL = "INSERT INTO AliasesSelected" & _
"(MyID, DateTimeSelected, Alias) " & _
"VALUES(" & Me.MyID & ", #" & _
Format(Now(), "yyyy-mm-dd hh:nn:ss") & "#, " & _
"""" & ctrl.ItemData(varItem) & """)"

Next varItem

' remove leading semi-colon
strAliasList = Mid(strAliasList, 2)

' assign list to text box
Me.txtAliasList = strAliasList
Else
MsgBox "No aliases selected", vbInformation, "Warning"
End If

End Sub

txtAliasList, when records are being selected is returning 1;2;3;4 instead
of A.Allen, B.Bryan, D.Douglas, F.Frank if that makes sense....

Thanks in advance!

Chris:

Firstly set the list box's MultiSelect property to 'Simple' or 'Extended',
whichever suits you best. The control's RowSource property will be a simple
SQL statement getting the aliases from your addresses table, e.g.

SELECT Alias FROM YourAddressesTable ORDER BY Alias;

To record the aliases selected for each record I'd suggest that you create a
table which references the form's underlying table's primary key (I'll call
it MyID and assume it’s a number data type for this example), so the table,
AliasesSelected say, would have columns such as MyID, DateTimeSelected and
Alias. The primary key of this table would be a composite one of all three
columns.

You can fill the unbound text box, txtAliasList say, with a delimited list
of aliases, and at the same time insert rows into the AliasesSelected table
with code in the event procedure of a 'confirm' button after selecting one or
more aliases from the list box, which Ill call lstAliases, like so:

Dim ctrl As Control
Dim strSQL As String
Dim strAliasList As String
Dim varItem As Variant
Dim cmd As ADODB.Command

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

Set ctrl = Me.lstAliases

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected

' add alias to list, separating them with semi-colons
strAliasList = strAliasList & ";" & ctrl.ItemData(varItem)

' build and execute SQL statement to insert
' row into AliasesSelected table
strSQL = "INSERT INTO AliasesSelected" & _
"(MyID, DateTimeSelected, Alias) " & _
"VALUES(" & Me.MyID & ", #" & _
Format(Now(),"yyyy-mm-dd hh:nn:ss") & "#, " & _
"""" & ctrl.ItemData(varItem) & """)"

cmd.CommandText = strSQL
cmd.Execute

Next varItem

' remove leading semi-colon
strAliasList = Mid(strAliasList, 2)

' assign list to text box
Me.txtAliasList = strAliasList
Else
MsgBox "No aliases selected", vbInformation, "Warning"
End If

I've delimited the list with a semi-colon in the above, but it’s a simple
task to amend the code to use another character if necessary.

Ken Sheridan
Stafford, England

 

[cunger28]

Looks like it's not grabbing "ctrl.ItemData(varItem)"

Hey Ken,

I'm definitely making some progress, so that's a plus. Thanks for the help
with that code. The problem I'm running into at this point:

List box is populating. I set up the command button with the code you
provided. Data isn't physically being written to the table AliasesSelected
though. Also, the txtAliasList is being populated, but it's populating the
ID correllating to the Alias and not the Alias itself. Here's the code I
used:

Private Sub Command2_Click()
Dim ctrl As Control
Dim strSQL As String
Dim strAliasList As String
Dim varItem As Variant
Dim cmd As ADODB.Command

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

Set ctrl = Me.lst_Alias

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected

' add alias to list, separating them with semi-colons
strAliasList = strAliasList & ";" & ctrl.ItemData(varItem)

' build and execute SQL statement to insert
' row into AliasesSelected table
strSQL = "INSERT INTO AliasesSelected" & _
"(MyID, DateTimeSelected, Alias) " & _
"VALUES(" & Me.MyID & ", #" & _
Format(Now(), "yyyy-mm-dd hh:nn:ss") & "#, " & _
"""" & ctrl.ItemData(varItem) & """)"

Next varItem

' remove leading semi-colon
strAliasList = Mid(strAliasList, 2)

' assign list to text box
Me.txtAliasList = strAliasList
Else
MsgBox "No aliases selected", vbInformation, "Warning"
End If

End Sub

txtAliasList, when records are being selected is returning 1;2;3;4 instead
of A.Allen, B.Bryan, D.Douglas, F.Frank if that makes sense....

Thanks in advance!

 

[Ken Sheridan]

You didn't mention that the alias was not the list box's bound column.
Assuming the ID is the (presumably hidden) first column and the alias the
second, you'll need to reference the second column like so:

' add alias to list, separating them with semi-colons
strAliasList = strAliasList & ";" & ctrl.Column(1,varItem)

and:

strSQL = "INSERT INTO AliasesSelected" & _
"(MyID, DateTimeSelected, Alias) " & _
"VALUES(" & Me.MyID & ", #" & _
Format(Now(), "yyyy-mm-dd hh:nn:ss") & "#, " & _
"""" & ctrl.Column(1,varItem) & """)"

I'm not surprised that its currently not inserting any rows into the table,
however, as you appear to have omitted the two lines of code after the above:

cmd.CommandText = strSQL
cmd.Execute

If you'd included these, but with the original SQL statement, it would have
inserted the rows, but with the ID values in the Alias column, not the actual
aliases.

Ken Sheridan
Stafford, England