Coded this in another programing language but having trouble implementing in Access VBA module. Posted in programming forum but no luck.
Can some one assist me?
No error is showing when I run this code, at the same time results are not accurate. It is working and populating only InB_Results and the other fields.
1) I have to do simple logic based on _ct( all fields). for Ex: if the InA_ct is 0 then InA_result =0 else if InA_ct> 0 then InA_result should be 1 otherwise InA_Result should be 9. This should repeat for all the _Result fields.
2) For all the _date fields I need to populate the value of date_tested ( a field in the same table). I know it is duplication but need to export to excel in this same format for some other purpose.
My _Date field should be text where as date=_tested is real date datatype.
Code:
Public Const ct = "InA_Ct,InB_Ct,H1_Ct,Hx_Ct, RP_Ct"
Public Const Dt = "InA_Date,InB_Date,H1_Date,Hx_Date,RP_Date"
Public Const result = "InA_Result,InB_Result,H1_Result,Hx_Result,RP_Result"
Sub arr_update()
Dim objDB As DAO.Database
Dim mytbl As DAO.Recordset
Set objDB = CurrentDb()
Set mytbl = objDB.OpenRecordset("Temp_RESULTS")
Dim array_ct, array_dt, array_r, i
Dim result_reported As String
array_ct = Split(ct, ",")
array_dt = Split(Dt, ",")
array_r = Split(result, ",")
For i = 0 To 4
While Not mytbl.EOF
mytbl.Edit
mytbl.Fields(array_dt(i)).Value = date_tested
If (mytbl.Fields(array_ct(i)).Value > 0 And mytbl.Fields(array_ct(i)).Value <= 32.99) Then
mytbl.Fields(array_r(i)).Value = 1 'Positive
ElseIf (mytbl.Fields(array_ct(i)).Value = 0) Then
mytbl.Fields(array_r(i)).Value = 0 'Negative
ElseIf mytbl.Fields(array_ct(i)).Value = Null Then
mytbl.Fields(array_r(i)).Value = 9 'Not tested
End If
mytbl.Update
mytbl.MoveNext
Wend
mytbl.MoveFirst
Next I
Any help is much appreciated.
Thank you