QB said:
I have the following code to update a record in a table
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_Parameters")
rs.FindFirst "[ParaName]='Registry'"
If rs.NoMatch Then
MsgBox "A fatal error has occured and the database must close.",
vbCritical
Application.Quit
Else
rs.Edit
Me.TxtKey.SetFocus
rs![ParaValue] = Me.TxtKey.Text
rs.Update
End If
rs.Close
Set rs = Nothing
Set db = Nothing
It works beautifully in 3 other dbs, but for some reason I keep getting an
error
3251 'Operation is not supported for this type of object'
and then it highlights the rs.FindFirst line
Anyone have any idea as to why?
The FindFirst method is not supported for a table-type recordset, which is
what you get by default if you open a recordset on a local table. Maybe in
other databases the table was a linked table, or maybe you opened the
recordset on a query instead of a table.
In this case, you could fix the problem by specifying that you want a
dynaset instead of a table-type recordset, like this:
Set rs = db.OpenRecordset("tbl_Parameters", dbOpenDynaset)
However, it's not really efficient to open a recordset on the whole table,
and then use FindFirst to find and update a single record. It would be much
better to write this:
'----- start of revised code #1 -----
Set db = CurrentDb
Set rs = db.OpenRecordset( _
"SELECT * FROM tbl_Parameters WHERE [ParaName]='Registry'")
With rs
If .EOF Then
MsgBox _
"A fatal error has occured and the database must close.", _
vbCritical, _
"ERROR"
.Close
Application.Quit
Else
.Edit
![ParaValue] = Me.TxtKey
.Update
.Close
End If
Set rs = Nothing
Set db = Nothing
'----- end of revised code #1 -----
And even that is not as efficient as this:
'----- start of revised code #2 -----
Set db = CurrentDb
db.Execute _
"UPDATE tbl_Parameters SET ParaValue=" & _
"'" & Replace(Me.TxtKey, "'", "''") & "'" & _
" WHERE ParaName='Registry'",
dbFailOnError
If db.RecordsAffected = 0 Then
MsgBox _
"A fatal error has occured and the database must close.", _
vbCritical, _
"ERROR"
Application.Quit
End If
Set db = Nothing
'----- end of revised code #2 -----
Note that the above code version #2 assumes that the ParaValue is a text
field.
You might also notice that I took out the business where you set the focus
to Me.TxtKey so that you could retrieve Me.TxtKey.Text. The Text property
of a control has very limited use in Access; mostly you use the Value
property, which is the default property of data-bound controls. To get the
Value property, you don't need to set the focus to the control.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
