Calling sub from NotInList

[thart21]

Could someone please tell me what is wrong with this module code?

I have a subform with 9 staff name fields that I want to call this sub procedure for each using OnNotInList instead of typing the code in for each field.

I also want to use it in the same way for a staff name field in my main form.

My public module name is "StaffAdd"

I have tried:

1) Private Sub fieldname_NotInList(NewData As String, Response As Integer)
Call addnew(Me) (tried with and w/out "Me")
End Sub

2)Private Sub fieldname_NotInList(NewData As String, Response As Integer)
Call StaffAdd.addnew
End Sub

3)Private fieldname_NotInList(NewData As String, Response As Integer)
Call addnew(strfirst, strLast)
End Sub




Public Sub AddName()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strfirst As String
Dim strLast As String

Set db = CurrentDb
Set rs = db.OpenRecordset("Staff")
strfirst = Right(NewData, Len(NewData) - InStr(1, NewData, ",") - 1)
strLast = Left(NewData, InStr(1, NewData, ",") - 1)

With rs
.addnew
.Fields("last") = strLast
.Fields("first") = strfirst
.Update
End With

Response = acDataErrAdded

End Sub

I am not getting an error message and the new name I typed is in my project details table as it should be, it just isn't creating a new record in my Staff table. ??????

Thanks!

 

[WayneRyan]

Toni,

Call with:

Private Sub fieldname_NotInList(NewData As String, Response As Integer)
   AddName(NewData)
   Response = acDataErrAdded ' <-- Get rid of error message
End Sub

Add an argument to the Sub:

Public Sub AddName(strNewName As String)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strfirst As String
Dim strLast As String

Set db = CurrentDb
Set rs = db.OpenRecordset("Staff")
strfirst = Right(strNewName, Len(strNewName) - InStr(1, strNewName, ",") - 1)
strLast = Left(strNewName, InStr(1, strNewName, ",") - 1)

With rs
.addnew
.Fields("last") = strLast
.Fields("first") = strfirst
.Update
End With

Response = acDataErrAdded

End Sub

Wayne

 

[thart21]

Thanks Wayne, I 'm getting an "Item not in list" error message on this-any ideas why?

 

[thart21]

Got it figured out - I just had a "typo" in my code. You're my hero!!! Thanks!!

 

[WayneRyan]

Toni,

I thought that's what the:

Response = acDataAdded

was supposed to take care of.

Do a search here for "NotInList". You have to set Response to something
(I thought it was acDataErrAdded).

I'll look too.

Wayne

 

[WayneRyan]

Toni,

Mile's FAQ:

http://www.access-programmers.co.uk/forums/showthread.php?t=61414

Wayne