Not in List Event Procedure problem 1

[healermoon]

I can add a new company without the msgbox popping up to say this isn't on the list are you sure you want to add it? I would like it to ask to keep misspellings etc out of the list. I used Dev Ashish's code with my own fld name, and table name:

' ************ Code Start **********
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Sub fldCustName_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available Company Name " & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to add a new Company Name?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblCust", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!fldCustName = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
'*********** Code End **************

Is is a matter of changing fldCustName to cbxCustName? I don't know why it isn't working. Please help.

Thanks-

Kenny Renaud
IT Manager
Corral West Ranchwear Inc.

 

[GingerR]

Private Sub fldCustName_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available Company Name " & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to add a new Company Name?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
me.fldCustName.undo
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblCust", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!fldCustName = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub


Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[healermoon]

Thanks. Unfortunatly that didn't help. I can type whatever I want in the box with no msgbox wanting to know if I want to add it when I hit enter and pop into another field. I know it is a weird one.

Thanks!!

Kenny Renaud
IT Manager
Corral West Ranchwear Inc.

 

[PHV]

And what is the value of the LimitToList property ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[healermoon]

The limit to list property is "no" and it will let me add to the list but will not check to see if the name is already on the list. I feel like a big dope.

Thanks for your help.

Kenny Renaud
IT Manager
Corral West Ranchwear Inc.

 

[MajP]

healermoon,
I personally never like using the not in list event, you are kind of limited to what you can do. I usually have a double click event on my list boxes that pop up a tabular form for the list choices. I usually add a sort field to these tables so that the user can specify the order for which the list appears. You index this list so no duplicates are added, and can do a lot of error checking. When the user closes the popup form the combo box is requeried showing the new choice.
That works nicely for adding a choice, but not so good for a one time event. In that case on the double click I ask them if they want to add to the list or a one time addition. If it is one time addition I set the limit to list property to no and after they enter the information set it back to yes.
You may find this strategy more flexible. If the combo box is updateable I usually mark it with a (+) in the label so that the user knows they can update the list.

 

[GingerR]

Limit to List should = YES.

I use LimitToList = YES and the On Not In List all of the time just fine.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[healermoon]

Thanks that worked!!!

Kenny Renaud
IT Manager
Corral West Ranchwear Inc.

 

[PHV]

Seems you didn't make sense with my post stamped 18 Nov 05 12:13 ...

 

[healermoon]

No sorry, Needed the set limit to list to yes .... you dummy. <grin> Thanks for trying, sorry I was to dense.

Kenny Renaud
IT Manager
Corral West Ranchwear Inc.