not in list event code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am not familiar with VBA or any of the coding for Access... I found this
code on mvps.I had been disinclined to use code *even from a good source*
because I wasn't sure I knew enough to apply it to the database I am trying
to create... I had a hard time reading through it but it doesn't look like
anything needs to be renamed. so I was wondering if this could be cut and
pasted into the notinlist event or if there was more too it...

http://www.mvps.org/access/forms/frm0015.htm
 
I am not familiar with VBA or any of the coding for Access... I found this
code on mvps.I had been disinclined to use code *even from a good source*
because I wasn't sure I knew enough to apply it to the database I am trying
to create... I had a hard time reading through it but it doesn't look like
anything needs to be renamed. so I was wondering if this could be cut and
pasted into the notinlist event or if there was more too it...

http://www.mvps.org/access/forms/frm0015.htm
Click to expand...

The code Dev posted is AN EXAMPLE, appropriate for Dev's database - he
doesn't know the names of your fields or your tables, of course!

I've marked the lines that will need to be customized by putting

<<< comments

before each such line. Watch for word wrap, I'm not going to try to
fix it - just copy Dev's code from the website. And I've NEVER seen
any damaging or deceptive code on mvps.org, it's all top-notch stuff.

<<<< the cbxAEName part of this will be the Name property of your
combo box; Access will create this line automatically

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

<<<< Presumably you'll hae some other desriptive text to prompt the
user; AE Name is meaningful to Dev's database, probably not to yours

strMsg = "'" & NewData & "' is not an available AE Name " & vbCrLf
& vbCrLf

<<<< Likwise DLSAF

strMsg = strMsg & "Do you want to associate the new Name to the
current DLSAF?"
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

<<<< replace tblAE with the name of the table containing the data you
want to add

Set rs = db.OpenRecordset("tblAE", dbOpenDynaset)
On Error Resume Next
rs.AddNew

<<<< Replace AEName with the name of the field that you want to add

rs!AEName = 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


John W. Vinson[MVP]
 
Back
Top