[AccessD] Update an existing record in an Access Table using VBA 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