ItemsSelected Compile Error

[94Sport5sp]

Hi:

I have a Combo Box on a form populated with a query from which the user selects a value. In my code I would like to get the item selected. It is my understanding that ItemsSelected and ItemData would work for this purpose but when coded I get a "compile error Method or Data Member not found" and I cannot figure out why.

I looked on here for sample code and on the net and this is the code (code is in OnExit event) I came up with

If Me.cbRegister.ItemsSelected.Count > 0 Then
' Do some processing
Else
' Display error message
End If

When I switch to form view and select a value from the combo box, On exit the compile error is triggered with .ItemsSelected highlighted. What am I missing?

Thanks

 

[pbaldy]

To the best of my knowledge those are only applicable to a list box (I haven't used the new multi-value field and its combo). If it's a regular combo, simply test its value:

If Len(Me.cbRegister & vbNullString) > 0 Then

which tests for both Null and a zero length string.

 

[spikepl]

Your understanding is incorrect. The documentation for

combobox itemsselected

is the first place to look. It will tell you why.

Learn to use the documentation.

 

[boblarson]

Your understanding is incorrect. The documentation for

combobox itemsselected

is the first place to look. It will tell you why.

Learn to use the documentation.

Just one thing to remember Spike - the documentation is not always correct.

 

[boblarson]

Bear in mind that multi-valued fields require a different method to get the selected count. You can't use the control to get what you need. You need a recordset and a query to get what you want, since they don't work like regular fields. There is actually a table behind the scenes which has the values stored. So selections are not really part of the control's properties, they are a part of the RECORD.

So, to get the number of items selected, you would use something like:

Function GetSelectedCount(strTableName As String, strFieldName As String) As Long
 
Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Database
 
 
strSQL = "Select [" & strFieldName & "].Value From " & strTableName
 
Set db = Currentdb
 
Set rst = db.OpenRecordset(strSQL, dbOpenForwardOnly)
 
If rst.RecordCount > 0 Then
   rst.MoveLast
End If
 
 
   GetSelectedCount = rst.RecordCount
 
rst.Close
Set rst = Nothing
 
End Function

And an important note - You MUST use the .VALUE part in this code for it to work.

 

[94Sport5sp]

Hi Bob:

If I understand you correctly, ItemsSelected, does not apply in my case because the control box is populated by a query. As such, I must use the value of the control box to run a query to retrieve the record the user selected and then I can extract data from that query.

If my understanding is correct then I need to craft an SQL statement to get the three fields I want with a where clause set to the value from the control. Am I following you correctly? In my above example I wanted the count to use the Column to get the fields I wanted but I do not need that if I create and SQL statement to get the record.

Thanks

 

[boblarson]

I am confused. How can you have a multi-select combo box if it is not bound to the table's field which has it. And if you select from that combo then it would change the contents of that field.

As far as I am aware, and I could be wrong, but there is no multi-select for an unbound combo box. You would need a list box to accomplish multi-select for being unbound.

Now, if it is ONLY ONE item that is being selected in a combo box, it is simply just the value of the combo's bound column which you would check:

If Me.cboMyCombo = 3 Then

or if you want a different column than the bound column:

If Me.cboMyCombo.Column(2) = "Something"

 

[94Sport5sp]

Hi Bob:

The ComboBox is populated by a query for the record source. The LimitToList property is set to yes so the user can only select from the list. The query returns 3 values and the ComboBox displays the second value for the user to select and then I can use the combo box name to get the value selected but I also want the value for the first and third field for further processing. So Me.myComboBox and Me.myComboBox.value will give me the item selected now I need the code to get the other two fields. Does Me.myComboBox.column(2) always represent the second field of the currently selected item and Me.myComboBox.column(1) always represent the first field? I will try that tomorrow and see.

Yes the user can only select 1 item from the list.

Thanks

PS: If it matters I am using A2003.

 

[pbaldy]

The column property is zero based, so 1 will be the second field and 2 the third.

 

[boblarson]

Does Me.myComboBox.column(2) always represent the second field of the currently selected item and Me.myComboBox.column(1) always represent the first field?

No, the columns are ZERO based so Column(2) refers to the THIRD (3) column and Column(1) refers to the SECOND (2) column.

 

[94Sport5sp]

Hi:

Yes that does give me what I want.

Thanks

 

[boblarson]

Hi:

Yes that does give me what I want.

Thanks

Good, glad you got it working.