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 important ErrorHandler trap.

Scrawny,

Multi-select only applies to Listboxes, not Comboboxes.

It seems to me that you could amend your code my building in a provision
like this:

If .ControlType = acListBox And .MultiSelect = True Then ...

--
Steve Schapel, Microsoft Access MVP


"scrawny" <(E-Mail Removed)> wrote in message
news:3a95ba48-db4a-409c-a847-(E-Mail Removed)...
> 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 important ErrorHandler trap.

Hi Steve!

The ControlType of the list is actually 111 which is acComboBox. Also,
MultiSelect doesn't apply to this control (I can't reference that
property - causes an error).

Any other suggestions?

Cheers,

Scrawny

Scrawny,

Ah! You are using Access 2007, aren't you? And the field that this control
is bound to has its Allow Multiple Values property set to Yes, right?

No, I'm sorry, I don't know. I know how to manage MVFs (multi value fields)
in queries, but not to do the kind of thing you are doing.

As this is a macros newsgroup, and the question does not relate at all to
macros, may I suggest you post in another newsgroup, such as forms coding,
as you may have a better chance of getting a good answer over there.

--
Steve Schapel, Microsoft Access MVP


"scrawny" <(E-Mail Removed)> wrote in message
news:a321ccbc-7eed-4855-be04-(E-Mail Removed)...
> Hi Steve!
>
> The ControlType of the list is actually 111 which is acComboBox. Also,
> MultiSelect doesn't apply to this control (I can't reference that
> property - causes an error).
>
> Any other suggestions?
>
> Cheers,
>
> Scrawny
>

Yes I am using 2007... have been trawling through the properties list
of this thing and cannot find anything that even remotely resembles
multi value property. Anyway, I'll go elsewhere. I'm 99% certain is
has something to do with the fact that the values populated for this
list come from a related table (1 to many).

Cheerio,

Scrawny