not in list question

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

Guest

I have a combo box on a form for state abbreviations with the limit to list
set to true. I have code to handle this in case there needs to be additional
entries. All worked well until I needed to add an additional state through
code. This new state shows on the form but is not aded to the table so the
record will not save. How can I handle this situation?
Thanks in advance
 
Here is the code I have currently which works from the keyboard but it is not
working when the new data comes from code.
Private Sub State_NotInList(NewData As String, Response As Integer)
Dim Confirm
Confirm = MsgBox(StrConv(NewData, vbProperCase) _
& " Not In List." & _
vbCrLf & "Do You Want To Add It?", _
vbInformation + vbYesNo, "Limited Entry")
If Confirm = vbYes Then
Dim ListStates As New Recordset
ListStates.Open Source:="tlkpStates", _
ActiveConnection:=CurrentProject.Connection, _
CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic
ListStates.AddNew
ListStates!State = NewData
ListStates.Update
Me.State = NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

Do I need to change this or is there another problem?
Thanks
 
Do you have the "Limit to list" property for the combo box set to "Yes"?

The "Limit to List"property must be set to "Yes" for the "Not in list" event to
fire and run the code.

HTH
 
The code itself must be ok if it works, as you say, from the keyboard but
not if the data comes from code.

The help file explains the problem in kind of a round-about way. It states
that the LimitToList work when the "user" enters a value that isn't in the
list. Through omission, that would mean that if code enters the value, it
most likely will be accepted. My testing shows that it is, in fact, accepted
and stored in the combo box's Control Source field.

If you are going to enter the value via code, you'll need to do your own
check in the code to see if that is a value that currently exists in the
combo box and, if not, you'll need to add it to tlkpStates. You should be
able to use a DLookup() statement to see if the value is already there. If
not, add it, requery the combo box, and set its value. If it is there, just
set the value of the combo box.
 
Thanks! I'll work at it from that angle.

Wayne Morgan said:
The code itself must be ok if it works, as you say, from the keyboard but
not if the data comes from code.

The help file explains the problem in kind of a round-about way. It states
that the LimitToList work when the "user" enters a value that isn't in the
list. Through omission, that would mean that if code enters the value, it
most likely will be accepted. My testing shows that it is, in fact, accepted
and stored in the combo box's Control Source field.

If you are going to enter the value via code, you'll need to do your own
check in the code to see if that is a value that currently exists in the
combo box and, if not, you'll need to add it to tlkpStates. You should be
able to use a DLookup() statement to see if the value is already there. If
not, add it, requery the combo box, and set its value. If it is there, just
set the value of the combo box.
Click to expand...
 
Back
Top