Not In List

I have a form with a subform. The subform has a combo box
for Part Number. The problem comes when I attempt to
enter a part number that is not on the list. In that case
I would like to direct the user to a form for entering a
new part number. When that is done, I would like to
return to the original form/subform and select the part
number from the list.
I have attempted this in two ways. First, I used a
message box at the combo box's Not In List event. My
limited knowledge of message box code only allows me to
use the message box to direct the user toward another
action such as clicking a command button. If I could use
the message box to open the Parts form (to add a new
part), that would probably work, but I can't figure it out.
Second attempt was to set the Not In List event to open
the Parts form directly, or to open a form that allows the
user to either cancel or to open the Parts form. Sort of
a message box alternative. This led to the problem
described below, but by a shorter route than using the
message box.
Adding a new part by whatever method requires, I think,
requerying the combo box. However, attempting to do so by
means of code leads to an error message that I need to
save the record first. If I attempt to save the record,
that leads me back to the original Not In List event, and
I get stuck in a loop. How can I have the Not In List
event for a combo box allow the user to add to the list,
then to select from the revised list?

Hello Bruce

What I use is the Not In List event
to prompt to "Double-click this field to add an entry to the list."
on Double-click it opens Your parts Form

-----------------Code-------------------

Private Sub Combo0_NotInList(NewData As String, Response As Integer)
MsgBox "Double-click this field to add an entry to the list."
Response = acDataErrContinue
End Sub
-------------end code-----------------------

Double-click Event

--------------Code--------------------------

Private Sub Combo0_DblClick(Cancel As Integer)
On Error GoTo Err_Combo0_DblClick
Dim lngCombo0 As Long

If IsNull(Me![Combo0]) Then
Me![Combo0].Text = ""
Else
lngCombo0 = Me![Combo0]
Me![Combo0] = Null
End If
DoCmd.OpenForm "YourFormName", , , , , acDialog, "GoToNew"
Me![Combo0].Requery
If lngCombo0 0 Then Me![Combo0] = lngCombo0

Exit_Combo0_DblClick:
Exit Sub

Err_Combo0_DblClick:
MsgBox Err.Description
Resume Exit_Combo0_DblClick
End Sub
-------------------end code---------------------------

You have to edit the Combo0 to your ComboBoxName
and YourFormName to the Name of your parts Form


Hope this helps

Thomas


"Bruce" wrote in message
...
I have a form with a subform. The subform has a combo box
for Part Number. The problem comes when I attempt to
enter a part number that is not on the list. In that case
I would like to direct the user to a form for entering a
new part number. When that is done, I would like to
return to the original form/subform and select the part
number from the list.
I have attempted this in two ways. First, I used a
message box at the combo box's Not In List event. My
limited knowledge of message box code only allows me to
use the message box to direct the user toward another
action such as clicking a command button. If I could use
the message box to open the Parts form (to add a new
part), that would probably work, but I can't figure it out.
Second attempt was to set the Not In List event to open
the Parts form directly, or to open a form that allows the
user to either cancel or to open the Parts form. Sort of
a message box alternative. This led to the problem
described below, but by a shorter route than using the
message box.
Adding a new part by whatever method requires, I think,
requerying the combo box. However, attempting to do so by
means of code leads to an error message that I need to
save the record first. If I attempt to save the record,
that leads me back to the original Not In List event, and
I get stuck in a loop. How can I have the Not In List
event for a combo box allow the user to add to the list,
then to select from the revised list?

I would rather not use double clicking the field to open
the form unless it is the only possible way. There are
two reasons for this. Main reason is that it tends to
confuse users. Second reason is that in many cases the
user will know the part is not on the list, and will just
want to add the part directly, perhpas by clicking a
command button.
I tested the code at the double click event of the combo
box, and I received this message:
"You tried to assign a null value to a variable that is
not a variant data type". After I clicked OK, it left a
record blank and moved to the next record, where any
attempt to do anything led me to the same error message.
As this is an undocumented error message (except in a few
limited circumstances that do not apply to my situation),
I can go nowhere with it.
I also tried the code from a command button. I added a
line to set the focus to the combo box, but I received the
same error message when I clicked the button. Attempts to
find out what a "variant data type" is led to the usual
Help file dead end.
Finally, I would prefer that the Not In List event would
be to open the form, since that is what is going to happen
anyhow. The message box is an extra step I would rather
avoid, unless I could make one of the message box choices
be to open the form.
-----Original Message-----
Hello Bruce

What I use is the Not In List event
to prompt to "Double-click this field to add an entry to
the list."
on Double-click it opens Your parts Form

-----------------Code-------------------

Private Sub Combo0_NotInList(NewData As String, Response
As Integer)
MsgBox "Double-click this field to add an entry to the
list."
Response = acDataErrContinue
End Sub
-------------end code-----------------------

Double-click Event

--------------Code--------------------------

Private Sub Combo0_DblClick(Cancel As Integer)
On Error GoTo Err_Combo0_DblClick
Dim lngCombo0 As Long

If IsNull(Me![Combo0]) Then
Me![Combo0].Text = ""
Else
lngCombo0 = Me![Combo0]
Me![Combo0] = Null
End If
DoCmd.OpenForm "YourFormName", , , , ,
acDialog, "GoToNew"
Me![Combo0].Requery
If lngCombo0 0 Then Me![Combo0] = lngCombo0

Exit_Combo0_DblClick:
Exit Sub

Err_Combo0_DblClick:
MsgBox Err.Description
Resume Exit_Combo0_DblClick
End Sub
-------------------end code---------------------------

You have to edit the Combo0 to your ComboBoxName
and YourFormName to the Name of your parts Form


Hope this helps

Thomas


"Bruce" wrote in
message
...
I have a form with a subform. The subform has a combo
box
for Part Number. The problem comes when I attempt to
enter a part number that is not on the list. In that
case
I would like to direct the user to a form for entering a
new part number. When that is done, I would like to
return to the original form/subform and select the part
number from the list.
I have attempted this in two ways. First, I used a
message box at the combo box's Not In List event. My
limited knowledge of message box code only allows me to
use the message box to direct the user toward another
action such as clicking a command button. If I could
use
the message box to open the Parts form (to add a new
part), that would probably work, but I can't figure it
out.
Second attempt was to set the Not In List event to open
the Parts form directly, or to open a form that allows
the
user to either cancel or to open the Parts form. Sort
of
a message box alternative. This led to the problem
described below, but by a shorter route than using the
message box.
Adding a new part by whatever method requires, I think,
requerying the combo box. However, attempting to do so
by
means of code leads to an error message that I need to
save the record first. If I attempt to save the record,
that leads me back to the original Not In List event,
and
I get stuck in a loop. How can I have the Not In List
event for a combo box allow the user to add to the list,
then to select from the revised list?


.