Create my own 'not in list' event ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to create my own 'not in list' event?
I want to capture the data entered to the table but I don't want it to
become part of the combo box contents.
What I envisage doing is in the after update event for the combo box, check
if the value entered is not one of the currently listed values, and if not,
insert the contents with a special flag that excludes it from the combo box
query.
Are the values in a combo box part of a collection I can loop through? The
intellisense seems to indicate there is no .count property?
 
You can do whatever you like in the NotInList event. You just have to end it
with something like the following to stop the event adding to the combo box
list.

Me.controlname.Undo
Response = acDataErrContinue

Before you do those two lines you probably want to open a dialog form that
the user will use to create the new record.

DoCmd.OpenForm "frm", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData


In the new form's onLoad event you capture the new data:

If Not IsNull(Me.OpenArgs) Then
strNewData = Me.OpenArgs
' Do what you need to do to initialize the new record
End If
 
The ListCount property of the combo box will return how many records are
currently loaded into it.

To loop through the records, you'd use something like:

Dim lngLoop As Long

For lngLoop = 0 To (Me.MyCombo.ListCount - 1)
Debug.Print Me.MyCombo.ItemData(lngLoop)
Next lngLoop

That'll return the bound column. If you wanted, say, the third column of the
combo box, you'd use

Dim lngLoop As Long

For lngLoop = 0 To (Me.MyCombo.ListCount - 1)
Debug.Print Me.MyCombo.Column(2, lngLoop)
Next lngLoop

(the Column collection starts numbering at 0)
 
I think you misunderstood what I want. I dont want to use the NotInList
event, I want to create my own event. This must be completely transparent to
the user and I dont want any dialog boxes or messages to appear. I just need
the user to be able to type text into a combo box and have it stored in the
table that feeds the combo box. However, these values must never appear in
the combo box list.
 
I am not exactly sure what you are trying to do. However, if you just want
to enable someone to add any item to the list just by typing it in all you
need to do to get what you want is to open the combo box's Properties Sheet,
click on the Data tab, and set the Limit to List property to No. Any new
entry typed in the combo box will then be added to the bound table field of
the combo box. When the form is opened again or the combo box is requeried,
the new item will be displayed with the rest of the items in the list in the
combo box.

If, as you wrote, you want the item to NEVER appear in the combo box list
then you can do the following:

You can use the Before Update event of the combo box. You must set your Not
In List Property of the Combo Box to No for this to work.

You could add a not-visible text box to the Form bound to the field for the
new entry. Here is some code I tested for the Before Update event of the
combo box:

Private Sub myComboBox_BeforeUpdate(Cancel As Integer)
If Not IsNull(Me.myComboBox) Then
Me.myTextBox = Me.myComboBox
Me.myComboBox.Undo
End If
Cancel = True
End Sub

This will add the typed in contents to the text box and when you save the
record it will be saved to the table.

The only drawback is the user will no longer see the value in the combo box.
However, you could make the text box that will receive the value visible for
the user to see.

Patrick Wood
I think you misunderstood what I want. I dont want to use the NotInList
event, I want to create my own event. This must be completely transparent to
the user and I dont want any dialog boxes or messages to appear. I just need
the user to be able to type text into a combo box and have it stored in the
table that feeds the combo box. However, these values must never appear in
the combo box list.
You can do whatever you like in the NotInList event. You just have to end it
with something like the following to stop the event adding to the combo box
Click to expand...
[quoted text clipped - 27 lines]
Click to expand...
 
Back
Top