Printing a report and sending out e-mails whilst updating a table...

[scliffe]

Hi all,

This may be obvious, but the following code gives me an error of "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done". The code seems to trip up on the txtAdmissionsNumber (emboldened below) having played around with breakpoints. Any suggestions as to where I need to investigate further? The work flow is as follows:

Select person on a form. Click to bring up from on which this button resides to request additional info.

The report worked no problem before I added in the e-mail and add info to table but I may be conflicting something here... Any thoughts?

Simon

Private Sub btnStudentCoCrpt_Click()
On Error GoTo Err_btnStudentCoCrpt_Click

'check to see all is completed
If IsNull(cmbRequested) = True Then
MsgBox "Please complete the 'Requested By' Field", , "Missing : Requested By"
cmbRequested.SetFocus
Exit Sub
End If

If IsNull(cmbAuthorised) = True Then
MsgBox "Please complete the 'Authorised By' Field", , "Missing : Authorised By"
cmbAuthorised.SetFocus
Exit Sub
End If

If IsNull(txtChanges) = True Then
MsgBox "Please let us know what changes you want to make!", , "Missing : Changes"
txtChanges.SetFocus
Exit Sub
End If

'herd cats
If MsgBox("You are printing a course change form for " & Trim(Called) & _
". This form must be signed by all relevant parties before being returned to MIS for processing. " & _
"An email will be sent to inform people that this course change form has been raised. " & _
"If you wish to cancel this request, please click 'No'", vbYesNo, "Warning") = vbNo Then

Exit Sub

End If

'update table
Set db = New Connection
db.Provider = "SQLOLEDB"
db.ConnectionString = "user id = admin; data source = cedar; initial catalog=KIM;password=huddersfield"
db.Open

Set rec = New Recordset

strsql = "SELECT * FROM tbl_course_change_leaver"
rec.Open (strsql), db, adOpenDynamic, adLockOptimistic

rec.AddNew
rec!AdNo = txtAdmissionsNumber
rec!description = txtChanges
rec!RaisedBy = cmbRequested
rec!authorisedby = cmbAuthorised
rec!Date = Now()
rec!Type = "CC"

rec.AddNew

'send email

 

[kjv1611]

Starting point:

http://support.microsoft.com/kb/269495

Just a thought:
Have you tried:

    rec.Fields("AdNo") = txtAdmissionsNumber  
    rec.Fields("description") = txtChanges
    rec.Fields("RaisedBy") = cmbRequested
    rec.Fields("authorisedby") = cmbAuthorised
    rec.Fields("Date") = Now()
    rec.Fields("Type") = "CC"

instead of

    rec!AdNo = txtAdmissionsNumber  
    rec!description = txtChanges
    rec!RaisedBy = cmbRequested
    rec!authorisedby = cmbAuthorised
    rec!Date = Now()
    rec!Type = "CC"

Also, I notice you have:
rec.AddNew

Listed twice. You should have it like this:

rec.AddNew
[green]'~Other code in between[/green]
rec.Update [green]'(instead of the second .AddNew)[/green]

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Also, I just noticed that you are trying to open a SQL Query to add records to. In order to add the records, you've got to open the underlying table as the recordset for adding new records.

--

"If to err is human, then I must be some kind of human!" -Me

 

[scliffe]

I should have mentioned that there is more code below what I posted (but shouldn't be an issue at this point because its not getting that far). The code worked in a previous database and I've renamed everything correctly so the adding of records etc should work. As I said, it is likely to be something too obvious but it still eludes me...

 

[kjv1611]

Well, I'd start with taking out one of the .AddNew listings, and replacing with .Update - unless there is some code missing in between.

Here is an example from the MS Access help file to clarify:

   With rstTemp
      [highlight].AddNew[/highlight]
      !FirstName = strFirst
      !LastName = strLast
      [highlight].Update[/highlight]
      .Bookmark = .LastModified
   End With

--

"If to err is human, then I must be some kind of human!" -Me

 

[scliffe]

Well, I knew that I was going to feel stupid... I thought I'd checked everything, but having put the db on the backburner a while (other priorities) I came back it with a fresh impetus. Stupid me forgot to check the values. Seems I missed a hidden column. Now, to the next error!