Access Combobox Match found?

[TheSmileyCoder]

I have a combobox on my form, which has its MatchRequired set to false. Basicly this means you can write new selections into the combobox. The rowsource of the combobox selects (distinct) all previous entries of that field in the underlying table.

The aim is no try and make what people write into the field uniform, but not limiting them to a pre-defined list.

My problem is that if a user types a new value that doesn't exist in the underlying query, I need to requery the combobox before it becomes available for the next record entry.

I would like to know in the Combobox before_Update event, whether or not a match was found, and if not, requery the combobox in the afterupdate event.

[zmbd]

TheSmileyCoder ...
Is it possible to use the "notinlist event" for what you are trying to do...

http://msdn.microsoft.com/en-us/library/office/aa211431(v=office.11).aspx

This is for V2003; however, is still valid in V2010

-z

[TheSmileyCoder]

As I read your post I initially thought *Facepalm* but then I realised that the "not in list" event only fires if the combobox is set with "Limit to list" as true. To maintain a list in the way you suggest I would need to add the value to a seperate table, in which case its true I could limit it to list, and have the not in list event add the value to the table. However I was hoping to not have to add an extra table.


I also soon after realised that it is no use to requery the value in the combobox's afterupdate event, since the value will not yet have been written to the table, it doesn't get written to table until the whole record is saved.

[zmbd]

TheSmileyCoder

This is a modification of some code I found some years ago, see if it works for you.

Option Compare Database
Option Explicit

Private Sub cbo_exampleupdatelist_NotInList(NewData As String, Response As Integer)
Dim z_ctl As Control
Dim z_YesNo As Integer
'
'Setup to handle the control
Set z_ctl = Me.cbo_exampleupdatelist
'
'Verify that the user is entering a new value:
z_YesNo = MsgBox("The value:" & vbCrLf & NewData & vbCrLf _
    & "Is not currently in the list." & vbCrLf _
    & "Would you like to add it?", vbQuestion + vbYesNo + vbDefaultButton2, _
    "Verify Data Entry")
If z_YesNo = vbYes Then
    ' Set Response argument to indicate that data
    ' is being added.
    Response = acDataErrAdded
    ' Add string in NewData argument to row source.
    z_ctl.RowSource = z_ctl.RowSource & ";" & NewData
Else
    'If user chooses Cancel, suppress error message
    ' and undo changes.
    Response = acDataErrContinue
    z_ctl.Undo
End If
End Sub

-z

[zmbd]

Here's another nice set of code...

http://msdn.microsoft.com/en-us/library/office/ff845736

I must have used something from one of the Inside&Out books as my code is fairy close to one listed in the link.

-z