What combobox events are triggered when an assignment is made to it via vba

[Dave44000]

I have a combobox (cboTwo) where you type in an office name, and if that office name is not in the list, the NotInList event opens a form. I have another combobox (cboOne) and create a record associating the entries in both comboboxes (many-to-many).

A user wants cboTwo to act like a normal combobox (one-to-many). The problem will be that existing records could legitimately end up being duplicated. Actually, not written, since I've prevented that.

I want to leave the system unchanged and just add a combobox (cboThree) that displays the already associated with the entity selected in cboOne. This works fine. I use the NotInList event on cboThree to assign the value in cboThree to cboTwo. The value does appear in cboTwo, but manually clicking there does nothing.

NotInList appears to process NewData (built in variable that apparently holds the value of the text box component of the combobox while assignment is made to the whole combobox.

The problem is that the NotInList event on cboTwo does not work when I make the assignment. I have put messages boxes in the combobox events, but can't seem to find anything that changes when the assignment was made. I've tried to click cboThree, but again, nothing seems to work.

Any help will be appreciated. Thanks.

Dave44000

[zmbd]

The basic Order of events:
Order of events for database objects (v2010)

That's a muddle to follow (@-@)

The problem is that the NotInList event on cboTwo does not work when I make the assignment. I have put messages boxes in the combobox events

Check that the properties are set correctly (ie. not in list, enabled, locked, etc...) When I use VBA to plug in values to my CBO, all of the events fire as expected which is why I am asking you to double check the property settings on your controls.

[Dave44000]

The properties are

Limit To List Yes
Allow Value List Edits Yes
Enabled Yes
Locked No

Allow Values List Edits is Yes, because the form that gets called adds a record and a the list is refreshed.

[zmbd]

What is the record source for your CBO?
(sorry should have asked for that in my first post)

> and you might want to take a look at "option 4" in the following:Adding values to lookup tables - Allen Browne

[Dave44000]

row source is

SELECT [PhysicianOffice].[PhysicianOfficeID]
   , [PhysicianOffice].[PhysicianOfficeName] 
   FROM PhysicianOffice 
   ORDER BY [PhysicianOfficeName]; 

This works perfectly before the third CBO got involved. If an entry wasn't in the list, it opened a form via the NotInList event. Once the value is assigned from the third CBO, and it is still not in the list, the NotInList event does not fire.

[Dave44000]

I read option 4, and yes, I'm not letting them add items to the list. They have to create a number of records to create the item. The form is a data entry form.

The hair splitting is caused by the difference between typing an entry in, which works, and assigning a value programmatically. The former opens the form. The latter does not, because I cannot get it to do so.

[zmbd]

That's reasonable.
Please post the code that is entering the value into your combobox - please remember to use the [CODE/] formating (^-^) to place the code tags.
I think that what's happening is that the focus is still on your second control, thus the afterupdata and notinlist events are not triggering, Seeing your code will help.

[Dave44000]

The source for the entry assigned to the cbo is cboPOShort, it lists those PhysicianOffices already assigned to Physician. The Physician has already been selected.

Row source for cboShort is

SELECT [POShortListQ].[PhysicianOfficeID], [POShortListQ].[PhysicianOfficeName] FROM POShortListQ ORDER BY [PhysicianOfficeName]; 
 

The query in the SELECT (POShortListQ) is

SELECT PhysicianOffice.PhysicianOfficeName
FROM PhysicianOffice INNER JOIN (Physician INNER JOIN PhysicianPhysicianOfficeJT ON Physician.PhysicianID = PhysicianPhysicianOfficeJT.PhysicianID) ON PhysicianOffice.PhysicianOfficeID = PhysicianPhysicianOfficeJT.PhysicianOfficeID
WHERE (((Physician.PhysicianID)=[forms]![frmMain]![cboPhysician]))
ORDER BY PhysicianOffice.PhysicianOfficeName;
 

The Not In List event on the assigning cbo is

Private Sub cboPOShort_NotInList(NewData As String, Response As Integer)
Me.cboPhysicianOffice = NewData
Me.Txt202 = NewData
Me.cboPOShort = Null
End Sub
 

[Dave44000]

I added SetFocus to the NotInList event. It cleaned up some left over behavior in cboPOShort. It also selects the value in cboPhysicianOffice. But, the relevant events are still not launching.

Private Sub cboPOShort_NotInList(NewData As String, Response As Integer)
Me.cboPhysicianOffice = NewData
Me.Txt202 = NewData
Me.cboPOShort = Null
Me.cboPhysicianOffice.SetFocus
End Sub

[zmbd]

Sorry,
I had to dig out my notes on ACC2003.
I had something along these same lines in a cascading combobox situation (long story), in anycase: the on_change event and not_in_list events are linked. I had thought this had changed in ACC2010.

From the following you can see that the On_Change event is not triggered by a VBA entry; thus, the not_in_list event is never triggered.

You can call the not_in_list event for the control that you are making the entry via VBA from the other control's not_in_list event; however, it may be better in long run to reconsider the design of the form

---

ComboBox.NotInList Event (Access)
Office 2010

The NotInList event occurs for combo boxes whose LimitToList property is set to Yes, after you enter a value that isn't in the list and attempt to move to another control or save the record. The event occurs after all the Change events for the combo box.

(BTW: Please take a look at the code examples here and compare them with what you are doing... some slight differences. (^_^) )
---
---

Change Event [Access 2003 VBA Language Reference]
Office 2003

Notes

•Setting the value of a control by using a macro or Visual Basic doesn't trigger this event for the control. You must type the data directly into the control, or set the control's Text property.
•This event applies only to controls on a form, not controls on a report.

You can not set the control's text property without setting the focus on the control. You can not change the control focus while within a not_in_list event; thus, you can not trip the on_change event until AFTER the not_in_list event finishes.
---

[Dave44000]

Thanks!

The move focus tip broke the logjam. Moving the focus change to the last line in the sub made a difference. I almost ignored the behavior change after that, but I went back and put it in again.

I've gone with the on Enter event. I'll put the value in the cbo, the user will click the cbo, and all is well. Help text will suffice. The form works as needed.

[Dave44000]

Thanks for telling me to rethink my solution.

It took me two more days of struggle before I came up with an easier way. Implementing that, I found an even easier way, far easier.