Results 1 to 11 of 11

Thread: Form question

  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a form (Access 2003) that I want to use Filter by Form on. There are over 50,000 records behind this form. When I use the dropdown lists beside the fields, it say is Null or is not null. I know I have used filter by form before and the dropdown lists contain what is actually in the field. Is there anyway to get this data to appear instead of Is Null or Is not Null?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    By default, the dropdown lists only show individual values if there are 1,000 or fewer entries. If there are more, the dropdown list will only show Is Null and Is Not Null.
    You can increase the limit as follows:
    - Select Tools | Options...
    - Activate the Edit/Find tab.
    - Type the new limit in the box labeled "Don't display lists where more than this number of records read:".
    - The maximum number you can enter is 32766.
    - Click OK.
    Attached Images Attached Images
    • File Type: png x.png (9.2 KB, 0 views)

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    In the query behind the form, I used a filter to get the records down to 30,000 but the filter by form still says is null or is not null instead of the field content. Shouldn't it work with that amount of records or less?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you increased the value in the text box to more than 30,000 I'd expect the dropdown to display all items. Have you quit and restarted Access to see it that makes a difference?

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='795448' date='29-Sep-2009 12:55']If you increased the value in the text box to more than 30,000 I'd expect the dropdown to display all items. Have you quit and restarted Access to see it that makes a difference?[/quote]


    I rebooted and no luck. I have another form that used filter by form and it has over 800,000 records and it shows the field content. I do notice that the fields are indexed. I tried to index the fields in this database and it still doesn't work. I even filtered the query to be only 110 records and nothing.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Where are the source data? In the same database, in another Access database or in another type of database such as SQL Server or Oracle?

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='795478' date='29-Sep-2009 13:15']Where are the source data? In the same database, in another Access database or in another type of database such as SQL Server or Oracle?[/quote]


    The source data is in the same database.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Some other things to check:

    - Make sure that the check boxes "Local indexed fields" and "Local non-indexed fields" in the Edit/Find tab of Tools | Options... are ticked, as in my ealrier screenshot.

    - Open the form in design view. Select a text box or combo box, and activate the Data tab of the Properties window. Make sure that the Filter Lookup property is set to Default or Always, not to Never.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='795485' date='29-Sep-2009 13:47']Some other things to check:

    - Make sure that the check boxes "Local indexed fields" and "Local non-indexed fields" in the Edit/Find tab of Tools | Options... are ticked, as in my ealrier screenshot.

    - Open the form in design view. Select a text box or combo box, and activate the Data tab of the Properties window. Make sure that the Filter Lookup property is set to Default or Always, not to Never.[/quote]


    Checked everything you suggested and still not working. I did notice that there are some fields that are indexed and are numeric that do have data in the drop down lists but the text fields - no matter what I do (make them indexed) don't work. There is a date field and a unique Id field that are indexed and they do have data in the dropdown list.

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    [quote name='LindaR' post='795502' date='29-Sep-2009 09:10']Checked everything you suggested and still not working. I did notice that there are some fields that are indexed and are numeric that do have data in the drop down lists but the text fields - no matter what I do (make them indexed) don't work. There is a date field and a unique Id field that are indexed and they do have data in the dropdown list.[/quote]
    Can you strip the datasbase down to the point where you can upload it so we can take a look? It sounds like something weird is going on - my suspicion is that it is something on the form.
    Wendell

  11. #11
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='WendellB' post='795508' date='29-Sep-2009 15:44']Can you strip the datasbase down to the point where you can upload it so we can take a look? It sounds like something weird is going on - my suspicion is that it is something on the form.[/quote]

    A made a copy of the table used in the query without any data and imported it into a new database. I copied the query and the form and put 15 records in the table.

    The filter by form worked with that small amt of records.

Posting Permissions

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