Update MS Access Record from Excel Form with VBA

[JCHuysamer]

Hi All

I am curently updateing my "Excel Database" to a MS Access Database. when you open the Excel Workbook all the data is copied from the database into the Excel workbook.

All new records into the database is via a Excel Form as below.
<code class="bbcode_code">

Sub CaptureProjectsData()
 On Error Resume Next
 Sheets("Captured Data").ShowAllData
 Lastrow = Sheets("Captured Data").Cells(Rows.Count, "A").End(xlUp).Row
 ActiveWorkbook.Connections("Connection").Delete
 '******************************************'
 '* Add lastRow of data to Access database *'
 '******************************************'
 Dim oAcc As Object
 Dim rstTable As Object
 Set oAcc = CreateObject("Access.Application")
 'Open Database in Microsoft Access window
 oAcc.OpenCurrentDatabase "\\rbmfsc\public\EDS Project Execution\Projects.accdb", True
 oAcc.Visible = True
 'Create a Recordset based on "Table name"
 Set rstTable = oAcc.CurrentDb.OpenRecordset("Captured Data")
 With rstTable
   .AddNew
     ![Field1] = Sheets("Captured Data").Range("A" & Lastrow).Value
     ![Field2] = Sheets("Captured Data").Range("B" & Lastrow).Value
     ![Field3] = Sheets("Captured Data").Range("C" & Lastrow).Value
     ![Field4] = Sheets("Captured Data").Range("D" & Lastrow).Value
     ![Field5] = Sheets("Captured Data").Range("E" & Lastrow).Value
     ![Field6] = Sheets("Captured Data").Range("F" & Lastrow).Value
     ![Field7] = Sheets("Captured Data").Range("G" & Lastrow).Value
     ![Field8] = Sheets("Captured Data").Range("H" & Lastrow).Value
     ![Field9] = Sheets("Captured Data").Range("I" & Lastrow).Value
     ![Field10] = Sheets("Captured Data").Range("J" & Lastrow).Value
  .Update
  End With
  oAcc.Quit
  Set oAcc = Nothing
 End Sub

</code>Where i get stuck is when i have to update a a existing record in Table "Project Details"
The data should first be filtered by 2 columns "Clerk of Works" and then "Order Number" - once the correct details remains it needs to update column "PercentComplete" with the new data copied from the Excel Form.

Please see below - this is how far i got but i am now completely stuck

<code class="bbcode_code">

Sub Update_Percent_Complete()
 On Error Resume Next
 ActiveWorkbook.Connections("Connection").Delete
     Dim oAcc As Object
     Dim rstTable As Object
     Dim AccApp As Object
     Dim OrderNumber As String
     Dim COW As String
     
     
     
 Set oAcc = CreateObject("Access.Application")
 'Open Database in Microsoft Access window
    With oAcc
     .OpenCurrentDatabase "\\rbmfsc\public\EDS Project Execution\Projects.accdb", True
     .Visible = True
     .DoCmd.OpenTable "Project Details"
     '.Quit
    End With
 End Sub

</code>