Update table with VBA

[kentexcel]

Hi
I amtrying to Update a table with fields Year=1 and field Month shall update
with 1 to 12.
My code looks like this but it don´t work .

*********************************************************
Private Sub cmdNyttÃ…r_Click()

'********************************************************************************

Dim rst As ADODB.Recordset
Dim rstUppdatera As ADODB.Recordset
Dim strMessage As String
Dim strSQL As String, intÃ…rsID As Integer
Dim x As Variant, i As Integer
Set rst = New ADODB.Recordset
Set rstUppdatera = New ADODB.Recordset
' On Error GoTo Felhanteraren

'Skapa SQLuttryck för recordset
strSQL = "SELECT * FROM tblmedlem"

rstUppdatera.Open strSQL, CurrentProject.Connection, adOpenKeyset, _
adLockOptimistic


rst.Open "tblBetalningar", CurrentProject.Connection, adOpenKeyset, _
adLockOptimistic, adCmdTableDirect
intÅrsID = InputBox("Ange årsID. Årsid är årtal +1. Ex: År 2009 har
Ã¥rsid 10")
'Loopa igenom hela postuppsättningen
Do While Not rstUppdatera.EOF

With rst
.AddNew
.Fields("MedlemsID") = rstUppdatera.Fields("MedlemsID").Value
.Fields("Ã…rsID") = intÃ…rsID
For i = 1 To 12
.Fields("MÃ¥nadsID") = i
rstUppdatera.MoveNext
Next
rstUppdatera.MoveNext
End With
'Uppdatera
Loop

'rst.Close
Set rst = Nothing
End
Felhanteraren:
MsgBox "Ett fel uppstod", vbInformation
End Sub

Thanks for any helpor guidance

 

[Stefan Hoffmann]

hi,

Hi
I amtrying to Update a table with fields Year=1 and field Month shall update
with 1 to 12.
My code looks like this but it don´t work .
With rst
.AddNew
.Fields("MedlemsID") = rstUppdatera.Fields("MedlemsID").Value
.Fields("Ã…rsID") = intÃ…rsID
For i = 1 To 12
.Fields("MÃ¥nadsID") = i
rstUppdatera.MoveNext
Next
rstUppdatera.MoveNext
End With

The For loop makes no sense at all. Can you post your table structure
and describe what you want to do?

mfG
--> stefan <--

 

[kentexcel]

Hi and thanks for your quick reply

The table tblMedlem consists of fields Ã…rsID and MonthID.

Every year i need to add the new year (Ã…rsID) and all months (MÃ¥nadsID) in
the table.
This means that i need to update 12 records for person in tblMedlem.

Before this was done manualy, but now there are too many records so we need
to update automatically.

 

[John Spencer]

Perhaps what you need is the following

With rst
For i = 1 to 12
.AddNew
.Fields("MedlemsID") = rstUppdatera.Fields("MedlemsID").Value
.Fields("Ã…rsID") = intÃ…rsID
.Fields("MÃ¥nadsID") = i
.Update
Next i
End With

rstUppdatera.MoveNext

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================