setting .notinlist from VBA

[whilstiwait]

I have a form containing a number of unbound combo boxes in three
columns. Each column has boxes named "aaaa0", "aaaa1", etc. Using
syntax like:

With Forms(Me.Form.Name).Controls("txtTechnology" & i)
.AfterUpdate = "=txtTechnology_Change(" & i & ")"
.OnEnter = "=txtTechnology_OnEnter(" & i & ")"
End With

I can set events to a common routine for all the boxes, and in the
events use the passed ID (i) to reference the data in the combo box
that fired the event (using "with...")

I can set a .notinlist event, using syntax like:

.OnNotInList = "=txtTechnology_NotInList(" & i & ",0)"

which I know is not correct, but the event does fire. The ID doesn't
pass in.

The question: what syntax should I use on the .notinlist line to match
up the the (newData, Response) format of the notinlist event?

Thanks,

Keith

[Wayne Morgan]

The problem is, as you note, getting the functionallity of the built-in
parameters. If the NotInList routine is fairly complicated or a lot of
typing, you can do what you are wanting, but you'll need to use the
NotInList event to call the consolidated routine. Set the On NotInList to
[Event Procedure] then in the event procedure, pass the information to your
consolidated routine. You should even be able to pass a value for Response
to the consolidated routine. If you pass it ByRef and change it in the
consolidated routine, you will get the change back in the calling event. You
would then assign this changed value to Response once you return from the
consolidated routine.

Example:
Private Sub cboMyCombo1_NotInList(NewData As String, Response As Integer)
Dim intResponse As Integer, strComboName As String
'Remove the _NotInList to get the combo name
strComboName = Me.ActiveControl.Name
MyNotInList(NewData, intResponse, strComboName)
Response = intResponse
End Sub

Public Sub MyNotInList(NewData As String, ByRef intResponse As Integer,
strCombo As String)
'Do your add routine here.
'Set the value of intResponse before you exit, just as you would set the
value
'for Response in the normal sub.
'(intResponse = acDataErrAdded or intResponse = acDataErrContinue)
End Sub

The normal way of passing data in VBA is ByRef, so the ByRef keyword may not
be needed. However, it also shouldn't hurt anything and will serve as a
reminder of what you're trying to do.

--
Wayne Morgan
MS Access MVP
<wh*********@gmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

I have a form containing a number of unbound combo boxes in three
columns. Each column has boxes named "aaaa0", "aaaa1", etc. Using
syntax like:

With Forms(Me.Form.Name).Controls("txtTechnology" & i)
.AfterUpdate = "=txtTechnology_Change(" & i & ")"
.OnEnter = "=txtTechnology_OnEnter(" & i & ")"
End With

I can set events to a common routine for all the boxes, and in the
events use the passed ID (i) to reference the data in the combo box
that fired the event (using "with...")

I can set a .notinlist event, using syntax like:

.OnNotInList = "=txtTechnology_NotInList(" & i & ",0)"

which I know is not correct, but the event does fire. The ID doesn't
pass in.

The question: what syntax should I use on the .notinlist line to match
up the the (newData, Response) format of the notinlist event?

Thanks,

Keith

[whilstiwait]

Thanks for the suggestion.

Even though it's not correctly formed it does work. I set the ID on the
Enter event and the NotInList picks it up. I was hoping there would be
an easy way to set the event programmatically - it's the only one I've
found, so far, that has this problem.

Keith