Not In List Coding Problem

B

Bobby_II

I'm having a problem with the "NotInList" coding. I have a combo box that
lists "Department Contacts", but when I try to input a new contact, I get a
"Run Time Error 2113...Value entered isn't valid for the field". I'm using
Access 2003 and here is my code.

Thanks for any help I can get.....

Private Sub Contacts_NotInList(NewData As String, Response As Integer)
Dim intSelect As Integer
Dim strTitle As String
Dim strMsg As String
Dim intMsgSettings As Integer
Dim strEntry As String

strTitle = "Not in List"
strMsg = "This item is new. Do you want to add it to the list?"

intMsgSettings = vbYesNo + vbQuestion + vbDefaultButton1
intSelect = MsgBox(strMsg, intMsgSettings, strTitle)

If intSelect = vbYes Then


DoCmd.OpenForm "f_DeptContact", , , , acFormAdd, acDialog
Response = acDataErrAdded

Me![Contacts] = NewData
Else
Me![Contacts] = Null
End If
End Sub
 
W

Wayne Morgan

How many columns are in the combo box? I suspect that your Bound Column is
not the column that is displayed. When you try to set the Value of the combo
box (Me!Contacts = NewData) you are setting the Bound Column to the
displayed data that you typed in.

I believe you will find, that once you've added the data with your pop-up
form, that the combo box will show the new entry as the current selection.
Try the following amendments to your code.
If intSelect = vbYes Then


DoCmd.OpenForm "f_DeptContact", , , , acFormAdd, acDialog
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me.Contacts.Undo
End If
End Sub

--
Wayne Morgan
MS Access MVP


Bobby_II said:
I'm having a problem with the "NotInList" coding. I have a combo box
that
lists "Department Contacts", but when I try to input a new contact, I get
a
"Run Time Error 2113...Value entered isn't valid for the field". I'm
using
Access 2003 and here is my code.

Thanks for any help I can get.....

Private Sub Contacts_NotInList(NewData As String, Response As Integer)
Dim intSelect As Integer
Dim strTitle As String
Dim strMsg As String
Dim intMsgSettings As Integer
Dim strEntry As String

strTitle = "Not in List"
strMsg = "This item is new. Do you want to add it to the list?"

intMsgSettings = vbYesNo + vbQuestion + vbDefaultButton1
intSelect = MsgBox(strMsg, intMsgSettings, strTitle)

If intSelect = vbYes Then


DoCmd.OpenForm "f_DeptContact", , , , acFormAdd, acDialog
Response = acDataErrAdded

Me![Contacts] = NewData
Else
Me![Contacts] = Null
End If
End Sub
 
B

Bobby_II

Wayne:

There are four columns in the combo box..ID, LastName, FirstName, Phone with
the ID being the "bound" column. So I should change the bound column to the
LastName instead of the ID? I will give it a try and let you know.

Thanks for the help
--
Bobby Childers

Dona Ana County


Wayne Morgan said:
How many columns are in the combo box? I suspect that your Bound Column is
not the column that is displayed. When you try to set the Value of the combo
box (Me!Contacts = NewData) you are setting the Bound Column to the
displayed data that you typed in.

I believe you will find, that once you've added the data with your pop-up
form, that the combo box will show the new entry as the current selection.
Try the following amendments to your code.
If intSelect = vbYes Then


DoCmd.OpenForm "f_DeptContact", , , , acFormAdd, acDialog
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me.Contacts.Undo
End If
End Sub

--
Wayne Morgan
MS Access MVP


Bobby_II said:
I'm having a problem with the "NotInList" coding. I have a combo box
that
lists "Department Contacts", but when I try to input a new contact, I get
a
"Run Time Error 2113...Value entered isn't valid for the field". I'm
using
Access 2003 and here is my code.

Thanks for any help I can get.....

Private Sub Contacts_NotInList(NewData As String, Response As Integer)
Dim intSelect As Integer
Dim strTitle As String
Dim strMsg As String
Dim intMsgSettings As Integer
Dim strEntry As String

strTitle = "Not in List"
strMsg = "This item is new. Do you want to add it to the list?"

intMsgSettings = vbYesNo + vbQuestion + vbDefaultButton1
intSelect = MsgBox(strMsg, intMsgSettings, strTitle)

If intSelect = vbYes Then


DoCmd.OpenForm "f_DeptContact", , , , acFormAdd, acDialog
Response = acDataErrAdded

Me![Contacts] = NewData
Else
Me![Contacts] = Null
End If
End Sub
 
B

Bobby_II

Wayne:

I tried setting the "bound" column to the "LastName" then I got an error
message of invalid, so I changed it back to the ID column and put in your
code and it now works. Thank you, I've been working on this for 2 days and
have been going nuts.....

I really appreciate your help.
 
W

Wayne Morgan

No, don't change the Bound Column. You haven't said, but I'm guessing the ID
field is an autonumber or is being filled in by the pop-up form when you use
it to create the new record. If so, I suspect that just the changes I listed
in the code will get the job done for you.

As an added feature, you may want to send the NewData as an OpenArgs
argument in the OpenForm call and when the pop-up form opens, use the
OpenArgs argument to prepopulate some of the boxes on it.
 

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