NotInList Event not working after populating the combo box via OpenArgs

Posted on 2009-02-18
Last Modified: 2013-11-29
The NotInList combox is a FormA.  I works fine the first time.  It lets me put the information on subform of FormB.   Now working with FormB/subform  I want to add another record.  So I Open FormA with the new record number as a OpenArg.  

Well FormA opens with the new record visible in the combo box but the not in list event does not refire, but backspace and reenter the number and press enter the NotInList works fine.

How do I get the NotInList to re-fire?
Question by:MG_Janiszewski
    LVL 119

    Expert Comment

    by:Rey Obrero
    can you upload your db
    LVL 58

    Expert Comment

    I don't understand. The "not in list" handler is meant for the human interface. If the user types something that isn't in the list, your code can react to it. But if you change the value through code, of course, you know what you are doing, and the event will not fire.

    Can't you just call whatever function you need directly? What go through the event handler at all? Depending on how it's written, you can of course call it (fake the "not in list" user event):

        YourCombo_NotInList "something", 0

    LVL 74

    Expert Comment

    by:Jeffrey Coachman

    A few issues.

    1. The NotInList Event fires for items typed in the combobox that do not exist in the source table.
    A combobox can reference one or many fields from the source table.
    Typically it displays the "Friendly" field, while referencing the Key Filed. (displaying the customer Name while referencing the customerID).
    It is not known what you combobox references or displays.

    2. If you are opening this form with a new record ID from the OpenArg Argument, it is not clear how you are inserting this value into the form (or source table), or what you are seeing (new record visible in the combo box ) in the combobox.  

    If you just created a new record, and you can "See" it in the combobox, why do you need or want the NotInList event to fire?
    What I mean to say is that, if you can "See it" in the combobox, then it should be IN the list already.

    Here is what I would do:
    In the same "code" you are generating the new Key value, you actually create the entire Main record, then open the main form synchronized to that record.

    But again you need to provide a real-world scenario under which this would all take place.
    A sample would also be nice, but we need to understand the "why" behind this system, before we can make any recommendations.

    Perhaps there is a more direct approach.



    Author Comment

    tblContractor(KTR)                     tblMaster                  tblWorkFormsItems(WFI)              tblworkForms(WF)

    KTRIDnum (autonum)                                                
    pkMainktr   many to 1                fkKTR
                                                      pkJO     1 to many        fkJO
                                                      pkKO    1 to many        fkKO
                                                                                           pkWF                 many to 1           pkWFNum

    pk=primary key
    fk=foreign key
    SWB= Switchboard

    My contractor SWBform has two NotInList combo boxes one for new contractors another for contractor work form numbers       the subcontractor becomes the pkJO in the tblMaster & contractor work form numbers become the fkKO in the tblMaster.

    This is what Iis happening
    The SWB opens the bogus sub contractor is entered  an input form for tblContractor opens
    via openArgs the sub contractor entered the start WF number is defaulted value of 001 and I  add the work description and main contractor data.  A cmdButton opens another input form and the thru OpenArgs enters data into the tblMaster.  I haven't told the SWB nor the Contractor Input forms to close or hide
    So when this last input closes via close button   The sub contractor NotInList Question refires and SWB query re-fires.  Like code has not completely run?    Am I doing something wrong by adding more fields
    to the NotInList input Form?

    LVL 58

    Expert Comment

    So you don't want the event to re-fire?

    the NotInList event is meant to be used in one of two ways:

    1) customize the message, but do nothing else (Response = acDataErrContinue)

    2) allow the user or the interface to add the value (Response = acDataErrAdded)

    In the second case, the combo box will be requeried in order to look again for the same value, expecting it to have been inserted in the source table. If it hasn't been inserted, it will look as though the event "re-fires". It doesn't, but the combo box is dropped open again so that the user can make a valid selection.

    Do you open the forms in dialog mode? I would think so (and that would be correct). Do you use Response = acDataErrAdded if the insertion of the new record succeeds, and do you use the exact string from NewData to generate the new record?


    Author Comment

    I have other shemes similar to this, but come  to think of it  it is not the not in list event firing it is the after update event.  both of the combo boxes have after update events to the find a form that both combo boxes call up without any input from the user.  it was pleasant suprise when it did this.  All of the forms are opening in dialog mode.   I think I have a problem of sequencing then forms  There are three forms

    FormA with the notinlist event combo boxes
    FormB that accepts the new data and the user adds more info about the new data
    FormC takes the new data and new inormation and places it in more tables through FormC and a subform  

    This is the current sequent

    FormA opens via Main Switchboard cmdButton>>>User inputs new data in the combo box>>>>The NotInList Event opens FormB (FormA is still open?) >>>>User inputs more information about the new data (I don't refresh this form yet)>>>>the User click a cmdButton on formB and transfer the new data and new information via OpenArgs to FormC then I refresh the is form and contiue on filling FormC and subform.  Then I close the form C and get the Question to Add the new data again like the code in FormA never finshed.      Did I miss a refresh form command on Form B before moving on to formC?
    LVL 58

    Expert Comment

    When you use the "not in list" event to open a new form, it needs to be opened in dialog mode. That way, the code will stop and wait for the dialog form to either close or become invisible. In your sequence, I suspect you close FormB too soon. As soon as you do so, the code from FormA will resume, despite FormC being open at that time (and hiding what's happening behind it).

    Perhaps you shouldn't use this method for complex data entry. If you need two forms to create the "new" record for the combo in FormA, it would probably be better to use something more classic: a "create new" button or something similar. Adding records in a "not in list" event should be left for simple cases: adding a missing category or a missing title.


    Author Comment

    I'm still a little confused in the sequencing of forms and user input.  This what I'm thinking.

    User inputs in NotInList data >after answering Yes >opens the dialog form to input new data & I add to it
                       FormA                                                                                   FormB
    What do I do with FormB to make it leave the field of view?  Close it or make invisible?  acErrDataAdded adds and requeries the NotinList combo box.  I should now have FormA with the new data waiting on the user to select the new data and the AfterUpdateEvent to open FormC
    LVL 58

    Accepted Solution

    This is the sequence of events?

    - user inputs an unknown new string in a combo on FormA
    - event "not in list" fires
    - the handler opens FormB, in dialog mode (suspending the "not in list event")
    - FormB uses the "new data" string to create a new record
    - FormB is closed (or made invisible)
    - "not in list continues", returning "added"
    - the combo is automatically re-queried
    - the same "new data" string is looked up
    - and found (since FormB added it)
    - resulting in updating the underlying field
    - triggering the "after update" event
    - which opens a new form: FormC

    This should work, it's a rather normal sequence. In the question, you also said:
    > So I Open FormA with the new record number as a OpenArg.

    Where does that fit in? Perhaps I should re-read all comments, and try to identify the forms A, B, and C in your previous explanations... I might have it all mixed up.


    Author Closing Comment

    Thank you for staying engage.  I'm at a real disadvantage.  The database is at work and holds information that can't just bring home.  So I ask questions at home and take the answers with me at work and try to make it work there.  Luckily I'm the keeper of the database, so I'm imposing on anyone eleses code.  

    Author Comment

    Thank you for staying engaged and not fussing at the newbie.  Someday I will feel comfortable to start helping the EE community instead of being full of questions.
    LVL 58

    Expert Comment

    Thank you for your kind words. Asking questions at home for a database at work shows real loyalty, or a real enthusiasm for Access!

    > ... fussing at the newbie

    I only "fuss" at newbies who think they aren't, or who don't take the time to do their part... You stayed engaged and really tried to understand what we were saying to you. This makes answering your questions pleasant and rewarding for us.

    While I'm at it:

    I understand that you can't bring the data for security reasons, but you surely can take the empty structure with you. Normally, this should be easy. If you have split the database into a "front end" (the application with queries, forms, etc) and "back end" (with only the tables), you can make a copy of the "back end", remove all the data, replace it with some dummy non-sense records, and use that outside of work. If your database is a single *.mdb file, it's more complicated. Since you are developing the database now, you should use the FE/BE architecture. If it isn't the case, we can help you with that, it will make your life a lot easier.

    >  Someday I will feel comfortable to start helping the EE community...

    I like to hear that. Some of the best experts on this site started like that. The next step isn't directly answering questions, but reading other people's questions. Browse through open questions, and use the "subscribe" link on those you find interesting, so you can follow the progress. Very soon, you will venture advice and clarifications of your own, and earn your first "assist".

    Good luck!

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    How get a scanned image on a report 4 17
    Calculate age in Access report 11 22
    How to use TempVars to open a form? 3 21
    Open Args 16 24
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now