What's wrong with this code?

[Lee]

Hi everyone,
I'm getting Error Code 3251 saying that the operation is
not supported for this type of object.
I'm to trying add some text to a field called CentreNotes
in the table tbl_Centres.
Can you help?

Dim varReport As Variant
Dim stDocName As String
Dim intResponse As Integer
Dim db As Database
Dim rs As Recordset
Dim stChunk As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("tbl_Centres")

stChunk = vbCrLf & "Standard Letter 4 (security
confirmed) sent " & Date

varReport = Me!txtReportID

intResponse = MsgBox("You are about to produce Standard
Letter 4 (security confirmed) for Centre " &
Me.txtCentreNumber & "." _
& vbCrLf & vbCrLf & "Do you want to
continue?", vbQuestion + vbYesNo +
vbDefaultButton2, "Continue?")
If intResponse = vbNo Then
Exit Sub
Else
DoCmd.Echo False
DoCmd.SetWarnings False
chkLetter4 = True
DoCmd.OpenQuery "qry_TempLetterSecConfirmed",
acViewNormal, acAdd
DoCmd.OpenQuery "qry_AppendLetter", acViewNormal,
acAdd
DoCmd.SelectObject acForm, "frm_InspectionReports",
False
rs.FindFirst "CentreNo=" & txtCentreNumber
With rs
.Edit
!CentreNotes.AppendChunk stChunk
.Update
End With
DoCmd.Requery
DoCmd.GoToControl "txtReportID"
DoCmd.FindRecord varReport, acEntire, False,
acSearchAll, True, acCurrent, True
DoCmd.GoToControl "Page2"
DoCmd.SetWarnings True
DoCmd.Echo True

stDocName = "rpt_SecurityConfirmed"
DoCmd.OpenReport stDocName, acPreview
DoCmd.Maximize

rs.Close

End If

Thanks for your help.

Lee

 

[Wayne Morgan]

Just an initial guess, is your version of Access 2000 or newer? Try changing
two of your Dim statements.

Dim db As DAO.Database
Dim rs As DAO.Recordset

If these won't compile, in the code editor go to Tools|References. Scroll
down the list and check Microsoft DAO 3.6 Object Library.

 

[Lee]

I'm using Access 97. I changed the Dim statements as you
suggested anyway but it doesn't help I'm afraid.

Regards,

Lee

 

[Wayne Morgan]

Lee, which line of code is generating the error? You may have to disable
your Echo False and any error handler you have to find it.

Why are you opening the queries and selecting the form?

Changing the Dim statements won't make a difference in Access 97, good or
bad. If you move to 2000 or newer though, you'll want to do that to prevent
confusion with ADO.

 

[Lee]

The line that starts: rs.FindFirst....

The queries are action queries and are adding information
details of the standard letter to a table.
I've selected the form as, after the append chunk has
run, I want the focus to return to the form (and then a
control) for the FindRecord procedure to locate the
record I was focused on prior to the queries being
actioned.

It's a bit of a mess isn't it!!

Lee

 

[Wayne Morgan]

Judging by the name in the line

Set rs = db.OpenRecordset("tbl_Centres")

I suspect that tbl_Centres is a table. Since you aren't specifying a type of
recordset, Access will open a table type recordset, if possible. A table
type recordset doesn't have a FindFirst property, it uses Seek instead. Try
amending the above line to

Set rs = db.OpenRecordset("tbl_Centres", dbOpenDyanset)

You say that the queries you are opening are Action Queries. You have
included acAdd in the line. This is only effective on queries opened in
datasheet view to limit what the user can do by typing into the query. It
shouldn't have an effect on an Action Query. Also, instead of turning
warnings on and off, you may prefer the following syntax to run the queries.

db.Execute "qry_AppendLetter", dbFailOnError

The last item will cause an error message if the query fails. Also, the code
appears to be running on the form that you want to set the focus back to so
that you can select a control. If this is the case, hopefully by not
"opening" the queries, the form won't lose the focus. Also, you may prefer
replacing some of the DoCmd calls, although if they're working, that's the
first thing to consider.

For example:
DoCmd.Requery
Me.Requery

DoCmd.GoToControl "txtReportID"
Me.txtReportID.SetFocus

I believe the two mentioned here are available in Access 97, it's been
awhile since I've used 97.