• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 731
  • Last Modified:

NotInList Event not working after populating the combo box via OpenArgs

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?
Michael Janiszewski
Michael Janiszewski
1 Solution
Rey Obrero (Capricorn1)Commented:
can you upload your db
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

Jeffrey CoachmanCommented:

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.


Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Michael JaniszewskiRetired Access Database AdministratorAuthor Commented:
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?

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?

Michael JaniszewskiRetired Access Database AdministratorAuthor Commented:
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?
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.

Michael JaniszewskiRetired Access Database AdministratorAuthor Commented:
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
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.

Michael JaniszewskiRetired Access Database AdministratorAuthor Commented:
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.  
Michael JaniszewskiRetired Access Database AdministratorAuthor Commented:
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.
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now