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

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
Join Bytes!
www.pcdatasheet.com


"Adriaan van Heerden" <adriaan@postmaster.co.uk> wrote in message
news:890aefb8.0408020616.5e52dda6@posting.google.c om...[color=blue]
> 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[/color]

Adriaan van Heerden wrote:[color=blue]
> 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[/color]

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.

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
....