Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    List Box that updates with query (Access 2k)

    I have a single form that has a table as its source. I have a command button that allows the user to filter by form. When this filter is applied the totals in 8 calculated fields update to reflect only those records that have been filtered for. I also have a list box that has the same table as its source, however when I filter the records, the listbox entries remain the same and do not filter. is there a way to filter the list box to show the same records that the single form has once the filter has been applied?

    Thanks in advance for any assistance.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: List Box that updates with query (Access 2k)

    The only way to repopulate a listbox control is to repopulate the row source, just as you would with a combo box. It means you have to run some code with the filter by form is used, which can get tricky if you using the built-in filter stuff you get with right-click. In that case you may need to construct some VBA to make all of it hang together. One technique might be to use the form OnCurrent event to look at the filter that has been applied, and then construct the appropriate SQL string for the list box from the filter.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Box that updates with query (Access 2k)

    Sounds like this may be a little over my head.

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: List Box that updates with query (Access 2k)

    Here's one way you may be able to do this. For this to work the combo or listbox MUST have RowSource that is same as form's RecordSource (table or query). For form's Apply Filter event:

    Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)

    FilterList (ApplyType)

    'The following is for test purposes only:
    'Dim strMsg As String
    'strMsg = "Apply Type = " & ApplyType & vbCrLf & vbCrLf & _
    "Filter: " & Me.Filter
    'MsgBox strMsg, vbInformation, "CURRENT FILTER"

    End Sub

    The event procedure calls this sub:

    Sub FilterList(intApplyType As Integer)

    'ApplyType constants (Form ApplyFilter event):
    'Const acShowAllRecords = 0 'Remove filter
    'Const acApplyFilter = 1 'Apply Filter
    'Const acCloseFilterWindow = 2 'Close Filter by Form window

    Dim strSQL As String
    Dim strFilter As String

    Select Case intApplyType
    Case 1 'Apply filter:
    strFilter = Me.Filter
    strSQL = "SELECT TaskID, TaskName " & _
    "FROM TABLE1 WHERE " & strFilter & " " & _
    "ORDER BY TaskID;"
    Case 0, 2
    'Remove filter or close Filter by Form window w/o applying filter:
    Me.Filter = ""
    strSQL = "SELECT TaskID, TaskName " & _
    "FROM TABLE1 ORDER BY TaskID;"
    End Select

    'Apply filter to combo RowSource:
    With Me.ListTask
    .RowSource = strSQL
    .Value = Me.TaskID
    End With

    End Sub

    In this example, TABLE1 is record source for both form and listbox. Listbox is named "ListTask." When you change listbox or combo's RowSource the list is requeried based on the specified SQL string, then its value is set to current record (in this example TaskID is table's primary key). If filter removed or no filter applied, all records are displayed in list. To see how filter string reads, uncomment MsgBox code in Apply Filter event; the filter string explicitly references name of form's RecordSource along with field names, which is why the listbox or combo must have same source.

    HTH

  5. #5
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Box that updates with query (Access 2k)

    Your help has been invaluable to me. Thank you again for your time and effort.

    I am getting an error when applying your help to my database. I pasted the code I currently have, however, when trying to apply the filter I get the following error:

    Run-time error '2465':

    Liability Database can't find the field 'ID' referred to in your expression.

    Any ideas?

    Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)

    Dim strSQL As String
    Dim strFilter As String

    Select Case ApplyType
    Case 1 'Apply filter
    DoCmd.ShowToolbar "CustomFilter", acToolbarYes
    DoCmd.ShowToolbar "Filter/Sort", acToolbarNo
    strFilter = Me.Filter
    strSQL = "SELECT ID" & "FROM tblData WHERE" & strFilter & "" & "ORDER BY ID;"

    Case 0, 2 '0 = Remove Filter, 2 = close Filter by Form window
    DoCmd.ShowToolbar "CustomFilter", acToolbarNo
    Me.Filter = ""
    strSQL = "SELECT ID" & "FROM tblData ORDER BY ID;"
    End Select

    'Apply filter to combo RowSource:
    With Me.List4002
    .RowSource = strSQL
    .Value = Me.ID

    End With

    End Sub

    Thanks again for your assistance.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: List Box that updates with query (Access 2k)

    On what line of the code does the error occur? Select the Debug option when you get this error message; the offending line should be highlighted.

  7. #7
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: List Box that updates with query (Access 2k)

    I assume ID is valid field in tblData? The code you posted looks OK except some spaces after quotes seemed to be missing, which could cause a problem. Also if SQL is all on one line don't need superfluous ampersands (&) between clauses. I used these because I was using line continuation for SQL statement. Revised code slightly as follows:

    Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)

    Dim strSQL As String
    Dim strFilter As String

    Select Case ApplyType
    Case 1 'Apply filter
    DoCmd.ShowToolbar "CustomFilter", acToolbarYes
    DoCmd.ShowToolbar "Filter/Sort", acToolbarNo
    strFilter = Me.Filter
    strSQL = "SELECT ID FROM tblData WHERE " & strFilter & " ORDER BY ID;"

    Case 0, 2 '0 = Remove Filter, 2 = close Filter by Form window
    DoCmd.ShowToolbar "CustomFilter", acToolbarNo
    Me.Filter = ""
    strSQL = "SELECT ID FROM tblData ORDER BY ID;"
    End Select

    'Apply filter to combo RowSource:
    With Me.List4002
    .RowSource = strSQL
    .Value = Me.ID
    End With

    End Sub

    Try using modified code & see if problem persists.

    HTH

  8. #8
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Box that updates with query (Access 2k)

    Error was highlighting the .value = Me.ID line. I will try Mark D's suggestion today and let you know how it goes.

    Thanks again for the assistance.

  9. #9
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Box that updates with query (Access 2k)

    Unfortunately I am still getting the same error. As stated previously the de###### is highlighting the .Value=Me.ID line of code. I am at a loss, so I will probably set some standard filters and just let the user choose, instead of utilizing the filter by form feature.

    Thanks.

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: List Box that updates with query (Access 2k)

    Is ID a field in the source or a control on the form. I would suggest you create a control on the form a text box called tboID which has as its source the field ID. Make it invisible and then refer in you code to tboID rrather than to ID.
    David Grugeon
    Brisbane Australia

  11. #11
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: List Box that updates with query (Access 2k)

    Are you sure there is a control on form named "ID"; or if so it may be good idea to rename control and then refer to control, not field, when setting value of listbox to current record, as recommended in previous reply. Though I do not experience this problem even if field and control have same names.

    If you go with Plan B - allow user to use only certain predefined filters (make sure form's Allow Filters property is set to No) - which is what I sometimes do - then you can filter listbox or combo box by using "Like" criteria in query that populates listbox. Example:

    SELECT TABLE1.TaskID
    FROM TABLE1
    WHERE TABLE1.SERIAL Like ([Forms]![frmTABLE1]![Combo_Serial] & "*")
    AND TABLE1.USERID Like ([Forms]![frmTABLE1]![Combo_UserID] & "*")
    ORDER BY TABLE1.TaskID;

    In this example "TaskID" is field listed in listbox; "SERIAL" and "USERID" are 2 fields used to filter form; each filter is selected from a combo box on form which is referenced in the Like expression. If no filter applied (combo box value set to Null) all records will be listed. When you apply or remove filter, include instruction to requery listbox. Example:
    <pre>Me.Filter = "[USERID] = 'MD'"
    Me.FilterOn = True 'To remove filter, set to False
    Me.Listbox1.Requery</pre>

    NOTE: When applying filter using form's Filter and FilterOn properties in code, form's Apply Filter event is NOT triggered. If you want this event to be triggered when applying/removing filter, as alternative use DoCmd, as illustrated:
    <pre>DoCmd.ApplyFilter , "[USERID] = 'MD'" 'Apply filter
    DoCmd.ShowAllRecords 'Remove filter</pre>

    NOTE: Keep in mind this technique will work OK if the fields used for filter are required fields; Like expression will not "capture" records if specified field is Null. Meaning if no filter applied, records will not be listed if field named in Like expression is Null. If this is case, it'd be better to modify listbox's SQL in code to keep listbox in sync with form.

    HTH

  12. #12
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Box that updates with query (Access 2k)

    Thanks grudgeon, creating another control named tboID with the control source as ID is working. The only thing I have to fix is the SQL string (which I can do) so that all of the fields I want displayed in the listbox, as current SQL statement just shows the ID field. No errors now ans everything working great. I don't know if this had anything to do with it, but the original control named ID was a bound field, instead of an unbound control, which the tboID control was.

  13. #13
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Box that updates with query (Access 2k)

    I REALLY appreciate the help Mark D. As you can see from the above reply to grudgeon, it is now working.

    Thanks again.

Posting Permissions

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