Can I use a RecordSetClone in SQL

[G Gerard]

Hello

Can I write an SQL statement in code using a RecordSetClone of an open form?

something like:
MySQL = "UPDATE Me!Child0.Form.RecordSetClone SET
Me!Child0.Form.RecordSetClone.MyField = -1:"
DoCmd.RunSQL MySQL

Note: in the above example MyField is the Control Source of a Textbox in
Me!Child0 subform
(The above example gives me "Syntax error in Update statement" )

If it is possible to use a RecordSetClone in an SQL how would I write the
statement?

Thanks
G.Gerard

[Lyle Fairfield]

No.

[CDMAPoster]

G Gerard wrote:

Hello

Can I write an SQL statement in code using a RecordSetClone of an open form?

something like:
MySQL = "UPDATE Me!Child0.Form.RecordSetClone SET
Me!Child0.Form.RecordSetClone.MyField = -1:"
DoCmd.RunSQL MySQL

Note: in the above example MyField is the Control Source of a Textbox in
Me!Child0 subform
(The above example gives me "Syntax error in Update statement" )

If it is possible to use a RecordSetClone in an SQL how would I write the
statement?

Thanks
G.Gerard

Here is some code I tried behind a form:

'-------Begin code behind form
Private Sub cmdRename_Click()
Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim strSQL As String

Set MyDB = CurrentDb
strSQL = "SELECT EFirstName FROM tblEmployees WHERE EID = 1;"
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenDynaset)

Call UpdateRecordset(MyDB, MyRS)
MyRS.Close
Set MyRS = Nothing
Set MyDB = Nothing
Me.Requery
MsgBox ("Done.")
End Sub

Private Sub UpdateRecordset(MyDB1 As DAO.Database, MyRS1 As
DAO.Recordset)
If MyRS1.Fields(0) = "Joe" Then
MyRS1.Edit
MyRS1(MyRS1.Fields(0).Name) = "Frank"
MyRS1.Update
End If
End Sub
'-------End code behind form

Note: I get to reuse a table from a different example.

tblEmployees
EID AutoNumber
EFirstName Text
ELastName Text

EID EFirstName ELastName
1 Joe Young
2 King Kong
3 The Hulk

After clicking on the command button the table becomes:

EID EFirstName ELastName
1 Frank Young
2 King Kong
3 The Hulk

The A97 help file states that the RecordsetClone is read-only so you
can't use it directly to update records.

I decided to make sure that bound values would not interfere with the
edit. I set the RecordSource of the form to tblEmployees and had a
textbox with a ControlSource bound to the EFirstName field. I changed
'Frank' back to 'Joe' in the table, then opened the form and clicked
the command button. The name in the textbox changed to 'Frank' just
before the 'Done.' message popped up.

James A. Fortune
CD********@FortuneJames.com

If a police car, an ambulance, a fire truck and a mail truck all meet
at an intersection the mail truck has the right of way. -- James Allen

[RoyVidar]

G Gerard wrote in message
<i6********************@ursa-nb00s0.nbnet.nb.ca> :

Hello

Can I write an SQL statement in code using a RecordSetClone of an
open form?

something like:
MySQL = "UPDATE Me!Child0.Form.RecordSetClone SET
Me!Child0.Form.RecordSetClone.MyField = -1:"
DoCmd.RunSQL MySQL

Note: in the above example MyField is the Control Source of a Textbox
in Me!Child0 subform
(The above example gives me "Syntax error in Update statement" )

If it is possible to use a RecordSetClone in an SQL how would I write
the statement?

Thanks
G.Gerard

I think it should be possible to do something like this

strSql = "UPDATE " & Me!Child0.Form.RecordSetClone.Name & _
" SET MyField = -1"
currentdb.execute strSql, dbfailonerror
Me!Child0.Form.Requery

as long as the form recordset is DAO and the recordsource specifies a
table or query which is updateable (not an SQL string).

Or do you also wish to pick up the controlsource dynamicly?

strSql = "UPDATE " & Me!Child0.Form.RecordSetClone.Name & " Set " & _
Me!Child0.Form.Controls("MyField").Controlsource & " = -1"

--
Roy-Vidar