Access Combobox Not In List Event Not finding Added Record

Question

I'm trying to add a record to a ComboxBox source when the entered item isn't in the list.

The code correctly creates the record and then returns Response = acDataErrAdded but still displays the error Item not in list. And if I then manually scroll down the list the new user record is there!

tblUsers

ID(Auto Number), UserCode(Text), UserName(Text)

1, ID00001, "Tom Jones"

2, ID00007, "Fred Smith"

cmbUsers

RowSource = SELECT UserCode, UserName, ID FROM tblUsers

Bound Column = 3

Column Count = 3

Column Widths = "2cm;4cm;2cm"

Input Mask "ID00000;;_"

Private Sub cmbUsers_NotInList(NewData as string, Response as integer)
    dim sUserCode as string
    sUserCode = "ID" & NewData ' Doesn't have the "ID" ?!?!?
    if ActiveDirectoryUserIDExists(sUserCode) then
        AddNewUserFromActiveDirectory sUserCode
        Response = acDataErrAdded
        exit sub
    end if
    Response = acDataErrDisplay
End Sub

When NotInList is entered the NewData value is 00001 and is not prepended with the input mask "ID".

So I suspect that when the NotInList sub returns Access is using NewData value that is not prepended with "ID" so never finds the value is the re-queried list!

I have tried setting the NewData value to "ID" & NewData before exiting but this did not work.

So I'm really not sure!

Any ideas on how I can get this to work?

Answer 1

InputMask details

The docs for InputMask property say this regarding the second section for InputMask property:

If you use 0 for this section, all literal display characters (for example, the parentheses in a phone number input mask) are stored with the value; if you enter 1 or leave this section blank, only characters typed into the control are stored.

Example:

Input Mask: "ID"00000;0;_

This should cause the entire text value, e.g. "ID00001", to be passed to NotInList event handler in the NewData parameter.

Buggy InputMask behavior for bound ComboBox control

After extensive testing, it seems that this only works flawlessly for an unbound control. If the datatype of the column in the Control Source property does not support saving the fully formatted text value, then the text input value will be formatted to be compatible with (i.e. match) the bound field datatype even though the displayed text is not what is actually saved in the field. This is the best description I could come up with and I could not find any direct resolution to this bug.

So if the bound column is of text type (e.g. Short Text) then there is no problem. But as in the original question where the bound field is a foreign key column of Long Integer type (Control Source: [Assigned User]; Bound Column: 3; Row Source has [ID] as third column), then the input value is converted to a numerical string value before being passed to NotInList.

Workaround

This preserves the desired behavior of the ComboBox and InputMask with minimal code and one extra hidden TextBox control.

1. Add a simple bound TextBox control for the numeric foreign key field with the following properties:
   • Control Name: [Foreign_Key_Column_Name]
   • Control Source: [Foreign_Key_Column_Name]
   • Visible: False
2. Add unbound ComboBox control setup with InputMask property and NotInList event handler:
   • Control Name: [X_Foreign_Key_Column_Name]
   • Control Source: < Blank / null >
   • InputMask: "ID"00000;0;_
   • Row Source: SELECT UserCode, UserName, ID FROM tblUsers
   • Bound Column: 3 (this is not the same as the Control Source property binding to the form record source and apparently not what causes the buggy behavior)
3. Add NotInList event handler to unbound control with same code as before.

---

Private Sub X_Foreign_Key_Column_Name_NotInList(NewData as string, Response as integer)
    dim sUserCode as string
    sUserCode = NewData 
    if ActiveDirectoryUserIDExists(sUserCode) then
        AddNewUserFromActiveDirectory sUserCode
        Response = acDataErrAdded
    Else
        Response = acDataErrDisplay
    end if
End Sub

4. Add the following code to manually bind the ComboBox value to the numeric foreign-key column.

---

Private Sub Form_Current()
    Me.X_Foreign_Key_Column_Name.Value = Me.Foreign_Key_Column_Name.Value
End Sub

Private Sub X_Foreign_Key_Column_Name_AfterUpdate()
    Me.Foreign_Key_Column_Name.Value = Me.X_Foreign_Key_Column_Name.Value
End Sub