How to use a recordset

[Dachande11]

Hello all,

I am trying to find a way of creating a query in code and to pass the result of this query to the variable 'Per_Num'.

I wondered if this could be done using a recordset? I have looked through other topics but am unsure how to use the recordset.
Any help would be much appreciated, I pasted the core bit of the current code below.

Thanks


Private Sub Update_SAC_Click()

Dim cn As ADODB.Connection
Set cn = CurrentProject.Connection

cn.Execute "Delete SAC_SOrg_Temp.Channel, SAC_SOrg_Temp.SAC, SAC_SOrg_Temp.NIV, SAC_SOrg_Temp.Period " & _
"FROM SAC_SOrg_Temp;"

DoCmd.SetWarnings False
DoCmd.OpenQuery "SAC_01", acViewNormal
DoCmd.SetWarnings True

Dim Per_Num As Integer

Per_Num = ???????????

'Period 1 update
If Per_Num >= 1 Then
cn.Execute "UPDATE SAC_Periods INNER JOIN SAC_SOrg_Temp ON SAC_Periods.Channel " & _
"= SAC_SOrg_Temp.Channel SET SAC_Periods.P01 = [niv] " & _
"WHERE SAC_SOrg_Temp.Period='" & "p01' " & ";"
End If

 

[Fizzio]

You seem to be already familiar with using recordsets. If it is a solitary value you are looking for, you may be better suited using the DLookup method to retrieve the value.

 

[Dachande11]

Thanks Fizzio,

even though I managed to get that code to work I dont understand recordsets. I basically robbed that code from someone else and changed it to meet my requirements. Anyway the Dlookup worked perfect.

Thanks again