Update table with VBA

  • Thread starter Thread starter kentexcel
  • Start date Start date
K

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
 
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 <--
 
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.
 
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
'====================================================
 
Back
Top