Retrieving Data from Listbox

[jas580]

I have a form with two list boxes, and add/remove buttons. The user moves
Account Numbers from the first box [Accounts] to the second [Current
Accounts].

Once users have selected the accounts they want in a report, I need those
account numbers to pass to a table [Acct Temp]. The report queries are based
on this table. I need to know how to retrieve those account numbers from the
list box and place them in the table. And, how to remove those account
numbers from the table after a report has been run.

Thanks,

Jeff

 

[Ken Sheridan]

Jeff:

Iterate through the items in the list box and execute an SQL statement for
each to insert a row into the table. Rather than emptying the table
afterwards do so before inserting the rows. The code, which could go in the
Click event procedure of a button on the form for instance, would go like
this:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim n As Integer
Dim ctrl As Control

Set ctrl = Me.[Current Accounts]

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

' empty the table
strSQL = "DELETE * FROM [Acct Temp]"
cmd.CommandText = strSQL
cmd.Execute

' loop through list box's items
' and insert row into table for each
For n = 0 To ctrl.ListCount - 1
strSQL = "INSERT INTO [Acct Temp]" & _
"([Account Number]) " & _
"VALUES(" & ctrl.ItemData(n) & ")"
cmd.CommandText = strSQL
cmd.Execute
Next n

I've assumed the column in [Acct Temp] is called [Account Number] and is
number data type. If its text wrap the value in quotes:

strSQL = "INSERT INTO [Acct Temp]" & _
"([Account Number]) " & _
"VALUES(""" & ctrl.ItemData(n) & """)"

You don't really need to fill a temporary table for this, however. You
could base the report on a query which returns all rows from the main
accounts table(s) and open it from a button on the form filtered to the
selected accounts, like so:

Dim ctrl As Control
Dim strAcctList as String
Dim strFilter As String
Dim n as Integer

Set ctrl = Me.[Current Accounts]

' loop through list box's items
' and build a value list of account numbers
For n = 0 To ctrl.ListCount - 1
strAcctList = strAcctList & "," & ctrl.ItemData(n)
Next n

' remove leading comma
strAcctList = Mid(strAcctList,2)

strFilter = "[Account Number] In (" & strAcctList & ")"

' open report in print preview
DoCmd.OpenReport "YourReport", _
View:=acViewPreview, _
WhereCondition:=strFilter

Again If the account number column is text wrap the value in quotes:

strAcctList = strAcctList & ",""" & ctrl.ItemData(n) & """"

Ken Sheridan
Stafford, England

 

[jas580]

Ken, thank you- it worked perfectly! You brought up a good point with the
query though and I have a question related to that...

Could I get rid of the second list box and Add/Remove buttons and just run
that query based on the items selected in the first list box? This would be
a lot cleaner I think. This might be exactly what you were saying, but I'm
not sure.

The first box [Accounts], has [Acct ID] and [Acct Name] fields. [Acct ID]
is a stored number. I'm not a programmer, so I need some help adjusting the
code. I think it gets more complicated with multiple fields.

Thanks again...

-Jeff

Jeff:

Iterate through the items in the list box and execute an SQL statement for
each to insert a row into the table. Rather than emptying the table
afterwards do so before inserting the rows. The code, which could go in the
Click event procedure of a button on the form for instance, would go like
this:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim n As Integer
Dim ctrl As Control

Set ctrl = Me.[Current Accounts]

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

' empty the table
strSQL = "DELETE * FROM [Acct Temp]"
cmd.CommandText = strSQL
cmd.Execute

' loop through list box's items
' and insert row into table for each
For n = 0 To ctrl.ListCount - 1
strSQL = "INSERT INTO [Acct Temp]" & _
"([Account Number]) " & _
"VALUES(" & ctrl.ItemData(n) & ")"
cmd.CommandText = strSQL
cmd.Execute
Next n

I've assumed the column in [Acct Temp] is called [Account Number] and is
number data type. If its text wrap the value in quotes:

strSQL = "INSERT INTO [Acct Temp]" & _
"([Account Number]) " & _
"VALUES(""" & ctrl.ItemData(n) & """)"

You don't really need to fill a temporary table for this, however. You
could base the report on a query which returns all rows from the main
accounts table(s) and open it from a button on the form filtered to the
selected accounts, like so:

Dim ctrl As Control
Dim strAcctList as String
Dim strFilter As String
Dim n as Integer

Set ctrl = Me.[Current Accounts]

' loop through list box's items
' and build a value list of account numbers
For n = 0 To ctrl.ListCount - 1
strAcctList = strAcctList & "," & ctrl.ItemData(n)
Next n

' remove leading comma
strAcctList = Mid(strAcctList,2)

strFilter = "[Account Number] In (" & strAcctList & ")"

' open report in print preview
DoCmd.OpenReport "YourReport", _
View:=acViewPreview, _
WhereCondition:=strFilter

Again If the account number column is text wrap the value in quotes:

strAcctList = strAcctList & ",""" & ctrl.ItemData(n) & """"

Ken Sheridan
Stafford, England

I have a form with two list boxes, and add/remove buttons. The user moves
Account Numbers from the first box [Accounts] to the second [Current
Accounts].

Once users have selected the accounts they want in a report, I need those
account numbers to pass to a table [Acct Temp]. The report queries are based
on this table. I need to know how to retrieve those account numbers from the
list box and place them in the table. And, how to remove those account
numbers from the table after a report has been run.

Thanks,

Jeff

 

[Ken Sheridan]

Jeff:

It wasn't quite what I was saying, but the same thought had occurred to me
too. What I normally do for this sort of thing is to have a multi-select
list box, an Open Report button and a Clear Selections button. The user then
selects however many items they want from the list and clicks Open Report.
They can then deselect all items by clicking Clear Selections or individual
items by clicking selected items again.

First thing to decide is how you want the selections to be made. Setting
the list box's MultiSelect property to Simple means the user just clicks
items one by one; setting it to Extended means they can use Ctrl+Click or
Shift+Click to select ranges of items in the usual way.

The code for the OpenReport button iterates through the list box's
ItemsSelected collection and builds a value list of Acct IDs, and then opens
the report filtered to these. The code would go like this:


Dim varItem As Variant
Dim strAcctIDList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.Accounts

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strAcctIDList = strAcctIDList & "," & ctrl.ItemData(varItem)
Next varItem

' remove leading comma
strAcctIDList = Mid(strAcctIDList, 2)

strCriteria = "[Acct ID] In(" & strAcctIDList & ")"

DoCmd.OpenReport "YourReport", _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
MsgBox "No accounts selected", vbInformation, "Warning"
End If

If the Acct ID column is a text data type use:

For Each varItem In ctrl.ItemsSelected
strAcctIDList = strAcctIDList & ",""" & ctrl.ItemData(varItem) &
""""
Next varItem

The code for the Clear Selections button would be:

Dim n As Integer

For n = 0 To Me.Accounts.ListCount - 1
Me. Accounts.Selected(n) = False
Next n

Ken Sheridan
Stafford, England

Ken, thank you- it worked perfectly! You brought up a good point with the
query though and I have a question related to that...

Could I get rid of the second list box and Add/Remove buttons and just run
that query based on the items selected in the first list box? This would be
a lot cleaner I think. This might be exactly what you were saying, but I'm
not sure.

The first box [Accounts], has [Acct ID] and [Acct Name] fields. [Acct ID]
is a stored number. I'm not a programmer, so I need some help adjusting the
code. I think it gets more complicated with multiple fields.

Thanks again...

-Jeff

Jeff:

Iterate through the items in the list box and execute an SQL statement for
each to insert a row into the table. Rather than emptying the table
afterwards do so before inserting the rows. The code, which could go in the
Click event procedure of a button on the form for instance, would go like
this:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim n As Integer
Dim ctrl As Control

Set ctrl = Me.[Current Accounts]

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

' empty the table
strSQL = "DELETE * FROM [Acct Temp]"
cmd.CommandText = strSQL
cmd.Execute

' loop through list box's items
' and insert row into table for each
For n = 0 To ctrl.ListCount - 1
strSQL = "INSERT INTO [Acct Temp]" & _
"([Account Number]) " & _
"VALUES(" & ctrl.ItemData(n) & ")"
cmd.CommandText = strSQL
cmd.Execute
Next n

I've assumed the column in [Acct Temp] is called [Account Number] and is
number data type. If its text wrap the value in quotes:

strSQL = "INSERT INTO [Acct Temp]" & _
"([Account Number]) " & _
"VALUES(""" & ctrl.ItemData(n) & """)"

You don't really need to fill a temporary table for this, however. You
could base the report on a query which returns all rows from the main
accounts table(s) and open it from a button on the form filtered to the
selected accounts, like so:

Dim ctrl As Control
Dim strAcctList as String
Dim strFilter As String
Dim n as Integer

Set ctrl = Me.[Current Accounts]

' loop through list box's items
' and build a value list of account numbers
For n = 0 To ctrl.ListCount - 1
strAcctList = strAcctList & "," & ctrl.ItemData(n)
Next n

' remove leading comma
strAcctList = Mid(strAcctList,2)

strFilter = "[Account Number] In (" & strAcctList & ")"

' open report in print preview
DoCmd.OpenReport "YourReport", _
View:=acViewPreview, _
WhereCondition:=strFilter

Again If the account number column is text wrap the value in quotes:

strAcctList = strAcctList & ",""" & ctrl.ItemData(n) & """"

Ken Sheridan
Stafford, England

I have a form with two list boxes, and add/remove buttons. The user moves
Account Numbers from the first box [Accounts] to the second [Current
Accounts].

Once users have selected the accounts they want in a report, I need those
account numbers to pass to a table [Acct Temp]. The report queries are based
on this table. I need to know how to retrieve those account numbers from the
list box and place them in the table. And, how to remove those account
numbers from the table after a report has been run.

Thanks,

Jeff