Listbox items into table rows?

[Mr Smin]

I am trying to make a flexible checklist (of things which have to be done to discharge a patient from hospital).

My preferred interface is a multi-select listbox - the user selects items which have been done and then clicks a button to confirm. I'm open to other suggestions.

My intention is to use a junction table to store the 'done' items matched to a given patient discharge.

I currently have 32 checklist items stored as rows in a table and I really want to avoid a 32 field table - especially as the list could get changed! Also, as rows I can track each checklist item's date, time and who checked it off.

Please can someone tell me how to turn multi-select listbox items into rows? I have searched around but I can only find examples for making selections into queries. (I found some likely examples on here but my locked-down company PC refuses to open older Access version files!).

Thanks in advance.


TableChecklist
ID (pk)
ChecklistItem

TableDischarge
ID (pk)
Date
PatientName (etc)

TableJunction
ID (pk)
TableDischargeID
TableChecklistID
DateStamp
TimeStamp
UserStamp

Smin.

 

[DCrake]

The easiest way to do this is to use the ItemSelected() event for the list listbox.

For Each varItem = Ctrl.ItemSelected()

Run an append query here to append the neccessary info into your table here.

Next

BTW If you are a developer then your IT department should allow you the neccessary privilages to open earlier versions of Access. Whould you expect your mechanic to mend your car if he did not have the right tools?

David

 

[Mr Smin]

DCrake, thanks for the help. It's working now.
The wheels of the IT dept grind slowly here, so no additional priviledges for the forseeable future.

 

[misaloo]

Can you please post the code you used to make this work, or help me with what I have below? I am trying to modify the code to also insert the primary key (TrainingSessionID) of the new form record into the TrainingSession_Classes table but I can't get the syntax right.

I assume I would need to have a text box holding the TrainingSessionID on the form, and somehow declare the control and add it to the strSQL Values list as Me.TrainingSessionID (or whatever the name of my text box is). Any help on the syntax and code for this would be appreciated.

'******************** Code Start ************************
Dim ctrl As Control
Dim varItem As Variant
Dim strSQL As String
Dim db as DAO. Database

Set db=CurrentDb
Set ctrl = Me.ClassesListBox

For Each varItem In ctl.ItemsSelected
strSQL = "INSERT INTO TrainingSession_Classes (TrainingSessionClassID) VALUES '" &
ctrl.ItemData(varItem) & "'"
db.Execute strSQL, dbFailOnError
Next varItem


End Sub
'******************** Code end ************************

 

[Mr Smin]

Keep in mind I'm no specialist so although the following works, it may not be elegant, and I apologise for some confusing naming of objects.

The following code is attached to a button called btnOK which is in a subform alongside the listbox List0. This is populated from table tblChecklist.
The parent form contains among other things the DischargeID field.

Probably the important bit is between the Setwarnings lines.

ps I have a feeling I'm supposed to put the variable declarations inside the sub to keep things tidy.

Option Compare Database
Option Explicit
Dim intItem As Integer
Dim intParentID As Integer
Dim varItem As Variant
Dim strSQL As String
Dim strDischargeID As String
 
Private Sub btnOK_Click()
intParentID = Me.Parent!ID
'SetWarnings otherwise you have to click to confirm every single selected item
DoCmd.SetWarnings False
For Each varItem In Me!List0.ItemsSelected()
intItem = Me!List0.ItemData(varItem)
strSQL = "INSERT INTO tblPlanChecklist (ChecklistID,DischargeID)" & _
        "VALUES ('" & intItem & "','" & intParentID & "');"
DoCmd.RunSQL strSQL
Next varItem
'SetWarnings on again otherwise will affect whole application
DoCmd.SetWarnings True
End Sub