Combo box - not in list event

S

SuzyQ

First, I originally posted this question on Aug. 27th in the Forms
discussion, but no one responded. I am reposting here in hoping for better
exposure. I know the general etiquette regarding double posting, but since
the other post didn't get a response, and it's been several days, I thought
I'd try again. Sorry if this causes problems, but I need an answer if one
exists (other than writing it in code which I'm sure can be done).

I have a combo box that the data comes from a table. I have it set so that
the users can enter information even though it isn't on the list, however I
want to know if the item is not on the list so that I can enable another
control on the form. It seems that when you allow items not on the list,
then the "not in list" event doesn't fire. Is this true, or do I have
something else set incorrectly? Is there an easy way to determine a "not in
list" event when you allow items that aren't on the list?
 
J

John Spencer

If you allow users to enter a value that is not in the list, then the not in
list event is not active.

You could write code in the after update event of the control to check to see
if the value in does not exist and give your users a warning message.

Private Sub NameOfCombobox_AfterUpdate()

IF (Len(Me.NameOfCombobox & "") > 0 Then
If DCount("*","[Name of Source Table]", _
"[Name of Field] = """ & Me.NameOfCombobox & """) = 0 Then
Msgbox Me.NameofCombobox & " is not a value in the list."
Me.SomeOtherControl.Enabled = True
Else
Me.SomeOtherControl.Enabled = False
End if

Else 'If the value is null or "" then disable control
Me.SomeOtherControl.Enabled = False
End IF

End Sub

You would probably also want code in the current event to enable/disable the
control.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
B

Beetle

For the Not In List event to work, the Limit To List property
of the combo box must be set to Yes. Then you use code
in the Not In List event to control what happens. For example;

Private Sub cboYourComboBox_NotInList (NewData As String, _
Response As Integer)

Dim strMsg As String
strMsg = "The value you entered is not in the list" & vbCrLf
strMsg = strMsg & "Would you like to add it?"

If MsgBox(strMsg, vbYesNo, "Add New Item") = vbYes Then
Dim strSQL As String
strSQL = "Insert Into tblSomeTable ([SomeField]) " & _
"Values (""" & NewData & """);"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Me!SomeControl.Enabled = True
Else
Me!cboYourComboBox.Undo
Response = acDataErrContinue
End If

End Sub

This is a simplified example of course. For instance, depending on
the structure of the table the contains the data for the combo box
(ie - how many other fields are in the table), you may need to
open a small form in Dialog mode and have the users enter all
the necessary values (rather than using a simple append query
as I did in the example). There may be other corrections that
need to be made to the code example also (it's air code). Post
back if you have questions or need help modifying it to work in
your app.
 
J

Jeff Boyce

If you allow items to be added that aren't on the list, the NotInList
doesn't fire, as far as I know.

As an alternative approach, if you LimitToList and add NotInList code that
opens a data entry form, your users could add the new item (to the list) and
return to the combobox with the new item added/accepted.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
S

SuzyQ

Yes. I've done that in other projects, but it's not what I need here. I
don't want the item added to the table. I just want to enable another
control that is disabled if they select from the list. I think John Spencer
may have given me what I need. I'll try what he suggests.
 
H

Hans Up

SuzyQ said:
I have a combo box that the data comes from a table. I have it set so that
the users can enter information even though it isn't on the list, however I
want to know if the item is not on the list so that I can enable another
control on the form. It seems that when you allow items not on the list,
then the "not in list" event doesn't fire. Is this true, or do I have
something else set incorrectly? Is there an easy way to determine a "not in
list" event when you allow items that aren't on the list?

With a named query or a SELECT statement as the RowSource for your combo
box, you can take advantage of the associated Recordset object. If you
get a match for your combo's value with the FindFirst method, you know
the selection is in the list. And NoMatch for not in list.

However, if the user deletes all the text in the combo, you may get a no
match. I don't know what you would want to happen then.

Private Sub cboTest_AfterUpdate()
With Me.cboTest
.Recordset.FindFirst "MyField = """ & .Value & """"
If .Recordset.NoMatch Then
Debug.Print "not in list"
Else
Debug.Print "in list"
End If
End With
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top