[AccessD] Update an existing record in an Access Table usingVBA code

Darryl Collins Darryl.Collins at coles.com.au
Mon May 26 21:06:20 CDT 2008



Stuart!

That is great :) Once again you have help enormously.  Many thanks.  It is kinda frustrating as I know what I want to do in aircode, but get tripped up by Syntax - aaah, pactise practise practise.

warm regards
Darryl.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Stuart
McLachlan
Sent: Tuesday, 27 May 2008 12:02 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Update an existing record in an Access Table
usingVBA code


...

With gRS
strCriteria =  "KeyFYP_ID = " & Sheet13.Range("B" & glLR).Value
.Findfirst strCriteria
.Edit
!Product = Sheet13.Range("C" & glLR).Value
!Phase = Sheet13.Range("E" & glLR).Value
!Owner = Sheet13.Range("F" & glLR).Value
!OwnerData = Sheet13.Range("G" & glLR).Value
.Update
End With
...

Cheers,
Stuart

On 27 May 2008 at 11:01, Darryl Collins wrote:

> 
> 
> 
> Hi guys n gals.
> 
> I use the following code to write data from Excel to Access and it works great.
> 
> For Each grCEL In Sheet13.Range("nrTempExportLoop")
>     Application.StatusBar = "Writing to Forecast Archive Database: " & grCEL.Row & " of " & Sheet13.Range("nrTempExportLoop").Rows.Count
>     glLR = grCEL.Row
>     With gRS
>         .AddNew
>         .Fields("KeyFYP_ID") = Sheet13.Range("B" & glLR).Value
>         .Fields("Product") = Sheet13.Range("C" & glLR).Value
>         .Fields("Phase") = Sheet13.Range("E" & glLR).Value
>         .Fields("Owner") = Sheet13.Range("F" & glLR).Value
>         .Fields("OwnerData") = Sheet13.Range("G" & glLR).Value
>        
>         <yada yada yada>	
> 
>         .Update
>     End With
> Next grCEL
> 
> 
> However... rather than writing a new record, I want to find an existing record in the Access table and update the fields with new data.
> 
> With gRS
>         .AddNew ' < -- I want this to be UpdateExisting 
> 
> I guess I need to use the KeyID to locate the record in the table, and then update the fields, but I am not sure how to go about this?
> Anyone got any pointers?
> 
> 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


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

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.




More information about the AccessD mailing list