ListBox

[Sean]

On my form I have a combobox with a value list. After I make my selection I
would like to run a query (after_update) and have it populate a list box? Is
this possible and how? The query is simple and will only select one field
from my table with about 20 records. Then is it possible to select certian
values in this list box (may CTRL Click the ones I want) and then have a
report print out based on what I selected in the list box with other
selection on my form?

Thanks,

 

[Graham R Seach]

Sean,

In the combo's AfterUpdate event, you need to add code to set the listbox's
RowSource property.

Private Sub myCombo_AfterUpdate()
Dim strSQL As String

If IsNull(me!myCombo) Then
Me!myListbo.RowSource = ""
Else
strSQL = "SELECT x, y FROM myTable " & _
"WHERE somevalue = " & Me!myCombo

Me!myListbox.RowSource = strSQL
End If
End Sub

The above code assumes 'somevalue' is numeric. If it's textual, use the
following line instead:
"WHERE somevalue = """ & & Me!myCombo & """"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Sean]

Thanks. Also, what I am looking to do is eventually have all of the
information that appears on the form print out on a report.

1st. How do I select multiple rows in my list box (like CTRL Clicking to
select multiple files) I can't CTRL click them, is there a way or can a yes
no box be added for every records that appear. Either way, how do I set it
up and then pass that information to a report for printing?

Thanks,

 

[Graham R Seach]

Sean,

Set the listbox's MultiSelect property to either Simple or Extended,
depending on the sort of selectability you want (test it and see which you
prefer).

Not knowing whether the selected items are numeric or textual, I have
included code for both. This will give you an idea of how to work with the
ItemsSelected collection.

Dim varItem As Variant
Dim strSelected As String

strSelected = ""

For Each varItem In Me.List2.ItemsSelected
'If the selected item is numeric, uncomment the following line
strSelected = strSelected & Me.List2.Column(0, varItem) & ","

'If the selected items are textual, uncomment the following line
' strSelected = strSelected & """" & Me.List2.Column(0, varItem) &
""","
Next varItem

'Get rid of the trailing comma
'If the selected item is numeric, uncomment the following line
' strSelected = "(" & Left(strSelected, Len(strSelected) - 1) & ")"

'If the selected items are textual, uncomment the following line
' strSelected = "(" & Left(strSelected, Len(strSelected) - 2) & """)"

MsgBox strSelected
'Uncomment the following line when you're happy with the result
'DoCmd.OpenReport "somereportname", acViewPreview, , "somefield IN " &
strSelected


Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia