Results 1 to 4 of 4
  1. #1
    momani is offline Novice
    Windows XP Access 2003
    Join Date
    May 2013
    Posts
    10

    dealing with all the entries in a combo box

    hii

    I am using a combo box to show a specific records in the related sub_form , I would like to add a piece of code to the combo box so that it show an "all " option in the list that will show all the records in the sub_form .!!



    are there any simple way to do that.

    I am not familiar with VBA codes and I am using access 2003.


    Best regards
    Momani

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,752
    This is an unbound combobox used to input filter criteria? The RowSource is an SQL statement that pulls values from field in table? Try:

    SELECT "_ALL" AS fieldname FROM tablename
    UNION SELECT fieldname FROM tablename;

    Then code will have to translate the "_ALL" choice into wildcard filter. Show the code you have for filtering the subform.
    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
    momani is offline Novice
    Windows XP Access 2003
    Join Date
    May 2013
    Posts
    10
    hi again
    the combo box is unbounded one and used as a filter craiteria in a query, also the SQL pulls the values from a table called tblcategory as follows :

    SELECT tblCategory.categoryID, tblCategory.category FROM tblCategory ORDER BY [category];
    I have add a record "All" to the table, and I have tried to implement the code given but it always gives an error, could you help me with more detalis about the SQL statment

    regards
    ,

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,752
    Having a single record in tblCategory just so there is an "ALL" item in the RowSource won't accomplish anything because there is no data with a value of "ALL" to match the combobox parameter of "ALL".

    Also, dynamic parameter in the query won't work. Need to use code (I use only VBA, not macros) to apply filter to the subform RecordSource.

    Regardless of how you get "ALL" into the RowSource list (table record or the UNION query I suggested), need code to convert the "ALL" value. I didn't give you any code, what did you try and what is the error you get?

    In the combobox AfterUpdate event VBA procedure, try something like:

    Me.subformname.Form.FilterOn = False
    Me.subformname.Form.Filter = "fieldname='" & IIf(Me.combobox="ALL", "*", Me.combobox) & "'"
    Me.subformname.Form.FilterOn = True
    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.

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

Similar Threads

  1. Replies: 4
    Last Post: 01-04-2013, 04:03 PM
  2. Help - Query Dealing with Dates!!
    By bbanks2 in forum Queries
    Replies: 3
    Last Post: 12-18-2012, 11:31 AM
  3. New Entries to a Combo box
    By whojstall11 in forum Forms
    Replies: 3
    Last Post: 07-10-2012, 04:10 PM
  4. Entries made to Combo Boxes
    By jparker1954 in forum Access
    Replies: 6
    Last Post: 08-23-2011, 05:46 PM
  5. Replies: 3
    Last Post: 05-25-2010, 02:16 PM

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