Split choice control (or other method to implement this?)

[Grahammer]

I have the followin relationships in my table...

Province 1->M Cities 1->M Centres
Province 1->M Centres

.... One country can have many Cities. Once City can have many Centres. The
Centres that are available in a City is limited by the Country.

Ontario -> Toronto -> East York
Ontario -> Toronto -> Scarborough
Ontario -> Toronto -> Mississauga
Alberta -> Calgary -> Downtown
Alberta -> Calgary -> Rundle

...and..

Ontario -> East York
Ontario -> York
Ontario -> Etobicoke
Ontario -> Scarborough
Ontario -> Missisauga
Alberta -> Downtown
Alberta -> Rundle
Alberta -> Woodbine
Alberta -> Country Hills

.... You'll notice in the top secition that not all of the Ontario Centres
did not appear for Toronto. Ditton for the Alberta Centres.

I'm trying to find a simple MS Access form (it's a child form placed on the
Cities form) to implement the ability to add and remove Centres from the
Cities, based on what Centres are available in the Province. I'm trying to
use as little VBA as possible.

I considered two list boxes, with and "Add->" and "<-Remove" button, similar
to what is found in many Windows apps, but the list boxes require different
queries, and I can't figure out how to limit their results based on the
parent form.

Is this description clear? Can someone suggest how this can be done?

 

[Michel Walsh]

Hi,


A relatively simple way to implement a Add-> <-Remove between two lists is
to first start with a temporary table with all the records (to be displayed)
and an extra field, a Boolean field. That Boolean field is not to be
displayed in the list box, but the first list box get its row source equal
to: SELECT ... FROM temp WHERE booleanField; the second list box get its
row source equal to: SELECT ... FROM temp WHERE NOT booleanField; You
start the process with: UPDATE temp SET booleanField=True; and requery
both lists.

On a click on the Add-> button, read the values in a loop, and update the
Boolean field of the record to False. Requery both lists at the end. On a
click on the <-Remove button, do the same, this time, updating the records
corresponding to the selected values to True.


Dim var As Variant
With Me.AppropriateListBoxControl
For Each var in .SelectedItems
CurrentDb.Execute "UPDATE temp SET booleanField= ... " & _
" WHERE pk=" & .ItemData(varItem)
Next var
.RowSource = .RowSource
End With


Hoping it may help,
Vanderghast, Access MVP

 

[Roger Carlson]

On my website (see sig below) I have two small sample databases called
"MoveList1.mdb" and "MoveList2.mdb" which illustrate this.