By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,700 Members | 1,506 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,700 IT Pros & Developers. It's quick & easy.

Custom message box on NotInList - don't want to add new value

P: n/a
I want to make a message box appear on the NotInList event for several
combo boxes. The message box will alert the user that if they need to
add a value they must go through the database administrator. I have
some code that isn't working but I assume it's close. I just don't
know what to do next. Thanks!!
-------------
Private Sub cboSiteClass_NotInList(NewData As String, Response As
Integer)

Dim Msg As String

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Advise the user that a new value cannot be added to the list.
MsgBox("'" & NewData & "' is not a valid selection." & vbCrLf
& _
"@Please see the SEUG database administrator if you want to
add it.", 0, "Invalid selection")

End If

End Sub
-------------
Heather
Nov 13 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
Heather,

Give this a try. I think it's what you're looking for. Couple of
things, though. Not sure what the 'Dim Msg As String' was for, but it's
not needed for any of this. Also, you needed to use the Access constant
acDataErrContinue to give the Response variable a value. If you don't
you'll get the second system standard message stating that the item was
not in the list. I'm assuming you don't want to see that right after
your message pops up...

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

' Not sure what this is for??
Dim Msg As String

' Exit this subroutine if the combo box was cleared.
If NewData <> "" Then
' Advise the user that a new value cannot be added to the list.
MsgBox "'" & NewData & "' is not a valid selection." & vbCrLf & _
"Please see the SEUG database administrator if you want to " & _
"add it.",_
0, _
"Invalid selection"
End If

NewData = ""
cboSiteClass = ""
Response = acDataErrContinue
End Sub

------
Chris
cdmwebs [at] no_spam_hotmail [dot] com

Heather wrote:
I want to make a message box appear on the NotInList event for several
combo boxes. The message box will alert the user that if they need to
add a value they must go through the database administrator. I have
some code that isn't working but I assume it's close. I just don't
know what to do next. Thanks!!
-------------
Private Sub cboSiteClass_NotInList(NewData As String, Response As
Integer)

Dim Msg As String

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Advise the user that a new value cannot be added to the list.
MsgBox("'" & NewData & "' is not a valid selection." & vbCrLf
& _
"@Please see the SEUG database administrator if you want to
add it.", 0, "Invalid selection")

End If

End Sub
-------------
Heather

Nov 13 '05 #2

P: n/a
Heather,

Give this a try. I think it's what you're looking for. Couple of
things, though. Not sure what the 'Dim Msg As String' was for, but it's
not needed for any of this. Also, you needed to use the Access constant
acDataErrContinue to give the Response variable a value. If you don't
you'll get the second system standard message stating that the item was
not in the list. I'm assuming you don't want to see that right after
your message pops up...

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

' Not sure what this is for??
Dim Msg As String

' Exit this subroutine if the combo box was cleared.
If NewData <> "" Then
' Advise the user that a new value cannot be added to the list.
MsgBox "'" & NewData & "' is not a valid selection." & vbCrLf & _
"Please see the SEUG database administrator if you want to " & _
"add it.", _
0, _
"Invalid selection"
End If

NewData = ""
cboSiteClass = ""
Response = acDataErrContinue
End Sub

------
Chris
cdmwebs [at] no_spam_hotmail [dot] com

Heather wrote:
I want to make a message box appear on the NotInList event for several
combo boxes. The message box will alert the user that if they need to
add a value they must go through the database administrator. I have
some code that isn't working but I assume it's close. I just don't
know what to do next. Thanks!!
-------------
Private Sub cboSiteClass_NotInList(NewData As String, Response As
Integer)

Dim Msg As String

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Advise the user that a new value cannot be added to the list.
MsgBox("'" & NewData & "' is not a valid selection." & vbCrLf
& _
"@Please see the SEUG database administrator if you want to
add it.", 0, "Invalid selection")

End If

End Sub
-------------
Heather

Nov 13 '05 #3

P: n/a
Heather,

Give this a try. I think it's what you're looking for. Couple of
things, though. Not sure what the 'Dim Msg As String' was for, but it's
not needed for any of this. Also, you needed to use the Access constant
acDataErrContinue to give the Response variable a value. If you don't
you'll get the second system standard message stating that the item was
not in the list. I'm assuming you don't want to see that right after
your message pops up...

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

' Not sure what this is for??
Dim Msg As String

' Exit this subroutine if the combo box was cleared.
If NewData <> "" Then
' Advise the user that a new value cannot be added to the list.
MsgBox "'" & NewData & "' is not a valid selection." & vbCrLf & _
"Please see the SEUG database administrator if you want to " & _
"add it.", _
0, _
"Invalid selection"
End If

NewData = ""
cboSiteClass = ""
Response = acDataErrContinue
End Sub

------
Chris
cdmwebs [at] no_spam_hotmail [dot] com

Heather wrote:
I want to make a message box appear on the NotInList event for several
combo boxes. The message box will alert the user that if they need to
add a value they must go through the database administrator. I have
some code that isn't working but I assume it's close. I just don't
know what to do next. Thanks!!
-------------
Private Sub cboSiteClass_NotInList(NewData As String, Response As
Integer)

Dim Msg As String

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Advise the user that a new value cannot be added to the list.
MsgBox("'" & NewData & "' is not a valid selection." & vbCrLf
& _
"@Please see the SEUG database administrator if you want to
add it.", 0, "Invalid selection")

End If

End Sub
-------------
Heather



Nov 13 '05 #4

P: n/a
Chris, Thanks so much! I tweaked the code a little bit cause the
MsgBox wasn't working. I kept getting an error in the Module window.
So I brought back the Dim strMsg since I went from MsgBox to Msg. (I
think that was the right thing to do.) This is what I have now but
when I try to add a not-in-list value, the combo box just clears
itself and drops down the list for me to choose from. No error
message, nothing. I don't know what's wrong with the code. Any
thoughts? Thank you so much!

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

Dim strMsg As String

' Exit this subroutine if the combo box was cleared.
If NewData <> "" Then

' Advise the user that a new value cannot be added to the list.
strMsg = "'" & NewData & "' is not a valid selection." & vbCrLf &
_
strMsg = strMsg & "Please see the SEUG database administrator if
you want to add it."

End If

NewData = ""
cboSiteClass = ""
Response = acDataErrContinue

End Sub
Nov 13 '05 #5

P: n/a
I figured this out after looking more closely at Microsoft Access 97
Step By Step Visual Basic by Evan Callahan, pg 94. I knew this was
super simple but being a VBA newbie I was lost. Thanks also to Chris
- I used some of your code. Here is the code that makes it work.
-------------------------
Private Sub cboYourComboBox_NotInList(NewData As String, Cancel As
Integer)

' Advise the user that a new value cannot be added to the list.
If NewData <> "" Then
MsgBox "'" & NewData & "' is not a valid Your Field Name." &
vbCrLf_ & "Please see the database administrator if you want to add
it.",_ vbExclamation
cboYourComboBox.SetFocus 'Go back to Your Field Name field.
Cancel = True 'Cancel saving the record.
End If

End Sub
-------------------------

Heather
Nov 13 '05 #6

P: n/a
mal
Hi Heather,

I see you split the messages and have found the solution but just to
say that the reason that you didn't get an error message on the
previous attempt is that you forgot the Msgbox strMsg line.
I prefer the way you were doing it before to set up a long message
string as :
strMsg = "This is my first part "
strMsg = strMSG & MyVariable 'using this as an example
strMsg = strMsg & " and this will follew the variable."
Msgbox strMsg

just makes it a bit easier to read

Cheers

sa*****@yahoo.com (Heather) wrote in message news:<5b**************************@posting.google. com>...
I figured this out after looking more closely at Microsoft Access 97
Step By Step Visual Basic by Evan Callahan, pg 94. I knew this was
super simple but being a VBA newbie I was lost. Thanks also to Chris
- I used some of your code. Here is the code that makes it work.
-------------------------
Private Sub cboYourComboBox_NotInList(NewData As String, Cancel As
Integer)

' Advise the user that a new value cannot be added to the list.
If NewData <> "" Then
MsgBox "'" & NewData & "' is not a valid Your Field Name." &
vbCrLf_ & "Please see the database administrator if you want to add
it.",_ vbExclamation
cboYourComboBox.SetFocus 'Go back to Your Field Name field.
Cancel = True 'Cancel saving the record.
End If

End Sub
-------------------------

Heather

Nov 13 '05 #7

P: n/a
Thanks for following up with me. I agree that I like your format
better. And as I'm progressing in my db I'm learning more about VBA so
I can follow your code better than I could when I first posted. I've
printed out your post to save in my Access reference notebook for next
time I need to use that code and can't remember how to write it.
Thanks!
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #8

P: n/a
mal
No problem...
Not sure if/when you'll come to it, but if you are writing multiline
SQL statements they can be a nightmare to debug if they are all in one
line, or with continuation rows.
I stole the idea from someone I guess years ago !
It's a bit odd that little techniques like these aren't used in some
help systems but if you look through some postings you'll find all
sorts of things that make life easier.
Good Luck !
HEATHER CARTER-YOUNG <sa*****@yahoo.com> wrote in message news:<41**********************@news.newsgroups.ws> ...
Thanks for following up with me. I agree that I like your format
better. And as I'm progressing in my db I'm learning more about VBA so
I can follow your code better than I could when I first posted. I've
printed out your post to save in my Access reference notebook for next
time I need to use that code and can't remember how to write it.
Thanks!
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 13 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.