HELP forum friends!

Hi forum buddies!

Please HELP! In my application, I have a form that is basically a data entry screen. Once the user has entered all data requested and clicked save, the form is supposed to save the data in the fields, but I am getting this error when I begin to assign form fields to the fields in SQL:

Run-time error '-2147217887 (80040e21)':
Multiple-step operation generated errors. Check each status value.

Can anyone tell me what this error means, why it occured, and how to solve it? Below is the code I am using just in case you need it.

Thanks again.

Code:

Private Sub ProcessData() 'This procedure processes and saves the inputted data
'to the database
Dim response As Integer
Dim rs As ADODB.Recordset
Dim strSQL As String

txtRepId.Text = "0"
strSQL = "Exec sp_get_report_id " & txtRepId.Text
Set rs = New ADODB.Recordset
Set rs.ActiveConnection = cn
rs.Source = strSQL
rs.CursorLocation = adUseClient
rs.LockType = adLockOptimistic
rs.CursorType = adOpenKeyset
rs.Open , , , , adCmdText

If (txtRepId.Text = 0 Or txtRepId.Text = "") Then
rs.AddNew
End If

With rs
.Fields("AtmId") = txtAtmId.Text 'This is where the cursor stops and gives me above mentioned error.
.Fields("ContactName") = txtContName.Text
.Fields("ContactPhone") = txtContPhone.Text
.Fields("DOccurence") = txtDoccur.Text
.Fields("NOccurence") = txtNature.Text
.Fields("ServiceTech") = lstSvcTech.Text
.Fields("STDate") = txtSvcDate.Text
.Fields("STTime") = txtSvcTime.Text
.Fields("SCrew1") = lstSCrew1.Text
.Fields("SCrew2") = lstSCrew2.Text
.Fields("SCrew3") = lstSCrew3.Text
.Fields("SCDate") = txtSCDate.Text
.Fields("SCTime") = txtSCTime.Text
.Fields("Resolution") = txtResolution.Text
.Fields("Result") = txtResult.Text
.Fields("DateReported") = txtTodaysDate.Text
.Fields("ReportedBy") = txtUserName.Text
.Fields("EmailTo") = lstEmailTo.Text
End With

rs.Update
'txtRepId.Text = rs.Fields("ReportId")
response = MsgBox("Your Report Id is " & rs.Fields("ReportId") & _
".", vbOKOnly, Title:="Your Report Id")
End Sub

I have run into this problem before, but I am having difficulties remembering how I fixed it. What line does VB send you to when it has you debug? Is it the 'With rs' or the line following it, or is a different line? I for some reason think my program was hicupping because if where my 'End If' statement was. Try putting it just before your 'rs.Update'. If this doesn't work, tell me and I will have to think a little more on how I solved it.

smh

To work around this problem, try either of the following:

Use the client side cursor library. In the following code you need to change the cursor location to adUseClient instead of adUseServer.

-or-

Use the Microsoft OLE DB Provider for ODBC Drivers instead of the Microsoft OLE DB Provider SQL Server.

Url where found:
http://support.microsoft.com/support.../Q217/0/19.ASP

hope this helps.

Flint

Thanks for your help, but it is still not working. It's really weird.

For database access I've written several class modules to handle this for me, one for MSAccess databases, one for Oracle databases, etc. I know this would require a little code re-writing on your part but you would then have an object that can be used over and over again. For example the following code would deal with an MSAccess database:

Private dbDatabase As Database
Public recRecordset As Recordset
Private mstrQuery As String

Public Property Let Query(strQuery As String)
mstrQuery = strQuery
End Property

Public Property Get Query() As String
Query = mstrQuery
End Property

Public Sub OpenDB()

Set dbDatabase = Workspaces(0).OpenDatabase _
(App.Path & "\IsoTrack.mdb")
Set recRecordset = dbDatabase.OpenRecordset(Query)

End Sub

Public Sub CloseDB()

On Error Resume Next
recRecordset.Close
dbDatabase.Close

End Sub

Public Sub AddAsNew()

With recRecordset
.AddNew
Save_Members
.Update
End With

End Sub

Private Sub Save_Members()

With recRecordset
.Fields("AreaID") = frmArea.strArea
.Fields("IsometricLineNumber") = frmArea.strIsoNum
.Fields("Size") = frmArea.intPipeDia
.Fields("Spec") = frmArea.strSpec
End With

End Sub

The Save_Members sub could be written outside of the class module and called so that the database object is totally generic. In this instance, my database object is tied to a specific table. Of course, to call the object from your code would look something like this:

In the General Declarations section:

Private DB As New clsAccessDB

In the Form Load event:

Private Sub Form_Load()
Set DB = New clsAccessDB
End Sub

Private Sub cmdSaveToDB_Click()

strQuery = "SELECT LineNumberSize FROM IsoLines WHERE" _
& " LineNumberSize='" & arrNewNumSize(I) & "'"
DB.OpenDB strQuery
strArea = "Blah"
strIsoNum = "WooHoo"
strSize = "Giant"
strSpec = "Asbestos"
DB.AddAsNew
DB.CloseDB

End Sub

I know I didn't really answer your question, but I hope this helps!

Thanks for your input, mgoarrow, I am going to paste your reply to my files so that I can use it in the future. But, I do need a solution.

Thank you any way. Anybody else have any ideas?

What line does VB take you to after showing the error message?

smh

My code above tells you where it stops.

It stops after the .Addnew command i.e.

rs.addnew
with rs
.fields("...")=txtbox1.text =====> this is where it stops.

I hope this doesn't confuse.
Thanks.

I have solved the problem, so therefore, this thread will be closed. Thank you very much for all of your help. What happened was that VB was trying to save to a SQL field of a different datatype. So I went in to my SQL database, changed the datatypes and it worked.

Thank you everyone for all your help.