custom not in list message

  • Thread starter Thread starter RipperT
  • Start date Start date
R

RipperT

Hi, I am not very good with code and I have a cbo in which a user selects a
value from a drop down list. If a user types a value that is not in the
list, I would like a custom message to display that will inform the user the
value needs to be entered into the table via another form, possibly even
take the user to that form. I have 'limit to list' set to "yes" on the cbo,
but this provides only a generic error message. What code might I put in the
cbo After_Update event to accomplish this?

Thanx in advance,

Rip
 
Hi, I am not very good with code and I have a cbo in which a user selects a
value from a drop down list. If a user types a value that is not in the
list, I would like a custom message to display that will inform the user the
value needs to be entered into the table via another form, possibly even
take the user to that form. I have 'limit to list' set to "yes" on the cbo,
but this provides only a generic error message. What code might I put in the
cbo After_Update event to accomplish this?

Thanx in advance,

Rip
Click to expand...

Set the Combo LimitToList property to Yes.

Here is some code, to be placed in the combo box NotInList event, that
will show a message and give the user a chance to add the value to the
list by opening a form for data entry.

Code the Combo NotInList event:

Private Sub CombolName_NotInList(NewData As String, Response As
Integer)
If MsgBox("This person is not in the list." & vbNewLine _
& "Would you like to add this name to the list?", vbInformation +
vbYesNo, "Not included in the list.") = vbYes Then
DoCmd.OpenForm "SecondFormName", , , , acFormAdd, acDialog
Me![CombolName].Requery
End If
Response = acDataErrContinue
End Sub
=======
Change the Combo Name and form names to whatever your's are.
 
Thanking you heavily for the reply. I am, however getting a compile error -
syntax error with the following lines:

If MsgBox("This person is not in the list." & vbNewLine _
& "Would you like to add this name to the list?", vbInformation +

Please help! I don't know the language, particularly with regard to
continuing code to the next line.

Many thanx!

Ripper

--
Ripper T Smith
rippertsmith<nospam>@comcast.net
fredg said:
Hi, I am not very good with code and I have a cbo in which a user selects
a
value from a drop down list. If a user types a value that is not in the
list, I would like a custom message to display that will inform the user
the
value needs to be entered into the table via another form, possibly even
take the user to that form. I have 'limit to list' set to "yes" on the
cbo,
but this provides only a generic error message. What code might I put in
the
cbo After_Update event to accomplish this?

Thanx in advance,

Rip
Click to expand...

Set the Combo LimitToList property to Yes.

Here is some code, to be placed in the combo box NotInList event, that
will show a message and give the user a chance to add the value to the
list by opening a form for data entry.

Code the Combo NotInList event:

Private Sub CombolName_NotInList(NewData As String, Response As
Integer)
If MsgBox("This person is not in the list." & vbNewLine _
& "Would you like to add this name to the list?", vbInformation +
vbYesNo, "Not included in the list.") = vbYes Then
DoCmd.OpenForm "SecondFormName", , , , acFormAdd, acDialog
Me![CombolName].Requery
End If
Response = acDataErrContinue
End Sub
=======
Change the Combo Name and form names to whatever your's are.
Click to expand...
 
in message:
Thanking you heavily for the reply. I am, however getting a compile error -
syntax error with the following lines:

If MsgBox("This person is not in the list." & vbNewLine _
& "Would you like to add this name to the list?", vbInformation +

Please help! I don't know the language, particularly with regard to
continuing code to the next line.
Click to expand...

There is some line wrapping on Fred's post. Try this:

Private Sub CombolName_NotInList(NewData As String, _
Response As Integer)
If MsgBox("This person is not in the list." & vbNewLine _
& "Would you like to add this name to the list?", _
vbInformation + vbYesNo, "Not included in the list.") _
= vbYes Then
DoCmd.OpenForm "SecondFormName", , , , acFormAdd, acDialog
Me![CombolName].Requery
End If
Response = acDataErrContinue
End Sub
 
Thanx a millon. This is working, but not perfectly. I have two questions.
First, when the form opens to add the person to the 'list', it shows only
one record of one. Why, when the form is based on a table that has many
records (besides the one I'm about to enter)? Two, I can't get out of that
form without a runtime error: "you must save the current field before you
can run the requery action". I get this upon closing the form after adding
the person and I believe it refers to the first form. What could I add to
take care of it?

I appreciate the help, Fred and Jeff!

Rip

--
Ripper T Smith
rippertsmith<nospam>@comcast.net
Jeff Conrad said:
in message:
Thanking you heavily for the reply. I am, however getting a compile
error -
syntax error with the following lines:

If MsgBox("This person is not in the list." & vbNewLine _
& "Would you like to add this name to the list?", vbInformation +

Please help! I don't know the language, particularly with regard to
continuing code to the next line.
Click to expand...

There is some line wrapping on Fred's post. Try this:

Private Sub CombolName_NotInList(NewData As String, _
Response As Integer)
If MsgBox("This person is not in the list." & vbNewLine _
& "Would you like to add this name to the list?", _
vbInformation + vbYesNo, "Not included in the list.") _
= vbYes Then
DoCmd.OpenForm "SecondFormName", , , , acFormAdd, acDialog
Me![CombolName].Requery
End If
Response = acDataErrContinue
End Sub
Click to expand...
 
in message:
Thanx a millon. This is working, but not perfectly. I have two questions.
First, when the form opens to add the person to the 'list', it shows only
one record of one. Why, when the form is based on a table that has many
records (besides the one I'm about to enter)? Two, I can't get out of that
form without a runtime error: "you must save the current field before you
can run the requery action". I get this upon closing the form after adding
the person and I believe it refers to the first form. What could I add to
take care of it?

I appreciate the help, Fred and Jeff!
Click to expand...

1. The second form is told to open to a new record so the user can
immediately begin data entry. If you want to see all the records
then remove acFormAdd part.

2. Add an Undo statement for the combo box.

Private Sub CombolName_NotInList(NewData As String, _
Response As Integer)
If MsgBox("This person is not in the list." & vbNewLine _
& "Would you like to add this name to the list?", _
vbInformation + vbYesNo, "Not included in the list.") _
= vbYes Then
Me.CombolName.Undo
DoCmd.OpenForm "SecondFormName", , , , acFormAdd, acDialog
Me.CombolName.Requery
End If
Response = acDataErrContinue

End Sub
 
Back
Top