Selecting Multiple values from List in a Form (1 Viewer)

Hi! I am trying to use a list-control on a form to let the user select multiple values. I have understood that this requires some VBA-code to step through the selections in the list, since the "multivalue-selection" is set to "Extended".

When I try to execute the code I have (found and have tried to adjust), then I get the error message "Object required". The "ListCount"-paramater always only results in a ZERO-value, when i step through the code:

Here's the code I'm trying to use:

ANYBODY's got any ideas?

Greetings from Sweden
BJörn

'------------------ Code Start ------------------

Function cmdOpenQuery_Click()

On Error GoTo Err_cmdOpenQuery_Click
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant

Set MyDB = CurrentDb()

strSQL = "SELECT * FROM tbl_Aktuella_BUar"

'Build the IN string by looping through the listbox
For i = 0 To Form4BU.List19.ListCount - 1
If Form4BU.List19.Selected(i) Then
If Form4BU.List19.Column(0, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & Form4BU.List19.Column(0, i) & "',"
End If
Next i

'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [AktuellaBUar] in " & _
"(" & Left(strIN, Len(strIN) - 1) & ")"

'If "All" was selected in the listbox, don't add the WHERE condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If

MyDB.QueryDefs.Delete "ASSETS_SKANDIA_B/U"
Set qdef = MyDB.CreateQueryDef("ASSETS_SKANDIA_B/U", strSQL)

'Open the query, built using the IN clause to set the criteria
DoCmd.OpenQuery "ASSETS_SKANDIA_B/U", acViewNormal

'Clear listbox selection after running query
For Each varItem In Form4BU.List19.ItemsSelected
Form4BU.List19.Selected(varItem) = False
Next varItem


Exit_cmdOpenQuery_Click:
Exit Function

Err_cmdOpenQuery_Click:

If Err.Number = 5 Then
MsgBox "You must make a selection(s) from the list" _
, , "Selection Required !"
Resume Exit_cmdOpenQuery_Click
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_cmdOpenQuery_Click
End If

End Function

'------------------ Code End -------------------

birbjo said:

When I try to execute the code I have (found and have tried to adjust), then I get the error message "Object required".

Click to expand...

In which line do you get that, mark it with red, (and please you code tags when you post code it is much easier to read).

Hi!
Right in the end of the code.....what I don't understand is why the ListCount returns a ZERO value all the time and just jumps to the Error handler directly...
(Sorry about the code being hard to read....)
Björn


If Err.Number = 5 Then
MsgBox "You must make a selection(s) from the list" _
, , "Selection Required !"
Resume Exit_cmdOpenQuery_Click
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_cmdOpenQuery_Click
End If

End Function

'------------------ Code End -------------------

In order to be able to answer toJHB's question you must remove (or quote) this line:
On Error GoTo Err_cmdOpenQuery_Click

I think that the error is in each structure like this:
Form4BU.List19

To refer a form from VBA you must use this syntax::
Forms!Form4BU.List19

or to use the VBA name for the form (if exist in Project Explore window)
Form_Form4BU.List19

VBA add the Form_ prefix to each form name.

Hi!
Oh yes, the syntrax was incorrect! Now I got the list to return the selected values!! Thx ever so much!!!

Will see if we can institute a Nobelprize in Access next year! Then You will be the first nominee!"

Thank you !!!!!!!!!
Can I gain some beer until that time ?

Good luck !

Just in case someone else reads this

To refer a form from VBA you must use this syntax::
Forms!Form4BU.List19

---do not do this:
or to use the VBA name for the form (if exist in Project Explore window)
Form_Form4BU.List19

Click to expand...

Forms!Form4BU.List19 and Form_Form4BU.List19 are very definitely NOT equivalent.

Use the Forms!MyFormName!MyControlName syntax only.


The second syntax can cause many unexpected effects that you'll spend weeks chasing.

@spikepl
Now you are in trouble because now you MUST explain in more details.
I used many times the second structure and it works every time.
In fact, first I used the second structure (because my first background: Excel) and later the first one.

So, what are the differences and how should be used each one of this approaches ?

Thank you !

I used many times the second structure and it works every time.

Click to expand...

Sticking your fingers into a wall socket but escaping with your life, so far, is no proof that it is a sensible thing to do.

Form_MyForm.ControlName, if the form MyForm is closed, will open an INVISIBLE instance of the form in the background. But you can still open MyForm normally, and now you'll be picking up values from controls in one or the other, not always the one you see on screen. Not ideal, if you do not know that that is happening!

Thank you !
Never mind.
Of course I'll do a try next days. Seems to be a very useful way to confuse a user that don't pay for a program