Recordsets

[Dachande11]

Hello to anyone who can help,

I am trying to update a table from VBA using another recordsets output as the criteria to select which field will be updated. The output is also used as the criteria to select what data is used to update this field.

My objective is to update 12 columns in a table in turn (these are periods in a year). I am trying at the moment to just update 1 column and then use the loop to be able to do all of them in turn but for the moment I cannot even get that working (although it has worked before about 5 times but then stopped).

I have attached the code below and would appreciate any help as I am fairly new to recordsets in VBA.

Private Sub Update_SAC_Mar_Click()
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim Per As Variant

On Error GoTo proc_err

Set conn = CurrentProject.Connection

Set rst = New ADODB.Recordset

rst.Open "Periods", _
conn, adOpenForwardOnly, adLockReadOnly, adCmdTableDirect

'Do Until rst.EOF

'rst.MoveNext
Per = rst!Period


conn.Execute "UPDATE SAC_All_Period INNER JOIN Sales_SAC_Period_Channel " & _
"ON SAC_All_Period.SAC = Sales_SAC_Period_Channel.SAC SET SAC_All_Period." & Per & " = [niv] " & _
"WHERE (((Sales_SAC_Period_Channel.Period)like " & Per & "));"


'Loop

rst.Close

Set rst = Nothing

proc_exit:
Exit Sub

proc_err:
MsgBox Err.Description, vbCritical
Resume proc_exit

End Sub

Thanks

Mark

 

[Jon K]

What is [niv]?

I often try to make sure the SQL statement is correct before running an update query. I would put a text box on the form and output the SQL statement to the text box to see if the statement is correct.
.............
.............
Dim SQL As String

SQL="UPDATE SAC_All_Period INNER JOIN Sales_SAC_Period_Channel " & _
"ON SAC_All_Period.SAC = Sales_SAC_Period_Channel.SAC SET SAC_All_Period." & Per & " = [niv] " & _
"WHERE (((Sales_SAC_Period_Channel.Period)like " & Per & "));"

Me.txtSQL = SQL

'conn.Execute SQL
.............
.............

 

[Dachande11]

Hello Jon,

[NIV] is the field I want to use to update my other fields in turn with. i.e my first recordset will find the value of "P01" and pass this to per and then I will update the field [SAC_All_Period.p01] (per) with [niv] where [Sales_SAC_Period_Channel.Period] like P01 (per).

I have output the first recordset to a textbox and that works fine and when I just insert the criteria into the update query that works, so I think the problem lies in the variant Per.

I hope I have made some sense.

Thanks for your help.

Mark

 

[Jon K]

Since P01 is text, you need to put Per in single quotes:-

"WHERE (((Sales_SAC_Period_Channel.Period) like '" & Per & "'));"

 

[Dachande11]

Thanks for your help Jon K, that was the missing link. Everything works fine now.

Mark