elegant solution to many-to-many table/form problem

[Adriaan van Heerden]

hi,

once again apologies if I'm covering old ground. I've looked through
lots of previous posts and tried some code but nothing works as yet.
However, the problem must be a very common one so I'm slightly
surprised there isn't more on this (unless I'm missing something -- if
so please just point me in the right direction), and the textbooks are
also strangely silent.

I need to do the following:
* be able to associate any combination of a set of problems with a
list of patients (hence many-to-many relationship with junction table)
-- the list of problems is added to regularly so using checkboxes is
out of the question
* the solution that appeals to me is the ability to transfer possible
problems between listboxes with >, <, >> & << buttons and then write
the selected problems to the junction table.

So far I've got three tables: Patients, Problems and PatientProblems,
but I need a lot of help implementing the second part of the solution.
Also, if anyone knows of a more elegant way of handling this kind of
situation I'd really like to hear it.

many thanks
Adriaan

[PC Datasheet]

If what you have for problems lends itself, you need another table,
TblProblemType.

For data entry you need a main form based on TblPatient or a query based on
TblPatient with a way to select the patient record. The subform would be based
on TblPatientProblem. The linkmaster/linkchild properties would be PatientID. In
the subform you would have synchronized comboboxes for first selecting the
problem type and then selecting the problem.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Adriaan van Heerden" <ad*****@postmaster.co.uk> wrote in message
news:89**************************@posting.google.c om...

hi,

once again apologies if I'm covering old ground. I've looked through
lots of previous posts and tried some code but nothing works as yet.
However, the problem must be a very common one so I'm slightly
surprised there isn't more on this (unless I'm missing something -- if
so please just point me in the right direction), and the textbooks are
also strangely silent.

I need to do the following:
* be able to associate any combination of a set of problems with a
list of patients (hence many-to-many relationship with junction table)
-- the list of problems is added to regularly so using checkboxes is
out of the question
* the solution that appeals to me is the ability to transfer possible
problems between listboxes with >, <, >> & << buttons and then write
the selected problems to the junction table.

So far I've got three tables: Patients, Problems and PatientProblems,
but I need a lot of help implementing the second part of the solution.
Also, if anyone knows of a more elegant way of handling this kind of
situation I'd really like to hear it.

many thanks
Adriaan

[Bruce Dodds]

Adriaan van Heerden wrote:

hi,

once again apologies if I'm covering old ground. I've looked through
lots of previous posts and tried some code but nothing works as yet.
However, the problem must be a very common one so I'm slightly
surprised there isn't more on this (unless I'm missing something -- if
so please just point me in the right direction), and the textbooks are
also strangely silent.

I need to do the following:
* be able to associate any combination of a set of problems with a
list of patients (hence many-to-many relationship with junction table)
-- the list of problems is added to regularly so using checkboxes is
out of the question
* the solution that appeals to me is the ability to transfer possible
problems between listboxes with >, <, >> & << buttons and then write
the selected problems to the junction table.

So far I've got three tables: Patients, Problems and PatientProblems,
but I need a lot of help implementing the second part of the solution.
Also, if anyone knows of a more elegant way of handling this kind of
situation I'd really like to hear it.

many thanks
Adriaan

The Access 2000 Developer's Handbook's (Desktop Edition) Chapter 7
contains an example of exactly what you're looking for. The section is
called "Making Multiple selections in a List Box".

I don't have the A2002 version of the book, but if that's what you're
using and you don't have the book, it's well worth buying.

[Adriaan van Heerden]

thanks Bruce. I won't be able to buy the book for a while (limited
finances, say no more) but I found some code in this newsgroup which
seems to derive from the book you mention. I've got the listboxes to
work (if anyone wants to know drop me a line) and I've also got a
"Save Problems" button which executes the following code
("PatientHospNo" is the textbox holding the Patient ID):
************************************************** ***********
Private Sub btnSaveProblems_Click()
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Forms!frmEpisodes
Set ctl = frm!lstSelected

For Each varItem In ctl.ItemsSelected
strSQL = "INSERT INTO tblPatientProblem(Patient, Problem)
VALUES (" & frm.PatientHospNo.Value & ", " & ctl.ItemData(varItem) &
")"

CurrentDb.Execute strSQL, dbFailOnError
Next varItem

End Sub
************************************************** ***********
Unfortunately this still doesn't work -- the debugger stops at "For
Each varItem In ctl.ItemsSelected" and nothing is written to the
junction table (tblPatientProblem). I'm still trying to figure out why
....