Problem creating form that selects tables and fields

[Keith]

Hello

I’ve been working on this problem for a long time and, so far, each solution
runs into a roadblock that stops everything. I would appreciate any
suggestions. I’m trying to build a form that allows the user to pick a
table (in this mdb file) , and within that table, select several fields. It
could look something like those Microsoft wizards where there is a list box
on the left, and the user uses pointer controls identified with “>†and “<â€
to move variables from the box on the left to the box on the right. It could
also be something very different.

In my latest effort, I used two subforms in a form. In subform 1, I used VB
A to load and display a table with a list of tables. The display incudes a
field where the user would put an “x†an then VBA would be used to make
another table that contained all the fields in the selected table. That part
seems to work. But then we go to subform 2 where the user would put an “x’
beside each field selected. This second step has problems because subform 2
locks out the source table and the user can’t put in an “Xâ€. It's because I
need to use the VBA to update the table used in subform 2 each time the user
in changes something in subform 1. If I change the subform from dynaset
connecton to snapshot, the user can’t enter an x.

So I’m looking for a workable solution. How can I build this series of two
pickers quickly and efficiently. Is there a Microsoft tool that does the
work of the pickers we see in the wizards? Is there a better way? I once
saw a picker with radio-buttons and check boxes in the table display. How
might I do that here? All suggestions are welcome.

Thank you.

Keith

 

[Jeff Boyce]

Keith

I've created "picker"-type designs by using two list boxes, a series of
command buttons, and an underlying table that holds the "picked" items.

The generic approach is to first list the available items in the left
listbox. I'll typically allow only a single selection (i.e., turn off
'multi-select'). One of the command buttons has code to "move this
selection to the 'picked' list". To do that, the code has to iterate
through the listbox, finding the ItemSelected (and you could use
multi-select and the same approach). This works using an Insert SQL
statement, adding the item to the underlying table of picked items.

Another command button has code to "move ALL items to the 'picked' list".
This works using an Insert SQL statement.

A third command button allows for the selection of a 'picked' item and
"moving" it back to the listbox of available items.

The fourth removes all 'picked' items, sending them back.

To keep the display current, I requery both listboxes at the end of each
button-click's code.

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Keith]

Hi Jeff,
Thanks very much. That's helpful advice. It's an approach I haven't yet
tried, and I'll go to work on it.
Have a great new year...

keith