Limit to list msg

K

KAnoe

I have a list that feeds a text cell that I have set to "Limit to list" in
the properties.

How can I set a msg box up for the user, if the text is not on the list. At
this time it gives a defauld msg.

Thanks

Keith
 
D

Dirk Goldgar

KAnoe said:
I have a list that feeds a text cell that I have set to "Limit to
list" in the properties.
Click to expand...

I hope you're talking about a combo box. If not, I haven't the faintest
idea what you mean.
How can I set a msg box up for the user, if the text is not on the
list. At this time it gives a defauld msg.
Click to expand...

Create an event procedure for the combo box's NotInList event. In that
event procedure, display your own message using the MsgBox function, and
then set the event procedure's Response argument to the defined constant
acDataErrContinue. Here's an example:

'----- start of example code -----
Private Sub Combo1_NotInList(NewData As String, Response As Integer)

MsgBox "Sorry, the value '" & NewData & _
"' is not in the list. " & _
"Please pick a value from the list.", _
vbExclamation, "Not In List"

Response = acDataErrContinue

End Sub

'----- end of example code -----
 
6

'69 Camaro

Hi, Keith.
How can I set a msg box up for the user, if the text is not on the list. At
this time it gives a defauld msg.
Click to expand...

The custom message may created in the combo box's OnNotInList( ) event. A
common method is to give the user the custom message that the item is not one
of the approved items to select and then allow the user to add it to the
table. In the following example, cboContact is the name of the combo box:

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

On Error GoTo ErrHandler

MsgBox "Please double-click this field" & vbCrLf & _
"to add a new entry to the list.", _
vbInformation + vbOKOnly, "No Matching Record"
Response = acDataErrContinue

Exit Sub

ErrHandler:

MsgBox "Error in cboContact_NotInList( ) in " & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

In the combo box's OnDblClick( ) event, the following example could be used,
where frmContacts is the name of the data input form:

Private Sub cboContact_DblClick(Cancel As Integer)

On Error GoTo ErrHandler

Dim nContactID As Long

If (IsNull(Me!cboContact)) Then
Me!cboContact.Text = ""
Else
nContactID = Me!cboContact
Me!cboContact = Null
End If

DoCmd.OpenForm "frmContacts", , , , , acDialog, "New"
Me!cboContact.Requery

If (nContactID <> 0) Then
Me!cboContact = nContactID
End If

Exit Sub

ErrHandler:

MsgBox "Error in cboContact_DblClick( ) in " & Me.Name & " form." & _
vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

In the frmContacts form's OnLoad( ) event:

Private Sub Form_Load()

On Error GoTo ErrHandler

If ((Me.OpenArgs = "New") And Not IsNull(Me!txtID)) Then
DoCmd.GoToRecord , , acNewRec
End If

Exit Sub

ErrHandler:

MsgBox "Error in Form_Load( ) in " & Me.Name & " form." & _
vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub


HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
L

Larry Linson

Is this a Combo Box on a Form, or a Lookup Field in Datasheet View?

Larry Linson
Microsoft Access MVP
 
Top