Saving items in listbox

[ScubaSteve]

I found this in a previous thread and would like to implement this into
my database. I get the premise, but am not sure how to proceed with
it. I need to allow users to click on items in a listbox and add them
to a table that the second listbox queries to display it's value.
These items should be saved per recordkey. Here is the quote:
"
1) Use a temporary table as the rowsource for the second listbox, as
the
bosses "add" items to the listbox actually add them to the temporary
table
and then requery the listbox

2) Use a userdefined callback function for the rowsourcetype of the
listbox.
You can then maintain an array which contains the items added to the
list
and the callback function uses the array to fill the listbox (look up
RowSourceType in help and this will lead you to the function definition
and
examples of it's use)
"

The first one makes more sense to me, how to proceed with it? Thanks!

 

[Jeff Boyce]

If your end result will be a way to keep track of the selected items, why
bother with a temporary table?

Another approach would be to have a record added to a permanent table after
each item (in first listbox) is selected. After each addition, requery the
second listbox, which would be based on a query of that permanent table, and
(probably) of the control on this form that show "for whom" the item has
been selected.

Or have I misunderstood?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[ScubaSteve]

I agree, I believe a permanent table would make more sense. Clicking
the items (multiselect) sounds reasonable as well; however, the listbox
is actually based upon a parameter coming from a combobox. So a user
chooses from 1 of 11 categories (parameter) and the listbox displays
the items matched with the cbo parameter. Would selecting items still
work as users might be using more than one parameter in the cbo? What
I wanted to do was have the items move over to the second list on
DblClick, and then populate a table. (I don't know how to do that)

I'm still not sure how that would work though when a user views the
record form -- would the 2nd list pull up that data? Which is why I
thought it would make more sense to OnDblClick of the first list, for
the item to move to the table and then have the second list be
requery'ed and display that data (for viewing purposes or removal if
necessary). And I don't know how to do that either! eek,

 

[Jeff Boyce]

Not sure I see a question ...

Generically, and in another context, I've done the following:

* create a form, add a combo box in the header, and use the AfterUpdate of
the combo box to load the form with the "main table" data for the item
(person, case, whatever) I selected in the combo box.
* add a listbox to the form, the source of which is based on whatever is
"available". In your situation, it sounds like this might be based on a
second combo box for 1-11 categories. Again, use the AfterUpdate of that
second combo box to cause a requery, this time for the listbox.
* add a second listbox to the form to display those items selected for
(and to be associated with) the choice from the first/top combo box (i.e.,
which item(s) belong with which person, case, whatever).
* add a table that holds PersonID (the top combo box), ItemID (the
"selected" item) and whatever else is appropriate.
* on the form, add a command button ... when clicked, it appears to "push"
an item selected in the left-hand (first) listbox over into the right-hand
(second) listbox. Behind the button, code is adding a record to the table,
based on the first combo box (?PersonID) and the "available" item selected
(first listbox). Then the command button requeries the second listbox so it
can show the new addition.
* since folks make mistakes, add another command button that appears to
"push" back an already-selected item. Behind the button, you are deleted
the record and requerying the second listbox.

Hopefully that is enough to get you started...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[ScubaSteve]

Thanks for your response.

What I have is a main patient form (frmpatient) with a combobox and
then a listbox that updates according to the combobox.

What I've decided might be easier is to have a subform (frmProblems)
with a combobox in datasheet view so that I can see the multiple
records in one page.

I understand the concept of what to do, I just am not sure how to do it
- and I've tried to figure it out, but coding is not my forte.

When I dblclick on an item in the listbox (or use a command button), I
want a new record to be created with what was selected in a table
(tpatientdiagnoses) so that the subform cbo in datasheet view shows it
(per record key). How does one go about creating a new record in a
subform (based on a table that details case id: patientid:
diagnosisID).

Using the wizard for creating a "new record" button doesn't fit the
bill

Make sense at all?

 

[Jeff Boyce]

A standard main-form/subform construction uses the subform control's (in the
main form) Parent and Child properties to tell the subform which records to
show. Change the record showing in the main-form, and the subform should
follow, if you've used these properties to tell Access how they are
connected.

If the form you are using as a subform allows new records to be added, then
when you use it as a subform in a main-form, it will allow new records to be
added (and will automagically insert the correct "Parent" ID).

Regards

Jeff Boyce
Microsoft Office/Access MVP