Update table not in recordsource

[TM]

I would like to use the "update" VB statement. Is this a
valid VBA statement?? I am a novice in VBA and have
books on VB, but haven't really used it! I would like to
update a couple of Y/N fields on a table, based on
certain actions taken on a form. Is there a way, like
dlookup, I can update a table which is not part of the
current recordset??

THANKS!

 

[John Vinson]

Is there a way, like
dlookup, I can update a table which is not part of the
current recordset??

Yes, but not with a single statement - more like a dozen! You'll need
to open a Recordset; find the record that you want to update (unless
the Recordset is based on a query which retrieves only that record);
use the Edit method of the recordset object to open the record for
editing; set the value of the field; and use the Update method. Air
code:

Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("tblXYZ", dbOpenTable)
iKey = 123
rs.FindFirst "[KeyField] = " & iKey ' find a record by a numeric field
If rs.NoMatch Then
' error condition, no record found - warn the user
Else
rs.Edit ' open the recordset for editing
rs!FieldX = "Hello World"
rs.Update ' write out the edited record
End If
rs.Close ' clean up after yourself
Set rs = Nothing

 

[TM]

Thanks! I'll try that out... but I found also
the "update" statement, which used

update tblName
set field - value
where conditional statement

Any further information on this??? I tried it, but got
errors, when I had it one three separate lines like this,
I got an error that 'tblName' was not defined. Then I
tried to move it to one line and I got an error on "set"
something like "(" expected... I don't remember the exact
error. But if there is a reference of some sore form
more information I would appreciate it. I didn't find
anything on Knowledge base or google.

Thanks for any further information! For now, I'll
probably use what you have, but I would like to learn
more about the other!

THANKS AGAIN!

-----Original Message-----

Is there a way, like
dlookup, I can update a table which is not part of the
current recordset??

Yes, but not with a single statement - more like a dozen! You'll need
to open a Recordset; find the record that you want to update (unless
the Recordset is based on a query which retrieves only that record);
use the Edit method of the recordset object to open the record for
editing; set the value of the field; and use the Update method. Air
code:

Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("tblXYZ", dbOpenTable)
iKey = 123
rs.FindFirst "[KeyField] = " & iKey ' find a record by a numeric field
If rs.NoMatch Then
' error condition, no record found - warn the user
Else
rs.Edit ' open the recordset for editing
rs!FieldX = "Hello World"
rs.Update ' write out the edited record
End If
rs.Close ' clean up after yourself
Set rs = Nothing


.

 

[John Vinson]

Thanks! I'll try that out... but I found also
the "update" statement, which used

update tblName
set field - value
where conditional statement

This is a SQL query, not VBA code. Trying to execute this as if it
were VBA geht eben so schlecht als switching to German in the middle
of an English sentence!

Any further information on this??? I tried it, but got
errors, when I had it one three separate lines like this,
I got an error that 'tblName' was not defined. Then I
tried to move it to one line and I got an error on "set"
something like "(" expected... I don't remember the exact
error. But if there is a reference of some sore form
more information I would appreciate it. I didn't find
anything on Knowledge base or google.

Thanks for any further information! For now, I'll
probably use what you have, but I would like to learn
more about the other!

You can create and execute a SQL Query in several ways: e.g.

DoCmd.RunSQL "UPDATE tblName SET Fld1 = 'Some Text' WHERE fld2 = 3;"

or (preferably since it traps errors)

Dim strSQL As String
strSQL = "UPDATE tblName SET Fld1 = 'Some Text' WHERE fld2 = 3;"
Dim db As DAO.Database
Dim qd As DAO.Querydef
Set db = CurrentDb
Set qd = db.CreateQuerydef(strSQL)
On Error GoTo Proc_Error
qd.Execute dbFailOnError
....

Proc_Error:
<your error handling code here>