I have tried the following code that deals with access table named "Approved" >> the code is looping through the records and fill each field "NewGrade" field and "NewExpiryDate" field with some values
Is it possible to store the results in an array and put the results in one shot in the fields "NewGrade" and "NewExpiryDate" .. as we can do in the excel application
Here's the code
Code: Select all
Sub Test()
Dim rs As DAO.Recordset, dic As Object, sCode As String, sResult As String, i As Long
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = 1
Set rs = CurrentDb.OpenRecordset("Approved")
If Not (rs.BOF And rs.EOF) Then
rs.MoveFirst
i = 1
Do
sCode = rs.fields("SupplierCode").Value
If sCode <> "" Then
If Not dic.Exists(sCode) Then
sResult = GetGradeExpiryDate(CStr(sCode))
rs.Edit
rs.fields("NewGrade").Value = i * 2
rs.fields("NewExpiryDate").Value = i * 3
rs.UPDATE
dic(sCode) = Array(rs.fields("NewGrade").Value, rs.fields("NewExpiryDate").Value)
Else
rs.Edit
rs.fields("NewGrade").Value = dic(sCode)(0)
rs.fields("NewExpiryDate").Value = dic(sCode)(1)
rs.UPDATE
End If
End If
rs.MoveNext
i = i + 1
If i > 10 Then Exit Do
Loop Until rs.EOF
End If
End Sub