Update Table Using A Recordset

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to update a field in a different table and having no luck with my
code shown below, does anyone know why this is not working. I get a
Run-time error '3219' "Invalid operation".

Private Sub cmdSave_Click()
Dim db As DAO.Database
Dim rst As DAO.Database
Dim strSQL As String

Set db = CurrentDb

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Set rst = db.OpenRecordset("UPDATE tblDBNotes INNER JOIN tblCollections
ON (tblDBNotes.CustomerNo = tblCollections.xMember) " & _
"AND (tblDBNotes.MCNumber = tblCollections.xMC) SET
tblCollections.HasAdditionalNotes = True " & _
"WHERE (((tblCollections.HasAdditionalNotes)=False) AND
((tblCollections.xMember)=[Forms]![frmDBNotes]![CustomerNo]) " & _
"AND ((tblCollections.xMC)=[Forms]![frmDBNotes]![MCNumber]));")

DoCmd.RunSQL strSQL
rst.Close

DoCmd.Close

End Sub
 
The OpenRecordSet will open a table in select, so you can retrieve
information form it, or update it, add record to it using

Set MyRec = MyDb.OpenRecordSet("Select * Form TableName")
Then you can use
MyRec.Edit
Or
MyRec.AddNew
etc, please check help about OpenRecordSet

-------------------------------
What you are trying to do:

Private Sub cmdSave_Click()
Dim strSQL As String

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

strSQL = "UPDATE tblDBNotes INNER JOIN tblCollections
ON (tblDBNotes.CustomerNo = tblCollections.xMember) " & _
"AND (tblDBNotes.MCNumber = tblCollections.xMC) SET
tblCollections.HasAdditionalNotes = True " & _
"WHERE (((tblCollections.HasAdditionalNotes)=False) AND
((tblCollections.xMember)=[Forms]![frmDBNotes]![CustomerNo]) " & _
"AND ((tblCollections.xMC)=[Forms]![frmDBNotes]![MCNumber]));")

DoCmd.RunSQL strSQL
DoCmd.Close

End Sub
=================================
***** Why do you need to save this value in the table? *******
 
Leslie said:
I am trying to update a field in a different table and having no luck
with my code shown below, does anyone know why this is not working.
I get a Run-time error '3219' "Invalid operation".

Private Sub cmdSave_Click()
Dim db As DAO.Database
Dim rst As DAO.Database
Dim strSQL As String

Set db = CurrentDb

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70

Set rst = db.OpenRecordset("UPDATE tblDBNotes INNER JOIN
tblCollections ON (tblDBNotes.CustomerNo = tblCollections.xMember) "
& _ "AND (tblDBNotes.MCNumber = tblCollections.xMC) SET
tblCollections.HasAdditionalNotes = True " & _
"WHERE (((tblCollections.HasAdditionalNotes)=False) AND
((tblCollections.xMember)=[Forms]![frmDBNotes]![CustomerNo]) " & _
"AND ((tblCollections.xMC)=[Forms]![frmDBNotes]![MCNumber]));")

DoCmd.RunSQL strSQL
rst.Close

DoCmd.Close

End Sub
Click to expand...

Your confusing two different things here. Was this copied and pasted from
multiple sources? You dim strSQL and you attempt toi use it, but you never
assign a string to it. And you don't need the Recordset at all.

RunSQL is for Action queries UPDATE, APPEND and DELETE.

OpenRecordset is for SELECT queries where you want to work with the returned
data in your code.

Just use RunSQL with your SQL statement as a string. No need at all for the
Recordset here.
 
Leslie said:
I am trying to update a field in a different table and having no luck with my
code shown below, does anyone know why this is not working. I get a
Run-time error '3219' "Invalid operation".

Private Sub cmdSave_Click()
Dim db As DAO.Database
Dim rst As DAO.Database
Dim strSQL As String

Set db = CurrentDb

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Set rst = db.OpenRecordset("UPDATE tblDBNotes INNER JOIN tblCollections
ON (tblDBNotes.CustomerNo = tblCollections.xMember) " & _
"AND (tblDBNotes.MCNumber = tblCollections.xMC) SET
tblCollections.HasAdditionalNotes = True " & _
"WHERE (((tblCollections.HasAdditionalNotes)=False) AND
((tblCollections.xMember)=[Forms]![frmDBNotes]![CustomerNo]) " & _
"AND ((tblCollections.xMC)=[Forms]![frmDBNotes]![MCNumber]));")

DoCmd.RunSQL strSQL
rst.Close

DoCmd.Close

End Sub
Click to expand...

I think this should be

Dim rst As DAO.Recordset

And by the way, instead of

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

you could try to use

Me.Dirty = False

(assuming that this code is behind a form)

HTH
Matthias Kläy
 
Thank you to both of you. I did first search for help on the recordset and
wasn't finding what I thought I needed, now I know. I did as you suggested
taking out the recordset info and just running the sql....worked perfect.
Too much thinkin, it's time for drinkin. Have a good one!
--
Leslie


Rick Brandt said:
Leslie said:
I am trying to update a field in a different table and having no luck
with my code shown below, does anyone know why this is not working.
I get a Run-time error '3219' "Invalid operation".

Private Sub cmdSave_Click()
Dim db As DAO.Database
Dim rst As DAO.Database
Dim strSQL As String

Set db = CurrentDb

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70

Set rst = db.OpenRecordset("UPDATE tblDBNotes INNER JOIN
tblCollections ON (tblDBNotes.CustomerNo = tblCollections.xMember) "
& _ "AND (tblDBNotes.MCNumber = tblCollections.xMC) SET
tblCollections.HasAdditionalNotes = True " & _
"WHERE (((tblCollections.HasAdditionalNotes)=False) AND
((tblCollections.xMember)=[Forms]![frmDBNotes]![CustomerNo]) " & _
"AND ((tblCollections.xMC)=[Forms]![frmDBNotes]![MCNumber]));")

DoCmd.RunSQL strSQL
rst.Close

DoCmd.Close

End Sub
Click to expand...

Your confusing two different things here. Was this copied and pasted from
multiple sources? You dim strSQL and you attempt toi use it, but you never
assign a string to it. And you don't need the Recordset at all.

RunSQL is for Action queries UPDATE, APPEND and DELETE.

OpenRecordset is for SELECT queries where you want to work with the returned
data in your code.

Just use RunSQL with your SQL statement as a string. No need at all for the
Recordset here.
Click to expand...
 
Back
Top