multiselect listbox added to existing form

Originally Posted by MrBullwinkle

If you are asking how to store the data for patients then:

In the demographics table, you would have PatientID, Age, Gender, (Anything that has only 1 per patient...address, phone number)
For medications, since you can have more then 1 per patient, you need a second table that is linked by the PatientID.

In a table called Medications you have a field(column) called PatientID, Medication, and an autonumber field to use as a primary key

The two tables are linked by a 1-to-many relationship using the PatientIDs of both tables.

Originally Posted by lwells

A list box that is set to multiselect can not be bound to the forms underlying recordset. You will either need to make a table listing the medications available and use that as the rowsource or use a value list. But as MrBullwinkle suggested, have a table called medications that is linked to your patient ID. Then with code you can use the unbound multiselect list box to add the items selected to that table. You can show what medications have been given to a patient by using another listbox with the record source to that table and filtered by the patient ID. There isn't anyway to make one list box to function for both. A multiselect list box either in Simple or Extended has no value and is a read only.


lwells

Originally Posted by lwells

You could, but the question is why? If you use vba to create a table, then the code can only be used once..or you would have to delete the table when you are finished running the code to reuse the same code again. Creating and deleting tables would cause tremendous database bloat and possible corruption.

There appears to be a reluctance to just create a simple table to hold what the medications are for each patient which I am not following here. A simple table, a short piece of code to add records from a multiselect list box...is pretty straight forward database design and application. Is there something else about this application that is preventing you from doing this?

Just curious,
lwells

Originally Posted by lwells

tia,

Okay, here is what you do.

1) Create a table and name it tblMedicationsGiven. Give the table three fields. The first field will be the Primary Key and Autonumber. The second field will be PatientID and will be a number. The third field will be Medications and will be a text.

2) In table relationships window join the PatientID from your demographics table to this table with the PatientID. This will give you the one-to-many relationship between patient and medications given.

3) On your Demographics form, place two unbound listboxes. Name the first unbound list box lstMedicationsGiven, and the second list box name it lstMedications.

For the rowsource of the lstMedications - this is where you will select the available medications from. So you can either choose to create a seperate table listing all the available medications or use the value list type and make the list yourself as the rowsource.

4) On the forms On Current Event place the following code:

Dim intID As Integer
Dim strSQL As String
intID = PatientID

strSQL = "SELECT tblMedicationsGiven.* " & _
"FROM tblMedicationsGiven " & _
"WHERE tblMedicationsGiven.PatientID =" & intID & ";"
lstMedicationsGiven.RowSource = strSQL
Me.Refresh

This will keep the list box named lstMedicationsGiven in sync with each patient record.
So as you scroll through each patients records the list box will display what medications were given to that patient only.

5) On the After Update event of the listbox named lstMedications place this code:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim varItem As Variant
Dim strSQL As String
Dim intID As Integer
intID = PatientID
Set db = CurrentDb()
strSQL = "SELECT tblMedicationsGiven.* FROM tblMedicationsGiven;"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
For Each varItem In Me!lstMedications.ItemsSelected
If rs.RecordCount > 0 Then
rs.MoveLast
End If
With rs
.AddNew
rs!PatientID.Value = intID
rs!Medications.Value = Me!lstMedications.ItemData(varItem)
.Update
End With
Next varItem
Set db = Nothing
Set rs = Nothing
lstMedicationsGiven.Requery

Make sure this list box (lstMedications) is set to either multiselect Simple or Extended.

Now as the user selects medications from this list, it will add those medications to the records for that patient and automatically refresh the listbox lstMedicationsGiven so the user can see what was given to the patient.

I believe this is what you were wanting to do.

lwells