HELP forum friends!

[vbuser1976]

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

[smh]

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

[flint]

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

[vbuser1976]

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

[mgoarrow]

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!

[vbuser1976]

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?

[smh]

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

smh

[vbuser1976]

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.

[vbuser1976]

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.