[AccessD] Updating a specific record using VBA.

In addition to using the methods suggested by others you could (air code):


'No recordset needed
Dim db as dao.database
Dim sql as string

Set db=currentdb()

'the following does not handle quotes in the text fields
Sql="UPDATE tablename " & _
	"SET APLIDLoadedNumber = " & lngAPLID & ", " & _
	     " APLIDLoadedDesc = '" & strProgName & ', " & _
	     " APLIDasString = '" & strAPLID & " " & _
	"WHERE KeyID = " & lngMyKeyID & ";"

Db.execute sql,dbfailonerror

Db.close
Set db=nothing

Bobby

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl Collins
Sent: Wednesday, October 22, 2008 8:29 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Updating a specific record using VBA.

Bah! I have asked this before but I cannot find the answer in the archives.
and it is sooo darn simple too.

Sorry for repeating this.

I have code like this which writes new records into a table - easy:
'---------------------------------------------------------------------------
-----------------------------
    Set gDAOrs = gDAOdb.OpenRecordset("tmpUseOneProdCode", dbOpenTable)

    With gDAOrs
        .AddNew
        .Fields("APLIDLoadedNumber").Value = lngAPLID
        .Fields("APLIDLoadedDesc").Value = strProgName
        .Fields("APLIDasString").Value = strAPLID
        .Update
    End With

'---------------------------------------------------------------------------
-----------------------------

What I want is to update an existing record, rather than add a new one.
Something Like:

    With gDAOrs
        .Update KeyID = lngMyKeyID
        .Fields("APLIDLoadedNumber").Value = lngAPLID
        .Fields("APLIDLoadedDesc").Value = strProgName
        .Fields("APLIDasString").Value = strAPLID
        .Update
    End With.

Can someone please help with the syntax on this.

Cheers
Darryl.

This email and any attachments may contain privileged and confidential
information
and are intended for the named addressee only. If you have received this
e-mail in
error, please notify the sender and delete this e-mail immediately. Any
confidentiality, privilege or copyright is not waived or lost because this
e-mail
has been sent to you in error. It is your responsibility to check this
e-mail and
any attachments for viruses.  No warranty is made that this material is free
from
computer virus or any other defect or error.  Any loss/damage incurred by
using this
material is not the sender's responsibility.  The sender's entire liability
will be
limited to resupplying the material.


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com