468,514 Members | 1,427 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,514 developers. It's quick & easy.

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

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
Nov 13 '05 #1
3 2023
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

Nov 13 '05 #2
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.

Nov 13 '05 #3
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
....
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Brian Wolfe | last post: by
5 posts views Thread by jiangyh | last post: by
2 posts views Thread by msnews.microsoft.com | last post: by
3 posts views Thread by Alfredo Barrientos | last post: by
1 post views Thread by Tom C | last post: by
2 posts views Thread by Gernot Frisch | last post: by
reply views Thread by Deep | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.