ComboBox on subform - limit to list can be overridden?

[lizo.consulting]

I have a combo box on located a subform, with the listsource of list
contained in a separate table. The Limit to list property is set to
Yes, both in the table and the subform.

All is well until the users pastes a batch of data from excel into the
subform (this is by design, and a possible functionality we are hoping
for). However, data pasted into the combobox field is accepted even if
the text does not match a list in the source table.

Any suggestions on controlling/overriding this behavior? I'm hoping to
ensuring that the data is valid, even when pasted from an external
data source...

Thanks.

 

[Jeanette Cunningham]

Hi lizo,
I just tried this with a regular combo and I found that the combo only
accepted the data pasted in to it until I tried to save or dropped the
combo's list.
I noticed that you say that the limit to list property is set to Yes in the
table. Does this mean that you are using a lookup field in the table for
this field?
If so, that would seem to be the cause of your problem.
Many (most) of the access mvp's avoid using lookup fields in tables due to
the problems they cause in building the forms etc for the database.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[lizo.consulting]

I did have a the field in the table looking up the list in a separate
table (put it there trying to get the combo to reject non-matches when
a batch of records is pasted into the subform). Even after I removed
that, the subform still behaved the same way (could paste in records
into the combo box that were not matched in the list table.

I did find a temporary workaround by adding a relationship that
enforces referential integrity between the data table and the list
table. Now any record that doesn't match in the combo box throws an
error, and the offending records are put into a paste errors table.

Now for some code that might do it a bit more gracefully...

Liz

 

[Jeanette Cunningham]

What error message are you getting?

You can try this code on the form's error event to catch the error and
present a more pleasant message.

If Dataerr = 2237 Then
MsgBox = "Select an item from the list, or press Esc to cancel"
Response = acDataErrContinue
Me.NameOfCombo.Undo
End If


Replace the obvious with the name of your combo.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


I did have a the field in the table looking up the list in a separate
table (put it there trying to get the combo to reject non-matches when
a batch of records is pasted into the subform). Even after I removed
that, the subform still behaved the same way (could paste in records
into the combo box that were not matched in the list table.

I did find a temporary workaround by adding a relationship that
enforces referential integrity between the data table and the list
table. Now any record that doesn't match in the combo box throws an
error, and the offending records are put into a paste errors table.

Now for some code that might do it a bit more gracefully...

Liz

 

[lizo.consulting]

It's throwing 3201 -- your code was very helpful thanks. Just getting
into error trapping.

Liz

 

[Jeanette Cunningham]

So . . . adding a table with referential integrity for the combo has made
the mix more complicated. What happens if you add code to ignore error 3201?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

It's throwing 3201 -- your code was very helpful thanks. Just getting
into error trapping.

Liz