MsgBox

[Pam]

Hi,
I'm trying to write code for a msgbox that has the data user wants to enter
in the msg of the box.

EX: Are you sure you want to enter "John Doe" as a contact?

So far I've entered every combination of .column, .value, quotations, and
field names (contactname and contactnamecombo) I can think of and nothing
works. I get the quotations to appear by themselves or with the name of the
combo box between - but not "John Doe".
Here's what I have:

Private Sub ContactNameCombo_NotInList(NewData As String, Response As
Integer)
On Error GoTo Err_ContactNameCombo_NotInList

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "fContactList"
If MsgBox("Are you sure you want to add """ & Me.ContactNameCombo.Column(2)
& """ as a contact?", vbYesNo, "Confirm Contact Entry") = vbYes Then
DoCmd.OpenForm stDocName, , , , acFormAdd, acDialog, NewData

Response = acDataErrAdded

Exit_ContactNameCombo_NotInList:
Exit Sub

Err_ContactNameCombo_NotInList:
MsgBox Err.Description
Resume Exit_ContactNameCombo_NotInList
End If
End Sub

I would very much appreciate any help available.
Thanks in advance.
Pam

 

[PNOman via AccessMonster.com]

Try removing the extra quotes:

If MsgBox("Are you sure you want to add " & Me.ContactNameCombo.Column(2)
& " as a contact?", vbYesNo, "Confirm Contact Entry") = vbYes Then
DoCmd.OpenForm stDocName, , , , acFormAdd, acDialog, NewData

 

[Jeff Boyce]

Pam

What's in "Me.ContactNameCombo.Column(2)" ... that is, what data is stored
there?

Please post the SQL statement that you use to "feed" that combobox.

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Mike B]

Test this with a simple String containing "John Doe" in place of your
"Me.ContactNameCombo.Column(2)" and you will see that John Doe shows up
correctly in the message box.
This leaves you with verifying the integrity of the source you are trying to
include in the string, namely the "Me.ContactNameCombo.Column(2)"

 

[PNOman via AccessMonster.com]

Sorry, I see that you wanted those quotes!

Did you remember that the column numbering starts at zero? So if your data is
in the second column, your reference would be .column(1)

 

[Dennis]

Pam,

There are two issues you need to deal with.

1. I think the End If is in the wrong spot.
2. I don't think the variable Me.ContactNameCombo.Column(2) is in scope.

Secondly, I think you are trying to do to much in one line. I know that the
style is to pack as much as you can into a single statement, but it makes it
a lot easier to read and debug if you take it in steps. Yes, it might take a
millisecond longer to run, but compare that to how much time you have spent
trying to debug this.

Also, try replacing the """ with '" (one single quote followed by a double
quote).

There is another issue that I run into sometime and that has to do with the
scope of the variable. I don't believe that the variable
Me.ContactNameCombo.Column(2) is "visible" or "in scope" when you your are in
ContactNameCombo_NotInList.

The way around this is to create a public variable. You do this at the top
of the form's code, on the lines just after the lines:

Option Compare Database
Option Explicit

and before the first Private Sub code, insert this line :

Dim pstrContactorName as String

This makes this variable visible throught out the program.

In your ContactNameCombo_AfterUpdate code, place the following line:

pstrContractorName = Me.ContactNameCombo.Column(2)

The variable pstrContractorName now holds the value of column(2) and is
visible through out your code.

Then in the msg box code, use pstrContractorName in place of
Me.ContactNameCombo.Column(2).


Your code should something like this:

Private Sub ContactNameCombo_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_ContactNameCombo_NotInList

Dim stDocName As String
Dim stLinkCriteria As String
Dim strMsgBoxTxt as String
Dim intMsgBoxResp as Integer

' Note: pstrContractorName is set in ContactNameCombo_AfterUpdate

stDocName = "fContactList"
strMsgBoxTxt = "Are you sure you want to add '" & pstrContractorName & "'
as a contactor?"

intMsgBoxResp = MsgBox(strMsgBoxTxt, vbYesNo, "Confirm Contact Entry")
If intMsgBoxResp = vbYes Then
DoCmd.OpenForm stDocName, , , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
End if

Exit_ContactNameCombo_NotInList:
Exit Sub

Err_ContactNameCombo_NotInList:
MsgBox Err.Description
Resume Exit_ContactNameCombo_NotInList

End Sub


Good luck

Dennis

 

[Steve Sanford]

When the NotInList event occurs, it is because the data you typed into the
text portion of the combo box was not in the combo box list. The only way to
'see' what you entered is to check the .Text property.

The data you type in the combo box is passed to the NotInList event via the
NewData argument. If you really want to get the data that was typed in, you
can use the property Me.ContactNameCombo.Text.

Here is a link to the Access Web for an example of the NotInList event code:

http://www.mvps.org/access/forms/frm0015.htm


I haven't tested this, but it should work:

'----------beg code----------------
Private Sub ContactNameCombo_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_ContactNameCombo_NotInList

Dim stDocName As String
Dim strMsg As String
Dim strTitle As String
Dim intButtons As Integer

stDocName = "fContactList"

strMsg = "Are you sure you want to add '" & NewData & "'"
strMsg = strMsg & " as a contactor?"

strTitle = "Add new name?"

intButtons = vbQuestion + vbYesNo
'if you want 'NO' to be the default button, use
' intButtons = vbQuestion + vbYesNo + vbDefaultButton2

If MsgBox(strMsg, intButtons, strTitle) = vbNo Then
Response = acDataErrContinue
Else
DoCmd.OpenForm stDocName, , , , acFormAdd, acDialog, NewData
Response = acDataErrAdded

End If

Exit_ContactNameCombo_NotInList:
Exit Sub

Err_ContactNameCombo_NotInList:
MsgBox Err.Description
Resume Exit_ContactNameCombo_NotInList

End Sub
'----------end code----------------


HTH

 

[Douglas J. Steele]

The NotInList event is invoked because you've typed something into the combo
box that doesn't already exist in the combo box in the first visible column.
It's extremely unlikely that the first visible column will be the third
column (which is the column to which Me.ContactNameCombo.Column(2) refers),
which imples that Me.ContactNameCombo.Column(2) will be undefined.

If you're trying to refer to the value that was typed in, use NewData
instead of Me.ContactNameCombo.Column(2).

If you're trying to refer to something else, you'll need to supply more
details about your expectations.

 

[Dennis]

Steve,

Opps - missed that. Good point.

If the contractor that was entered by the user is NOT in the list (query),
then there will be nothing in the variable Me.ContactNameCombo.Column(2) to
display. This is because the contractor is not on file! Therefore, there is
no name on file to display.

Good catch.

Dennis

 

[Pam]

Thanks to everyone for the informative responses. I did forget that Access
col count started at 0, but I did try 1 and it still didn't work properly.
I needed to look at the source code of the box and once I used NewData it
works as needed.
Thanks again for your time and help. I've learned (and still learning) a
lot about Access from you guys.
Pam