Page 1 of 3 123 LastLast
Results 1 to 15 of 37

Thread: OnNotInList

  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Unanswered: OnNotInList

    I have an unbound form that contains (among other things) 30 combo boxes.
    I'm trying to refrain from creating an OnNotInList process for each one. I'd rather create one function that I can call.
    If I call them
    cmbBox1
    cmbBox2
    :
    :
    cmbBox30

    and put "=cmbBox_NotInList(X)" in the OnNotInList event, it will call the
    function cmbBox_NotInList passing the number of the control.

    However, I lose the NewData and Response arguments that are passed to the typical OnNotInList procedure. I can duplicate the NewData argument using Me("cmbBox" & X).Text, but I can't mimic the Response argument.

    What I do is... a user puts a run id in that isn't in the list. I ask the user for the corrseponding product code, and insert the data into a table. The data inserts, but I have no way of telling the control that. So it still gives me the
    Not In List error message.

    Again, what I'm trying to do is to keep from creating 30 seperate procedures that call one central function that does all the work.

    Any ideas?
    Inspiration Through Fermentation

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Interesting; never tried it. Couldn't you have your function accept the new value as well (so you could use NewData to pass it), plus pass back a result you could test and convert to the appropriate response?
    Paul

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Sorry, just noticed you were avoiding any code at all in each combo's event. I guess my idea won't work in that instance.
    Paul

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Actually, I've narrowed it down to the need to suppress the "...Not In List..."
    error message. I can add the product, and set the value of the combox using:
    Me("CMBBOX" & X).Value = Me("cmbbox" & X).Text

    It gives me the error message, but I can still tab out of the control.
    Inspiration Through Fermentation

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Well, I imagine you could trap that error in the form's error event and pass back a response there.
    Paul

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Since you are adding anything which isn't in the list, why have the limit to list property set to yes? Could you set this to no and have some code to do a quick check to see if the value exists?

    Also, you have a lot of comboboxes on the form. I've done quite a bit with unbound forms and found that having a lot of comboboxes (which had a rowsource) on the unbound forms really bombarded SQL Server when I monitored the transactions. I'm not sure if it's the same situation with you but if you do utilize SQL Server, try monitoring the transactions when this form first opens. I ended up populating the rowsource in the OnFocus event for the comboboxes with the query for those which exceeded the Value list length but it still hit the server hard having a lot of comboboxes.

    On some of the comboboxes, since all I was gaining was the expanding of the text, I changed these to text boxes and had code to lookup if the value existed in the table or not.
    Last edited by pkstormy; 07-18-07 at 16:48.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    damn, just when pbaldy had me going in the right direction...

    pkstormy,

    The comboboxes will give the users 50-100 choices. They'll have 2 columns
    (run id and product code) The list is limited to only those run ids that apply to the prodcution line they've selected elsewhere on the form.
    Normally, the run id's are prepopulated from legacy system in an overnight run. Periodically, they will need to add a new number though.

    I am using SQL Server as a front end, and some of my "users" will be accessing the system over dial up into our network. The speed tests I've
    done so far have been acceptable, but I hadn't thought about having 30 combo boxes slowing it down. Maybe I should consider going back to the drawing board?
    Inspiration Through Fermentation

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I've seen the same kind of problem my namesake brought up. One solution I used (and I'd be interested to hear Paul's opinion of it) was to load those values into a local Access table when the application started. The combos looked at the local table for their values instead of the SQL Server tables, which dropped the server load considerably. I think that would only be practical for fairly static tables, though in your case you could append to both pretty easily.
    Paul

  9. #9
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    The local table thing could work.
    or--
    What if I used an array that populated as soon as the production line was selected and refreshed when a new run was added. That way I could mark records that are already "selected" on the form. Then I could use pkstormy's suggestion of populating the list when the combo box gets the focus.

    Which method would have less "overhead"?
    Inspiration Through Fermentation

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'm still trying to get my head round why a form would need 30 comboboxes!!
    Personally I think I'd go for a good old fashioned "find" button which opened a new (modal) form which you could use to select a value and pass it back to the other form.

    Concerning this
    Quote Originally Posted by pbaldy
    Interesting; never tried it. Couldn't you have your function accept the new value as well (so you could use NewData to pass it), plus pass back a result you could test and convert to the appropriate response?
    You can use two generic functions. The second of which could be called from within the first if the user wants it to.

    But still THIRTY(?!!?!?!) comboboxes... Jeez!
    George
    Home | Blog

  11. #11
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    heh... Did I say thirty? There's thirty related to this problem. There are 64 total on the form. 4 are in the header - select Plant, Line, Shift & Crew Name. Of the 30 "detail" lines on the form though, only 1 of 2 boxes would be visible. I'd rather give users a list of valid choices in the first place, then expect them to remember what they're supposed to put where.

    What can I say... in most instances I won't use bound forms. They leave too much room for error. Especially in a system that will be used by factory workers.
    Inspiration Through Fermentation

  12. #12
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I used a combination of all your suggestions. On the ones I didn't need the autoexpand of letters, I changed these to text boxes. I looked for code to autoexpand the next letter in a text box but found this pathetically slow for the large tables and didn't really like the code. For those, I used a Find button as georgev suggested. For some I used the OnFocus event to populate it as a value list (rowsource from a variant on a recordset) and others a Table/Query with the rowsource a query. Some, I had MS Access tables as pbaldy suggested (usually the smaller tables as I found Access's indexing unsatisfactory compared to SQL Server). Boy, indexing really makes a difference with the speed on these. I even went to the extent of rebuilding the index a few times and even tried repopulating a blank table with the new indexing scheme. I had a double-field join (clustered index) on 2 tables which really bogged down a few of the comboboxes and I finally had to do away with them as a combobox and changed them to a textbox because each table in the query had a few million records (I hated to see them go but it was just too slow over citrix.)

    I can't remember if it made a difference in the load if the rowsource queries of some of the comboxes were based on the same tables (but different field names). I had a tendancy to put description type fields normally in a lookup table in the main data tables with the ID field to prevent having to link in more tables in the queries and total a large number of records quickly. This was sort of a cross between a Data Cube and a DataWarehouse where the grouping and totalling fields are all in one table. I got it down to where it could total a large number of records using 1 table which helped tremendously with query speed. It's funny but it seemed like MSAccess queries were the fastest verses opening a recordset or SQL View. Stored Procedurs were very fast though. Triggers were instantaneous. Otherwise the speed was extremely slow on some of the queries with multiple relational tables and fields I couldn't index on. Speed became a big issue for me so I went with the unbound forms design and as few comboboxes as possible. I also tried breaking some of the stuff into separate forms which seemed to help a bit.

    SQL Server would take a big hit for me when the form would load with a lot of comboboxes (due to the number of records). It would open up a bunch of "begin transactions..." which seemed to stay open until the user closed the form. That's why I went with the OnFocus event. I even used the AfterUpdate of one field to populate other comboboxes with starting values but I don't recall how much that helped. I wasn't that much concerned with SQL Server though (once we got a decent amount of memory on the server box) as I was with the performance of the front-end. SQL Server was also moved to a lightning fast server and everyone got upgraded to a faster network card. The big totalling queries were run off of a desktop machine verses citrix due to the speed (which made a big difference.) After the network administrator upgraded to the latest version of Citrix, printer driver problems and performance started acting better for those internal and external users (we had about 70 people internally and 30 people externally via citrix - the rest had desktops.) Unbound forms though were a necessity on most of the data. Dialup was ungodly slow and citrix was a better performer. One of our competitors tried using MSAccess replication but their totals were never accurate and the evaluation agencies couldn't put the random id generator together easily for relationships. Plus there were a lot of complaints by external users synchronizing MSAccess to the source db (especially dialup users.) Hence we got their contract the next year.

    Please keep in mind that I was working with records in the millions. If your comboboxes are based on a few 100,000 records your situation may be different and none of this may be an issue.

    I had one wierd problem where the combobox didn't show the bottom half of the records on a few 100,000 records but this was fixed when we went from a daisy chain of hubs to a switch box. I also went with the A, B, C, D....Z buttons to help with the listboxes/querying rowsources in comboboxes and showing records beginning with A, B, C...for searches (like the searching program I uploaded in the DB Code Bank) I had to do this as again, it was ungodly slow populating all the millions of records in a listbox.

    Sorry I rambled on. Hope that helps.
    Last edited by pkstormy; 07-19-07 at 19:01.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  13. #13
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Thanks everyone! I'm actually dealing with records that would total no more than 10,000 per year.

    The recordsource for these combo boxes is pulling from a pass-thru query (if that matters - I think it does make a difference). I've noticed no speed issues yet, and I don't think I'll have more than 5-6 concurrent users at any given time. I'm kind of committed to the path I'm on, so I'm going to try to finish it this way, since all I need to do is supress the error message, which I think I can do with pbaldy's suggestion (post #5). Then I'll just have one of the folks at the plants using dial-up test it again. If it's slow, then back to the drawing board.

    george (or anyone else),
    Just curious.... Everything I know about Access I learned from trial and error, from a book, or from here. In 15 years of developing - I've never seen anything about using too many combo boxes on 1 form until this thread. If there's something you know that I don't, please elaborate.

    Thanks Again!
    Mark
    Inspiration Through Fermentation

  14. #14
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I don't think you'll have a problem with 10,000 records and 30-64 comboboxes. You may want to look at your indexing should things become slow. But out of curiosity, you may also want to run some SQL Server traces (showing just the Opening/Closing transactions) and let us know what happens.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I think you'll have a problem using 30-64 recordsets!!
    And from a users perspective - 30-64 comboboxes is just plain SCARY!
    George
    Home | Blog

Posting Permissions

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