I want to update a table using VBA

[Guest]

I'm trying to update a table using a for...netx statement. How can I do it?

 

[Ken Snell [MVP]]

You're going to have to give us a lot more details than this.....

--

Ken Snell
<MS ACCESS MVP>

"I want to update a table using VBA" <I want to update a table using
(e-mail address removed)> wrote in message
news:[email protected]...

 

[Guest]

Heres some code that will show how its done. This extracts values from one
table and implants them into another

****************************
Public Function ReadRecsfromXL2()
On Error GoTo EH

Dim db As Database
Dim rstXL As Recordset
Dim rstACC As Recordset
Dim strClientID As String
Dim strClientName As String
Dim strCO As String
Dim strPolicy As String
Dim strCov As String
Dim strTransaction As String

'Set up your recordsets
Set db = CurrentDb()
'Change the name 'XLTableName' to whatever your Linbked XL table called
Set rstXL = db.OpenRecordset("P9Current", dbOpenSnapshot)
'Create a Table called 'tblXLImport' (See below for field names)
Set rstACC = db.OpenRecordset("tblXLImport", dbOpenDynaset)
rstXL.MoveFirst

'If there's nothing in the XL spreaddie, don't bother trying to load it
If rstXL.EOF Then Exit Function

'make sure you move to the start of recordsets, probably not necessary
but good practice
'rstXL.MoveFirst

'Monitor for the end of the XL table as you loop thru its records
Do While Not rstXL.EOF

With rstACC
'replace with .AddNew with .Edit for when you are modifying
existing records
.AddNew
![ClientName] = rstXL![Client Name]
![ClientID] = rstXL![Client ID]
![co] = rstXL![co]
![Policy] = rstXL![Policy]
![Cov] = rstXL![Cov]
![Transaction] = rstXL![Transaction]
![RptDate] = rstXL![RPT Date]
![FaceAmount] = rstXL![Face Amount]
.Update
End With

If Not rstXL.EOF Then
rstXL.MoveNext
End If

Loop

'Close the recordsets
rstXL.Close
rstACC.Close

EH:
End Function
'End Code
**************************************