Results 1 to 7 of 7

Thread: Listbox problem

  1. #1
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm working in Access 2003 from a query. I have a form with 2 comboboxes and a listbox. The comboboxes are to select Extension and Date. The listbox gives activities per extension, per day, e.g. "Sign on", Sign off", "Idle" etc. The SQL statement I'm using for the listbox row source is as follows:

    SELECT DISTINCT QUERY2.RES_ID AS EXT, NEW_DATE_STAMP AS [DATE], EVENT_NAME1 AS EVENT, START, ELAPSED, END FROM QUERY2
    WHERE (RES_ID=Extension and
    NEW_DATE_STAMP=Date)ORDER BY START

    It runs this way, but using input boxes for "Extension" and "Date". I"m using those as placeholders because I want the values of te two comboboxes there instead, so users can select Extension and Date instead of inputting them. The comboboxes are cboExtension and cboDate. What do I need to do in order to get the listbox connected to the two comboboxes? Any help will be appreciated; thanks in advance.

  2. #2
    Star Lounger
    Join Date
    Jun 2010
    Location
    Lichfield, UK
    Posts
    65
    Thanks
    14
    Thanked 0 Times in 0 Posts
    I would call the following code in the AfterUpdate event of the combo boxes. You will need to consider whether you want this to run after each of the combo boxes has been updated or only the second one. If you put it in the AfterUpdate event of only the second one (which is probably more logical) then it won't run if the user goes back to amend the first one. Therefore I would just put the code inside an IF statement to run if both combo boxes are populated and put in the AfterUpdate event of both combo boxes.

    Dim strSQL As String

    if IsNull(cboExtension)=False And IsNull(cboDate)=False Then
    strSQL = "SELECT DISTINCT QUERY2.RES_ID AS EXT, NEW_DATE_STAMP AS [DATE], EVENT_NAME1 AS EVENT, START, ELAPSED, END FROM QUERY2 WHERE (RES_ID=" & cboExtension.Value & " and NEW_DATE_STAMP=#" & cboDate.Value & "#) ORDER BY START"

    ListBox.RowSource = strSQL
    ListBox.Requery
    End if

    I'm not sure if Extension is a string or numeric. The code above assumes numeric. If a string then it should read:

    strSQL = "SELECT DISTINCT QUERY2.RES_ID AS EXT, NEW_DATE_STAMP AS [DATE], EVENT_NAME1 AS EVENT, START, ELAPSED, END FROM QUERY2 WHERE (RES_ID='" & cboExtension.Value & "' and NEW_DATE_STAMP=#" & cboDate.Value & "#) ORDER BY START"

    (includes extra single quotes around Extension.Value)

    Hope that helps

  3. #3
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks! That works well, except it doesn't pull in the data. I suspect it doesn't see cboExtension.value and cboDate.

  4. #4
    Star Lounger
    Join Date
    Jun 2010
    Location
    Lichfield, UK
    Posts
    65
    Thanks
    14
    Thanked 0 Times in 0 Posts
    To check whether it is seeing the values do a debug.print strSQL after it has been set and the values should be in the WHERE clause.

    I suspect it may not be finding a match on the date - Access can be picky in date formats.

    To try to isolate whether it is the date can you amend the line that sets the SQL to read:

    strSQL = "SELECT DISTINCT QUERY2.RES_ID AS EXT, NEW_DATE_STAMP AS [DATE], EVENT_NAME1 AS EVENT, START, ELAPSED, END FROM QUERY2 WHERE (RES_ID=" & cboExtension.Value & ") ORDER BY START"

    That should pull data in to the list box (although it will be for all dates). Let me know if it does.


    Secondly what is your RowSource and RowSource type for the date combo box? Also what format are the dates being displayed in in the combo box?

  5. #5
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK, so I'm following your last post line by line, debugging this thing, and get to the last line and discovered, way back last week when I had to create an intermediate table because of the way the telephone database spits out the date, I never changed the date format in that table from text to date. The query runs off that table, and the form runs off that query. Once I did that, it works perfectly! Thank you so much!


  6. #6
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    My form needs to display data in two ways: detail, and summary. The detail works fine. In order to do the summary, I need to sum time from the Elapsed field. I'm not sure whether I need to create a summary field in the query, or whether I need to do it in code. I've tried adding a field to the query:

    SUMMARY: sum([elapsed])

    but I get an error msg that I tried to execute a query that does not include the specified expression RES_ID as part of the aggregate function. That's the field that has the phone extension. On the form I'm going to want to filter by that, but I shouldn't think I'd need to include that as part of the Summary field.

    Thanks in advance.


  7. #7
    Star Lounger
    Join Date
    Jun 2010
    Location
    Lichfield, UK
    Posts
    65
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Hi Merlynsdad,

    You could do it in the code by adding all the Elapsed values return by the first query but that is long winded. I would recommend you do a separate query. In the new query you need to select sum([elapsed]) as you've suggested. Include in the select statement all other fields that you need and which identify the thing you are grouping eg RES_ID. However if you do this then you must also include them in a GROUP BY clause within the SQL eg GROUP BY RES_ID, Field2, Field3.......

    HTH

    Nigel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •