Solved

Update student record from multiple select list box (Access 2010)

Posted on 2016-08-16
12
49 Views
Last Modified: 2016-08-21
Dear Experts,

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];)

lstBehaviors
lstNegBehaviors

Control source:
Control Source
Sql:
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...
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 = "+"
  End If
  
    Dim sql As String
    sql = "INSERT INTO tblStudentBehaviors ([stdID], [stdBehavior], [stdBehaviorDate], [stdBehaviorPoint]) VALUES (" & Me.studentID & ", '" & behaviorValue & "', Date(), " & pointValue & ");"
    CurrentDb.Execute sql
    
    '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"), "")
         API_PlaySound SoundFile
       End If
    Else
        If Not IsNull(DLookup("tblSettingsNegativeWav", "tblSettings")) Then
         SoundFile = Nz(DLookup("tblSettingsNegativeWav", "tblSettings"), "")
         API_PlaySound SoundFile
        End If
    End If
    
    
     DoCmd.OpenForm "frmPointAwarded", , , "studentID = '" & Me.studentID.Value & "'", OpenArgs:=sign & " " & pointValue & " " & behaviorValue
     DoCmd.Close acForm, "frmBehaviors"
End Sub

Open in new window


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!

Thanks
0
Comment
Question by:shogun5
  • 6
  • 4
  • 2
12 Comments
 
LVL 34

Expert Comment

by:PatHartman
ID: 41759609
StudentPoints does not belong in tblStudent.  Whenever you need a total of the points for a student, use a query that sums the value in tblStudentBehaviors.  Storing a calculated value violates second normal form and can create what are called "update anomolies".  I.e. differences between the stored calculated value and the live calculated value.  Once the calculated value is stored, it becomes immediately invalid if any change is made to tblStudentBehaviors.  

Although I approve of naming standards in general, you are using something I discarded 20 years ago when I first moved to using Access.  Back in the late 60's when I learned to program in COBOL, using prefixes for variable and column names was the accepted standard.  Once I moved to Access and VBA in the 90's I realized that using prefixes was no longer necessary and also interfered with Intellisense.  Having to type seven letters (student) before getting to the meat of a name dramatically increases the characters you have to type prior to getting any help from intellisense.  The prefixes also get in the way when you open tables and queries to view data since all you see is the prefix so you have to constantly adjust column widths to make them wide enough for you to see some meaningful part of the name.  In your case, you have opted to go with spelling out a long prefix and then abbreviating the actual column name.  Logic says you should do the reverse.  If you like having each column name tagged with the name of the table in which it appears, a suffix makes more sense.  It serves the same purpose - uniquely identifying a  column name and relating it to a table - without getting in the way.

So for example - LastName_Stu provides a more meaningful name for the data while still keeping the table affiliation.  The suffix also allows you to distinguish the name of FK's without losing their original PK name.  So - StudentID_STU is the primary key in tblStudent and StudentID_BEH is the FK in tblStudentBehavior.
1
 

Author Comment

by:shogun5
ID: 41760279
Pat,
Thanks for the great comments! Always helpful to get nice insights from an expert.
0
 

Author Comment

by:shogun5
ID: 41760280
Pat,

Any thoughts on how I may solve my question above?
0
 
LVL 9
ID: 41760317
I respect Pat a lot, but we disagree on database naming standards.  In our shop we prefix all field names with the primary table name, or if it's very long, a reasonable abbreviation.  Sure, it adds a bit more time to the development, but most of the life of a database is in using and understanding it, not coding it.  Initial investment, long term payoff.

I do agree with Pat that you shouldn't have Points in the student table - that value can be derived with a query of behaviors and their point values, or a little function, say GetStudentPoints, that computes the total.

Regarding your actual question, I'm wondering why you're using a ListBox at all.  With a Listbox, not only do you have to catch all the changes to it to add/remove records from the StudentBehavior table, you also have to set them initially when your form loads in order to show the current records.  Also, if your list of possible Behaviors is long, a list box will get very unwieldy - you won't be able to see all the current Behaviors.

Instead, I recommend using a subform to show the Behaviors related to this Student.  Then you can just add or delete them as you like.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41760337
@Armen - a suffix gives you the comfort of knowing the table while not interfering with intellisense or narrow column widths in a DS form or query.

@shogun5 - Solving the problem really depends on where you need the summary information.  If you need it in a form that you will use to update the student table, then you can either use DSum() to summarize the behavior number or add a subform to the main form and either show the details of behavior or just show the total.  If you need the total in a report or a query (not bound to an editable form), then create a totals query that sums the behavior points by student.  Join to this query.
0
 

Author Comment

by:shogun5
ID: 41760340
Armen,

Thanks for commenting! I thought about making a compromise with naming conventions and just abbreviating the names so at least I know if studentID is a PK or FK. I am removing the points field in the student table and will simply create a query to sum up the points when I want to display.

To your thoughts about the list box...I am not married to the list box and am willing to try other ideas. I just need a way to give a group of students the same behavior. How would a subform allow me select the students that are to be awarded the behavior and then select the behavior to award?
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:shogun5
ID: 41760344
Pat,

Thanks again for the comments. I do appreciate you taking the time to comment. So basically, I just need (in a form) to 'check' a group of students (can to create a tabular/continuous form) and check the students that I want to award a specific behavior, today's date, and behavior points, and then select the behavior (perhaps from a list box) and have the the onclick event of this box add behavior records to all students that were checked on the form? Would that work?
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 250 total points
ID: 41761237
I don't have any objection to the multi-select listbox for this purpose.  It does make it easy to add a behavior to several people with minimum clicks.  However, you still need the subform for maintenance.  I've attached a picture of a form I use for bulk updates and maintenance.  It is for a utility reseller.  The form is used to create a forecast and projects the number of meters added per month for a particular utility.  There are four filters and four update options.  The filters are used to choose what set of data the bulk operation will apply to and the button applies the update.  For example, if the analyst wants to increase the meter count in future months, he selects the utility, the rate group, and the date range.  Then he puts the new number into the New Meter Count field and presses the apply bulk update button.  That changes the value from 734 to the new value for the dates selected.  If he wants to change one month, he just types into the Meter Count field for the month he wants to change.  He might do this if he knows we have a bulk client coming on line next month and we need to adjust our short term energy buys to accommodate the new subscribers.  The add button works similarly but instead of updating existing records, it creates new ones.  The Add new month to all, takes the last forecast month for every utility and rate group, adds 1 to the month and inserts a new record.  We have 38 combinations of utility and Rate Group so this process always adds 38 records.BulkUpdate.JPG
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41761242
Here's the code for the form.
Option Compare Database
Option Explicit

Private Sub cmdAdd_Click()
    Dim db      As DAO.Database
    Dim td      As DAO.TableDef
    Dim rs      As DAO.Recordset
    Dim qd      As DAO.QueryDef
    Dim rsDates As DAO.Recordset
    
    Dim vForecastDate As Date
    
On Error GoTo Err_Proc

    If Me.cboUtilityGroup & "" = "" Or Me.cboRateGroup & "" = "" Or Me.cboFrom & "" = "" Or Me.cboThru = "" Then
        MsgBox "Utility Group, Rate Group and Date Range are required.", vbOKOnly
            MsgBox "Update cancelled.", vbOKOnly
            Exit Sub
    End If

    Set db = CurrentDb
    Set td = db.TableDefs!tblNewMeterForecastByMonth
    Set rs = td.OpenRecordset
    Set qd = db.QueryDefs!qListOfForecastMonthsForBulkAdd
        qd.Parameters!FromMonth = Me.cboFrom
        qd.Parameters!ThruMonth = Me.cboThru
    Set rsDates = qd.OpenRecordset
    Do Until rsDates.EOF
        vForecastDate = DateSerial(Left(rsDates!ForecastMonth, 4), Right(rsDates!ForecastMonth, 2) + 1, 0)
        rs.AddNew
            rs!FPNAForecastUtilityAbrev = Me.cboUtilityGroup
            rs!RateGroup = Me.cboRateGroup
            rs!ForecastDate = vForecastDate
            rs!ForecastMonth = rsDates!ForecastMonth
            rs!MeterCount = Me.txtBulkMeterCountAdd
            rs!MeterCountPerDay = Me.txtBulkMeterCountAdd / Day(vForecastDate)
        rs.Update
        rsDates.MoveNext
    Loop
    Me.sfrmNewMeterForecastByMonth.Requery
Exit_Proc:
    Exit Sub
Err_Proc:
    Select Case Err.Number
        Case 3022
            Resume Next
        Case Else
            MsgBox Err.Number & "--" & Err.Description
            Resume Exit_Proc
            Resume
    End Select
End Sub

Private Sub cmdAddToAll_Click()
    Dim LastForecastDate    As Date
    Dim NewForecastDate     As Date
    Dim NewForecastMonth    As String
    Dim db                  As DAO.Database
    Dim qd                  As DAO.QueryDef
    Dim CountAffected       As Long
    
    If Me.cboRateGroup & "" = "" Then
        LastForecastDate = Nz(DMax("ForecastDate", "tblNewMeterForecastByMonth"), #12/31/1899#)
    Else
        LastForecastDate = Nz(DMax("ForecastDate", "tblNewMeterForecastByMonth", "RateGroup = '" & Me.cboRateGroup & "'"), #12/31/1899#)
    End If
    NewForecastDate = DateAdd("m", 1, LastForecastDate)
    NewForecastDate = LstDayNextMnth(LastForecastDate)
    NewForecastMonth = Year(NewForecastDate) & "/" & Format(Month(NewForecastDate), "00")
     
    Set db = CurrentDb
    Set qd = db.QueryDefs!qAddNewMonthForEveryUtilityForecast
        qd.Parameters!EnterForecastDate = LastForecastDate
        qd.Parameters!EnterNewForecastDate = NewForecastDate
        qd.Parameters!EnterNewForecastMonth = NewForecastMonth
        qd.Parameters!EnterRateGroup = Me.cboRateGroup
    qd.Execute
    CountAffected = qd.RecordsAffected
    MsgBox CountAffected & " records were added.", vbOKOnly
End Sub

Private Sub cmdDel_Click()
    If Me.cboUtilityGroup & "" = "" And Me.cboRateGroup & "" = "" And Me.cboFrom & "" = "" And Me.cboThru & "" = "" Then
        If MsgBox("Are you sure you want to run an unfiltered delete?  This could delete more records than you expect.", vbYesNo) = vbNo Then
            MsgBox "Delete cancelled.", vbOKOnly
            Exit Sub
        End If
    End If
    If MsgBox("Are you sure you want to run a bulk delete that will affect all selected records?", vbYesNo) = vbNo Then
        MsgBox "Delete Cancelled", vbOKOnly
        Exit Sub
    End If
    DoCmd.RunMacro "mWarningsOff"
    DoCmd.OpenQuery "qDelFilteredNewMeterForecast"
    DoCmd.RunMacro "mWarningsOn"
    Me.sfrmNewMeterForecastByMonth.Requery
End Sub

Private Sub cmdFilterOff_Click()
    Me.cboRateGroup = Null
    Me.cboUtilityGroup = Null
    Me.cboFrom = Null
    Me.cboThru = Null
    Me.sfrmNewMeterForecastByMonth.Requery
End Sub

Private Sub cmdFilterOn_Click()
On Error GoTo Err_Proc

    If Me.cboFrom & "" <> "" Then
        If Me.cboThru & "" <> "" Then
        Else
            MsgBox "Select a thru period.", vbOKOnly
            Me.cboThru.SetFocus
            Exit Sub
        End If
    End If
    Me.sfrmNewMeterForecastByMonth.Requery

Exit_Proc:
    Exit Sub

Err_Proc:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & "--" & Err.Description, vbOKOnly
    End Select
    Resume Exit_Proc

End Sub


Private Sub cmdUpdate_Click()
    If Me.cboUtilityGroup & "" = "" And Me.cboRateGroup & "" = "" And Me.cboFrom & "" = "" And Me.cboThru & "" = "" Then
        If MsgBox("Are you sure you want to run an unfiltered update?  This could change more records than you expect.", vbYesNo) = vbNo Then
            MsgBox "Update cancelled.", vbOKOnly
            Exit Sub
        End If
    End If
    If MsgBox("Are you sure you want to run a bulk update that will affect all selected records?", vbYesNo) = vbNo Then
        MsgBox "Update Cancelled", vbOKOnly
        Exit Sub
    End If
    DoCmd.RunMacro "mWarningsOff"
    DoCmd.OpenQuery "qUpdateFilteredNewMeterForecastWithBulkAmt"
    DoCmd.RunMacro "mWarningsOn"
    Me.sfrmNewMeterForecastByMonth.Requery
End Sub

Private Sub cmdView_Click()
    DoCmd.OpenQuery "qExportForecastWithAllProducts_crosstab"
End Sub

Private Sub Form_Close()
    Call CommonClose(Me)
End Sub

Open in new window

0
 
LVL 9

Assisted Solution

by:Armen Stein - Microsoft Access MVP since 2006
Armen Stein - Microsoft Access MVP since 2006 earned 250 total points
ID: 41761344
My objection to the Listbox is not that it can't be done - just it's that it's unwieldy with large numbers of records and requires more VBA coding.  I would probably tackle this "apply many behaviors to many students" problem this way:

- A work table that is loaded with a list of all possible students before the form loads.  It also has a SelectedFlag.
- Another work table that is loaded with all behaviors, plus a selected flag.
- A subform showing the Student records in the Student work table.  It could be sorted by different columns if desired.
- Another subform (below or to the side) that shows all records in the Behavior work table.

Let the user checkmark all the Students and Behaviors they like, using the Select Flag.  Then they click an Apply button, which adds all the selected Behaviors to each selected Student (if they don't already have that Behavior).  Since everything is in tables, this can be done using append queries instead of looping in code.

You also need to consider removing behaviors - my approach above doesn't cover that.  Perhaps you'd want to do that on an individual Student basis.  Or, if you wanted to get fancy, you could have buttons for Add Behaviors and Remove Behaviors that add or remove them as a group.
0
 

Author Comment

by:shogun5
ID: 41764298
Armen and Pat,

Thank you so much for your inputs and for taking the time from you busy schedule to offer suggestions. Both suggestions were a bit over my head so I just went with a simpler approach. I display my filtered list box (lstStudents) and for every students selected I just add a record to the tblStudentBahaviors like this:

Private Sub lstNegBehaviors_Click()
  Dim rowIndex As Integer
  Dim behaviorValue As String
  Dim pointValue As Integer
  Dim rowIsSelected As Integer

    Dim strSQL As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim ctl As Control
    Dim varItem As Variant

      
  Dim SoundFile As String
  
  
  
 ' ListBox row index clicked
  rowIndex = Me.lstBehaviors.ListIndex

  ' Row value clicked
  behaviorValue = Me.lstNegBehaviors.Column(1)
  
  pointValue = Nz(Me.lstNegBehaviors.Column(3), 0)
 
  
         On Error GoTo ErrorHandler
        
        Set db = CurrentDb()
        
          If Me.lstStudents.ItemsSelected.Count = 0 Then
           MsgBox "Please select at least one student."
           Exit Sub
          End If
        
        
            Set rs = db.OpenRecordset("tblStudentBehaviors", dbOpenDynaset, dbAppendOnly)
        
            For Each varItem In Me.lstStudents.ItemsSelected
             rs.AddNew
             rs!stdID = Me.lstStudents.Column(0, varItem)
             rs!stdBehavior = behaviorValue
             rs!stdBehaviorDate = Date
             rs!stdBehaviorPoint = pointValue
             rs.Update
            Next varItem
        
       
ExitHandler:
           Set rs = Nothing
           Set db = Nothing
      
      
      If pointValue > 0 Then
               If Not IsNull(DLookup("tblSettingsPositiveWav", "tblSettings")) Then
                 SoundFile = Nz(DLookup("tblSettingsPositiveWav", "tblSettings"), "")
                 API_PlaySound SoundFile
               End If
            Else
                If Not IsNull(DLookup("tblSettingsNegativeWav", "tblSettings")) Then
                 SoundFile = Nz(DLookup("tblSettingsNegativeWav", "tblSettings"), "")
                 API_PlaySound SoundFile
                End If
            End If
      Exit Sub
    
     DoCmd.OpenForm "frmPointAwarded", , , "studentID = '" & Me.studentID.Value & "'", OpenArgs:=sign & " " & pointValue & " " & behaviorValue
     DoCmd.Close acForm, "frmBehaviors"
     
ErrorHandler:
        
        Select Case Err
         Case Else
          MsgBox Err.Description
          DoCmd.Hourglass False
          Resume ExitHandler
         End Select
  
End Sub

Open in new window


Works like  a charm!

Cheers!
0
 

Author Closing Comment

by:shogun5
ID: 41764299
Thanks again guys!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now