Multivalue combo

[CJ]

Hi Groupies

I am using A2007

I have a multivalue combobox that lists our vehicles available for jobs. If
we use an external trucking company, I want to be sure that none of our
vehicles are selected. In previous versions I would have had no problem
using code (below) to display the field when required and clear and hide it
when not required:

Private Sub ysnCompanyVehicle_BeforeUpdate(Cancel As Integer)

If Me.ysnCompanyVehicle = True Then
Me.lngUnitID.Visible = True
Else
Me.lngUnitID = Null
Me.lngUnitID.Visible = False
End If

End Sub

The code works fine to open the combo so that I can select vehicles. The
problem is if I change my mind and decide that I do not want to use our
vehicles. With this multivalued combo, the line Me.lngUnitID=Null, causes my
check box to freeze. Basically, the system can not just remove or uncheck
all of the vehicles I might have selected. I would have to manually deselect
the vehicles from the combo and then I can deselect the check box for
CompanyVehicle

Has anybody figured out how to work around this?

 

[Allen Browne]

CJ, the best solution might be to avoid using the multi-value field.

They might help newbies who have no idea how to create a related table to
handle a one-to-many relationship, but they have serious drawbacks for
anyone who knows what they are doing. The relational structure beneath them
is not exposed. They are messy to query, and require you to adapt your
coding to handling recordsets within recordsets. Looping through the
selected values in a multi-value combo and changing the values is not
trivial. And once you get all that sorted out, Access doesn't maintain the
OldValue of a multi-select combo correctly, so you're still stuck with
convoluted workaround when it comes to responding to a change or an undo.

IMHO, you would be much better served by creating a related table to store
the vehicles for the job(?) It is then quite straightforward to do what you
need. A small subform to handle the vehicles (one per row) is a much more
flexible interface.

 

[CJ]

Thanks Allen. I should have figured.......the whole "too good to be true"
adage.