Combo Box (New Data not listed in Drop Down List)

  • Thread starter Thread starter WTC
  • Start date Start date
W

WTC

When using a form (A) in a combo box (A1) and does NOT have the Value I
want,
I use the Not In List Event. this works fine. So a form (B) opens to enter a
new value. I enter the new value(B1) on Form (B). When I close the form (B),
the value is
listed in the Combo box (A1) but it is not in the Drop Down list.

I am wondering why?

Example of my code in Form(B)

If IsLoaded("A") Then
Forms!A!A1.Undo
Forms!A!A2.SetFocus
Forms!A!A1.Requery
Forms!A!A1 = B1

DoCmd.Close
Else

DoCmd.GoToRecord , , acNewRec
End If
 
suggest you don't put any code in Form B that affects the combo box in Form
A. the NotInList event will handle the addition for you, smoothly, if you
let it. try the following code

Private Sub A1_NotInList(NewData As String, Response As Integer)

'it's a good idea to ask the question, because the user may have
'simply made a typo, rather than trying to enter a valid new value.
If MsgBox("Do you want to add a new value to the list?", _
vbDefaultButton1 + vbYesNo) = vbYes Then
DoCmd.OpenForm "Form B", , , , acFormAdd, acDialog
'opening the form as a dialog box causes this code to suspend until the
'form is closed, then the code resumes automatically.
Response = acDataErrAdded
Else
'if the user does NOT want to add a new value to the list, then
'the system quashes the error message, erases the invalid entry, and
'opens the droplist.
Response = acDataErrContinue
Me!A1 = Null
Me!A1.Dropdown
End If

End Sub

suggest you read up on the NotInList *Event* (not the Property) for a better
understanding of how the available responses work.

hth
 
Back
Top