Okay...been racking my brain with this scenario for a couple of days and just not getting it. I need some help. I'll try to explain as best possible.
Using this page as a starter :https://support.microsoft.com/en-us/kb/827423
I have a form (frmBehaviorsMultiple) with a three list boxes:
NamesList (simple - multi select)
NamesList Control source: SELECT tblStudent.studentID, tblStudent.studentFN, tblStudent.studentLN, tblStudent.studentClassID,
tblStudent.studentPoints FROM tblStudent ORDER BY tblStudent.[studentFN];)
SELECT tblStudent.*, tblClass.ClassID
FROM tblClass INNER JOIN tblStudent ON tblClass.ClassID = tblStudent.studentClassID;
Thus far when I select items from the NamesList and press testMultiselect button the studnetIDs populate in the mySelection text box delimited by comma's. So this is fine. But what I need to do is a couple of things. First here are the relationships...
on an item from either the lstBehaviors list box or lstNegBehaviors llist box I need to (1) write the new behavior, date, and point to the student behaviors table then I need to add the point for that behavior to the overall studentPoints field in the Student table.
I can do this for one record as shown below:
Private Sub lstBehaviors_Click()
Dim rowIndex As Integer
Dim behaviorValue As String
Dim pointValue As Integer
Dim rowIsSelected As Integer
Dim tempPoints As Integer
Dim pointCalc As Integer
Dim sign As String
Dim SoundFile As String
' ListBox row index clicked
rowIndex = Me.lstBehaviors.ListIndex
' Row value clicked
behaviorValue = Me.lstBehaviors.Column(1)
pointValue = Nz(Me.lstBehaviors.Column(3), 0)
If (pointValue > 0) Then
sign = "+"
Dim sql As String
sql = "INSERT INTO tblStudentBehaviors ([stdID], [stdBehavior], [stdBehaviorDate], [stdBehaviorPoint]) VALUES (" & Me.studentID & ", '" & behaviorValue & "', Date(), " & pointValue & ");"
'tempPoints = Nz(DLookup("studentPoints", "tblStudent", "studentID = 'me.studentID'"), 0)
tempPoints = Nz(Me.studentPoints, 0)
pointCalc = tempPoints + pointValue
'I can add points to the students overal point count by simply adding point to the textfield bound to the studentPoints field but this is not possile on a multiselect
Me.studentPoints = pointCalc
If pointValue > 0 Then
If Not IsNull(DLookup("tblSettingsPositiveWav", "tblSettings")) Then
SoundFile = Nz(DLookup("tblSettingsPositiveWav", "tblSettings"), "")
If Not IsNull(DLookup("tblSettingsNegativeWav", "tblSettings")) Then
SoundFile = Nz(DLookup("tblSettingsNegativeWav", "tblSettings"), "")
DoCmd.OpenForm "frmPointAwarded", , , "studentID = '" & Me.studentID.Value & "'", OpenArgs:=sign & " " & pointValue & " " & behaviorValue
DoCmd.Close acForm, "frmBehaviors"
The struggle I am having is trying loop through each selected item in the multisection list box and parse out the studentID, behavior, and points to add a new record to the studentBahaviors table and update the overall points to the studentPoints field on the student record.
Any assistance would be most appreciated!