'Unspecified Error' Message in NotInList Event Proc

[Chaim]

I"m having a problem with a NotInList event procedure. When a new value is
added to the combo box control list, the following error appears:
Run-time Error '-2147467259 (800004005)'
Unspecified error
The event procedure looks like:
******************************************************
Private Sub cboProjMgr_NotInList (NewData as string, response as integer)
dim oCmd as ADODB.Command, nRows as Integer, _
f as string, m as string, l as string
Call ParseName (NewData, f, m, l)
set oCmd = new ADODB.Command
with oCmd
.ActiveConnection = CurrentProject.Connection
.CommandText = "insert into Personnel (FirstName, MiddleName, " & _
"LastName, RoleID) " & _
"values ('" & f & "', '" & m & "', '" & l &
"', " & _
"(select roleid from roles where rolename =
'PM'))"
.CommandType = adCmdText
.Execute nRows
end with
If nRows = 1 then
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
set oCmd = nothing
End Sub
**********************************************************
The ParseName() function simply breaks a name entered in a variety of ways
into first, middle, and last name.
When the error message appears, selecting Debug highlights the .Execute
nRows line.
The three fields in the table are all declared as text, length 50, with
properties set as:
FirstName MiddleName LastName
Required No No No
Zero Length No Yes No
Indexed No No No
The list is populated by a simple query:
select FirstName & " " & MiddleName & " " & LastName from Personnel
where RoleID = (select RoleID from Roles where RoleName = 'PM')
order by FirstName & " " & MiddleName & " " & LastName;
'Unspecified error' is not particularly enlightening to me. Any suggestions?
I have several very similar such procedures, and they all seem to work just
fine.

TIA

 

[RuralGuy]

I"m having a problem with a NotInList event procedure. When a new
value is added to the combo box control list, the following error
appears:
Run-time Error '-2147467259 (800004005)'
Unspecified error
The event procedure looks like:
******************************************************
Private Sub cboProjMgr_NotInList (NewData as string, response as
integer)
dim oCmd as ADODB.Command, nRows as Integer, _
f as string, m as string, l as string
Call ParseName (NewData, f, m, l)
set oCmd = new ADODB.Command
with oCmd
.ActiveConnection = CurrentProject.Connection
.CommandText = "insert into Personnel (FirstName, MiddleName,
" & _
"LastName, RoleID) " & _
"values ('" & f & "', '" & m & "', '"
& l &
"', " & _
"(select roleid from roles where
rolename =
'PM'))"
.CommandType = adCmdText
.Execute nRows
end with
If nRows = 1 then
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
set oCmd = nothing
End Sub
**********************************************************
The ParseName() function simply breaks a name entered in a variety of
ways into first, middle, and last name.
When the error message appears, selecting Debug highlights the
.Execute nRows line.
The three fields in the table are all declared as text, length 50,
with properties set as:
FirstName MiddleName LastName
Required No No No
Zero Length No Yes No
Indexed No No No
The list is populated by a simple query:
select FirstName & " " & MiddleName & " " & LastName from
Personnel where RoleID = (select RoleID from Roles where RoleName
= 'PM') order by FirstName & " " & MiddleName & " " & LastName;
'Unspecified error' is not particularly enlightening to me. Any
suggestions? I have several very similar such procedures, and they all
seem to work just fine.

TIA

I'm not an ADO guy but my documentation states that: .Execute
RowsAffected returns a Long Integer. Your nRows is not defined as Long.
I don't know if that is the problem or not.

hth

 

[Chaim]

Thanks for the response, RuralGuy. Unfortunately, the nRows is optional. I
get this error even if I simply say .Execute and just assume that it
succeeded (taking out the if...else...end if statements and just setting
Response to acDataErrAdded).