Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Lateralusx777 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    21

    Load only one record in combo boxes


    I have a very large table(440,000 entries) with lots of data per entry. I am encountering an issue now that the backend is SQL where the form that we use to access everything at once will crash when I search for a specific number. I read that changing the combo boxes to only load the record that is being displayed will help, however, I am not quite sure how to do this. Could anyone help direct me on this?

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I used to do something similar by only populating the combo after the user typed the first three characters of the search which drastically limits the records returned. To implement it you load the form with the combo having no row source specified, then in the Change event of the combo you count the characters and once you get to the number you think will be enough to limit the entries you set the row source "SELECT ComboField FROM tblTABLE WHERE ComboField LIKE " & Me.cboCombo.Text & "*".
    You might need to use a saved pass-through query with parameters, many examples available,here is the first one after searching for "access pass-through query with parameters":
    https://stackoverflow.com/questions/...-to-sql-server

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    Lateralusx777 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    21
    I am not using the combo box to search I just don't want 400,000 combo boxes to load 4,000 entries each. The combo boxes typically do not get manipulated in this view.

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    You cannot have that many controls on a form?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I have a very large table(440,000 entries) with lots of data per entry. I am encountering an issue now that the backend is SQL where the form that we use to access everything at once will crash when I search for a specific number.
    ???
    Maybe then you can explain a bit better your scenario\requirements. If the form is a datasheet or continuous form and the combos don't get manipulated why have the combo control to begin with? If the form does not need to be editable add the combo's row source table\query to the form's recordsource and replace the combo with a texbox.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    Lateralusx777 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    21
    I just deleted the combo box off the form to test and it did not fix the issue. I guess I have a different problem altogether.

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    With that amount of data you should have an unbound combo in the form's header to search for the specific record you want to load (using the approach in my first post to limit the combo's row source) then once the combo is populated you sue that to limit the form's recordsource to just that record.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Quote Originally Posted by Lateralusx777 View Post
    ...I just don't want 400,000 combo boxes to load 4,000 entries each...
    I'm not at all sure what you mean by this.

    You're surely not saying that each Record has 4000 Controls to load, right?

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  9. #9
    Lateralusx777 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    21
    We have a separate form to display a single record. Our CSRs use this form to group unrelated data. I can limit the results to the front 100,000 or so but it still is crashing on me when I search.

  10. #10
    Lateralusx777 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    21
    The combo box options are from a 4000 entry table.

  11. #11
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You need to limit the number of records before you worry about the combo. You should load the form empty then once you enter your search item you set the form's recordsource to only include the record(s) that match it. It is how most SQL-based front-ends work.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  12. #12
    Lateralusx777 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    21
    I am pretty new with access, have basically taught myself what I know with it. I walked into this environment the way it is and am cleaning up. I am not sure how to make my form load without records without applying an on load record filter which then needs removed to display results.

  13. #13
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Exactly, you set the form's record source to something like this:
    Code:
    SELECT * FROM tblYour400000Table WHERE True=False
    Then in the AfterUpdate event of the search combo you do:
    Code:
     Me.RecordSource="SELECT * FROM tblYour400000Table WHERE [YourNumberField] = " & Me.cboSearch
    Here are some links that might be useful to you:
    https://www.access-programmers.co.uk...issues.291269/
    https://stackoverflow.com/questions/...h-large-tables
    https://bytes.com/topic/access/answe...se-sql-backend

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  14. #14
    Lateralusx777 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    21
    It doesn't like me.recordsource. Says cannot find object me.

  15. #15
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Where did you put the code, it has to be in the form's module in the AfterUpdate event of the combo box that does the searching.
    Cheers,

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 04-22-2021, 09:00 AM
  2. Replies: 2
    Last Post: 06-28-2016, 02:49 PM
  3. Replies: 11
    Last Post: 08-06-2014, 09:47 AM
  4. Replies: 2
    Last Post: 06-10-2013, 06:22 PM
  5. Replies: 1
    Last Post: 02-16-2011, 11:40 AM

Tags for this Thread

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