Results 1 to 13 of 13
  1. #1
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581

    Questions on using a list box

    I have a couple questions on using a list box. I have a form with a list box [lstTrainEmplList]. The list box has a column with numbers. Is there a way to get a sum of that column? I guessed at it and tried this: =Sum([lstTrainEmplList].[column](4)). That didn't work, text box just said, "Error." Is there a way to get the sum of that column?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Either use DSum() on table with same filter criteria applied to listbox RowSource or loop through the listbox items to accumulate the sum to a variable.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    I'm using an unbound combo box to filter the list box. Every time the combo box changes the row source changes also. So, I need to change the control source on it too. I tried this, but it said that it expected an end.
    Me.txtHrsTotal.ControlSource = "=DSum("Hrs","qryTrainingEmployeeListing","Employe esID = Forms!frmMainMenu!txtUserID")"
    Click image for larger version. 

Name:	Error.JPG 
Views:	32 
Size:	68.9 KB 
ID:	43255

  4. #4
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    I didn't understand, "loop through the listbox items to accumulate the sum to a variable."

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    What exactly do you not understand - what looping code is or how to loop through listbox items? There are many examples of code to loop listbox. Allen Browne shows looping a listbox in http://allenbrowne.com/ser-50.html

    In loop of listbox, pull number value and add it to a variable that is accumulating: dblSum = dblSum + value

    Use apostrophes instead of the embedded quote marks or double up the embedded quote marks so they will be seen as simple text not special characters in the string. Or instead of changing the ControlSource, leave it permanently with txtUserID parameter and just Refresh or Requery the control.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Your problem is nested quotes and that's often what causes that error. A pair of like quotes (single OR double) constitute the beginning and end of quoted text. Your first quoted phrase is "=DSum(" which of course makes no sense to the pc and thus neither does what comes after. There are several solutions, the easiest usually being to nest singles inside of doubles as in

    "=DSum('Hrs','qryTraining...' and 'so' on)"


    EDIT - too late again.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    That was perfect. Works great now. Thanks.
    One more question on this. On the same form, I have 2 unbound text fields to search for dates [txtBeginDate] [txtEndDate]. I'm trying to change the source code to find if the date field in the query [DOT] is in between them. This is what I used and I got absolutely no change:

    Me.lstTrainEmplList.RowSource = "SELECT [EmployeesID], [qryTrainingEmployeeListing].[TrainDates], [TCOLEClassList], [Class], [hrs] FROM qryTrainingEmployeeListing WHERE EmployeesID = Forms!frmMainMenu!txtUserID AND TCOLEClassListID = 1 AND DOT >= Forms!frmTrainingEmployeeListing!txtBegindate <= Forms!frmTrainingEmployeeListing!txtEndDate ORDER BY [DOT] DESC; "

    I'm guessing that I need # to identify the fields as dates. I'm not sure how to do that though.

  8. #8
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Perhaps:
    Code:
    Me.lstTrainEmplList.RowSource = "SELECT [EmployeesID], [qryTrainingEmployeeListing].[TrainDates], [TCOLEClassList], [Class], [hrs] FROM qryTrainingEmployeeListing WHERE EmployeesID = Forms!frmMainMenu!txtUserID AND TCOLEClassListID = 1 AND DOT Between Forms!frmTrainingEmployeeListing!txtBegindate AND Forms!frmTrainingEmployeeListing!txtEndDate ORDER BY [DOT] DESC; "
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  9. #9
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    Nope. I tried that one. My list box just goes blank.

  10. #10
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Can you post a copy
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  11. #11
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Whats the datatype of txtUserID, txtBegindate, txtEndDate ? Given the txt prefix are they text?
    Also if they are on the form you can refer to them with the Me keyword.

    Me.lstTrainEmplList.RowSource = "SELECT [EmployeesID], [qryTrainingEmployeeListing].[TrainDates], [TCOLEClassList], [Class], [hrs] FROM qryTrainingEmployeeListing WHERE EmployeesID = " & Me.txtUserID & " AND TCOLEClassListID = 1 AND DOT
    Between #" & Me.txtBegindate & "# AND #" & Me.txtEndDate & "# ORDER BY [DOT] DESC; "
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  12. #12
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    The reason I sometimes do the whole path instead of Me.txtText is because sometimes my forms don't act right with it. So, I will try both to see which works best. I tried your code but it just blanked my list box again.

  13. #13
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    I tested my query and it somehow got corrupted. I rewrote the query and it works great now. Thanks.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Not In List and Null Values Questions
    By d9pierce1 in forum Programming
    Replies: 10
    Last Post: 12-14-2018, 12:17 PM
  2. Replies: 8
    Last Post: 12-13-2017, 10:38 AM
  3. Some Questions
    By Hello1 in forum Access
    Replies: 2
    Last Post: 05-17-2015, 08:40 AM
  4. QBF Questions
    By brandonze in forum Forms
    Replies: 2
    Last Post: 08-04-2011, 01:49 PM
  5. List box questions
    By SteveL5231 in forum Forms
    Replies: 1
    Last Post: 12-15-2010, 08:43 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums