Update table not in recordsource

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TM - 12 Jan 2004 05:56 GMT

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!

Reply to this Message

John Vinson - 12 Jan 2004 06:40 GMT

>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 W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Reply to this Message

TM - 12 Jan 2004 16:04 GMT

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-----
>
[quoted text clipped - 27 lines]
>http://go.compuserve.com/msdevapps?loc=us&access=public
>.

Reply to this Message

John Vinson - 12 Jan 2004 18:49 GMT

>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,
[quoted text clipped - 8 lines]
>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>

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Reply to this Message