Can't reference value of this object

[scrawny]

Hi!

I'm currently getting an error out of the following code on the odd
occasion:

Public Sub Add2Table(frmChange as Form, recordID as Control)

Dim ctrl as Control

On Error Goto ErrorHandler

For Each ctrl in frmChange.Controls
With ctrl
If (.ControlType = acTextBox) or (.ControlType = acComboBox) or
(.ControlType = acListBox) then
If (.Value <> .OldValue) then
'SQL code to add new value to the table
End If
End If
End with
Next

My problem is, is that on the odd occasion, the control can be a
multi-
select combo box. That lists its values in ctrl as an array (ie.
ctrl.Value(0), ctrl.Value(1) etc.). So, the above code returns an
error because the .Value property is invalid. Is there any way of
getting around this. I've been trying to look for some property of the
control that tells me if it is a multi-select combo (as opposed to an
ordinary combo). But I can't seem to find anything. I can trap the
error and perhaps use the .Text value - but that starts to get a
little clunky as there is already an error trap.

 

[Piet Linden]

Hi!

I'm currently getting an error out of the following code on the odd
occasion:

Public Sub Add2Table(frmChange as Form, recordID as Control)

Dim ctrl as Control

On Error Goto ErrorHandler

For Each ctrl in frmChange.Controls
   With ctrl
      If (.ControlType = acTextBox) or (.ControlType = acComboBox) or
(.ControlType = acListBox) then
         If (.Value <> .OldValue) then
             'SQL code to add new value to the table
         End If
      End If
   End with
Next

My problem is, is that on the odd occasion, the control can be a
multi-
select combo box. That lists its values in ctrl as an array (ie.
ctrl.Value(0), ctrl.Value(1) etc.). So, the above code returns an
error because the .Value property is invalid. Is there any way of
getting around this. I've been trying to look for some property of the
control that tells me if it is a multi-select combo (as opposed to an
ordinary combo). But I can't seem to find anything. I can trap the
error and perhaps use the .Text value - but that starts to get a
little clunky as there is already an error trap.

multi-select combobox... shudder... I guess you could use INSTR() to
find if a field contains a comma, and then use SPLIT to break it out
in to separate values... personally, I'd stay away from multi-valued
fields. It's one of those bizarre non-normalized feature things of
A07. IMO, it's more of a misfeature, but that's just my opinion...

 

[scrawny]

I can probably see why actually - I'm not having much fun with them at
all.

I believe the fact that it's data is grabbed from a related table (1
to many) - is the reason it's multi select.

Maybe I should use a list box...? Would that work?

 

[Piet Linden]

I can probably see why actually - I'm not having much fun with them at
all.

I believe the fact that it's data is grabbed from a related table (1
to many) - is the reason it's multi select.

Maybe I should use a list box...? Would that work?

If you use a multi-select listbox, at least you can loop through the
ItemsSelected collection or the individual values in it and process
them. I would slap up a quick example to see if the multi-select
combobox is what's causing the problem. As I've never used them, I
really don't know. Here's a really quick example of processing the
contents of a multi-select listbox...

Private Sub cmdShowSelected_Click()
Dim varItem As Variant
Dim strList As String

For Each varItem In Me.List0.ItemsSelected
strList = strList + ", " + Me.List0.ItemData(varItem)
Next varItem

strList = Right$(strList, Len(strList) - 2)

MsgBox strList, vbOKOnly

End Sub

You could process the individual items any way you want inside the For
Next loop...