Add multiple records using a list box

[SteveG]

Hi. I'm trying to create a form in Access 2003 to enter data about
the kinds of assistance patients need -- dressing, washing and so on.
Patients will typically need assistance with more than one thing.

I've got a tblPatients, with PatientID as the primary key, a lookup
tblAssistanceType, with AssistanceTypeID as the primary key and a text
field for the type of assistance, and tblPatientsNeeds, which stores
PatientID and AssistanceTypeID, with one row for each type of
assistance a patient needs, on the lines of Customer, Order and
OrderDetails. First question -- should I be doing it this way?

Second queston. Assuming I should be doing it this way, if I put an
unbound list box that allows multiple selections, populated with a
query selecting the records in tblAssistanceType, on a form, bound to
the main form on PatientID, how can I get my multiple selection into
tblPatientsNeeds all in one go?

Can I somehow write an append query that runs when I click a button,
takes the AssistanceTypeIDs off the list and puts them into
tblPatientsNeeds? Or is there a better way of doing it? I'm afraid
I don't know much about Visual Basic.

 

[Pat Garard]

G'Day Steve,

The Key to this:

Patients will typically need assistance with more than one thing.

One patient will have many needs.

One need will be met for many patients.

For Tables, you need something like:

Patients PatientsNeeds NeedTypes

*PatientID-----*PatientID
Name *NeedID-----------*NeedID
: DateNeedMet NeedDescription
: : :
: etc etc
etc

"*" indicates Primary key (BOTH fields in PatientsNeeds)

If a given need may be "ministered" more that once, ie if a Need can
appear multiple times, then include the DateNeedMet in the Primary
Key.

PatientsNeeds will represent a subform on the Patients Form.
NeedID on the subform might be a look-up to
[NeedTypes]![NeedDescription].

 

[Jeff Boyce]

In addition to the method Pat offers, there's another possibility...

If you've used the New Query Wizard in Access, you've seen "paired
listboxes" in action. The left box holds possibilities, the right box holds
your selection(s). If you are building this application for a novice user,
the paired listbox approach may be a bit more intuitive than a
main-form/sub-form, primarily because the user can see what has been
selected and what still is.

However, building the easier-to-use interface is more work for YOU!

"Easy ... is HARD!"

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


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

Microsoft Registered Partner
https://partner.microsoft.com/

 

[SteveG]

Thanks, both. I fear I really do need the paired listbox solution, even
though it's more work; my users will, I think, need the more intuitive
interface. I've had a quick look and found Rebecca Riordan's articles
on the subject at

http://msdn2.microsoft.com/en-us/library/aa167285(office.11).aspx

Any other places people would recommend I look for advice on how to
build them?

 

[SteveG]

I've had a quick look at Rebecca's first article and the examples, and
I think I see how it works. One immediate question, if anyone can
help.

In the examples, literal strings are moved from one listbox to another.
Is there a way -- preferably an easy one for a doofus like me to
understand -- to make these work like multi-column combo and list
boxes, in that the user sees and selects the string, ItemDescription,
but what actually gets selected and stored is the Long Integer, ItemID?

 

[Jeff Boyce]

Steve

Even though a list box (or a combobox) displays a text/literal string, if
the ID field is the first in the source, and the column width is zero (and
the bound column is the first), you see the string, but the box's value is
actually the ID, just like you want.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


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

Microsoft Registered Partner
https://partner.microsoft.com/

 

[SteveG]

Thanks -- I've taken a closer look at the forms in Rebecca's example
database and now I understand (told you I was a doofus!). The
examples just use the one text field, but I see from examining the list
boxes' property sheets exactly what you mean.


Now I _think_ I know what I'm doing (famous last words!)