FindFirst

Q

QB

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?

QB
 
S

Stefan Hoffmann

hi,
rs![ParaValue] = Me.TxtKey.Text
Use TxtKey.Value, then you don't need the .SetFocus.

And instead of using a record set I would use a SQL statement, e.g.

Dim db As DAO.Database
Dim SQL As String

Set db = CurrentDb
SQL = "UPDATE tbl_Parameters " & _
"SET ParaValue = '" & Replace(TxtKey.Value, "'", "''") & "' " & _
"WHERE ParaName = 'Registry';"
db.Execute SQL, dbFailOnError
MsgBox db.RecordsAffected
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
Is it also a DAO.Recordset or do you have omitted it in the failing code?



mfG
--> stefan <--
 
D

Dirk Goldgar

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.
 
Q

QB

Thank you for the detailed explanation!

QB




Dirk Goldgar said:
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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top