1

I'm start to do programming in Access, and I really need help!!

My objective is to create a module that is run in "tbCustoProjeto" table and rewrite the field "Valor HH" values based on Dlookup. I found some solution (by azurous) who I think will solve this, but when I run the code, is returned

"object-required-error".

Sub redefineHH()
Dim objRecordset As ADODB.Recordset
Set objRecordset = New ADODB.Recordset
Dim i As Integer
Dim value As Variant
Dim HHTotal As Double
Set HHTotal = DLookup("[CustoTotalNivel]", "tbNivelNome2", "nUsuario='" & tbCustoProjeto!NumUsuario & "'" & "AND Numeric<=" & tbCustoProjeto!DataNumero)

'initated recordset obejct
objRecordset.ActiveConnection = CurrentProject.Connection
Call objRecordset.Open("tbCustoProjeto", , , adLockBatchOptimistic)
'find the target record
While objRecordset.EOF = False
'If objRecordset.Fields.Item(13).value > 0 Then
objRecordset.Fields.Item(13).value = HHTotal
objRecordset.UpdateBatch
'exit loop
'objRecordset.MoveLast
objRecordset.MoveNext
'End If
Wend
MsgBox ("Pesquisa Finalizada")
End Sub

Print of tbCustoProjeto

enter image description here

Print of tbNivelNome2

enter image description here

Please, someone can tell me where is the error? I don't know what to do.

June7
  • 19,874
  • 8
  • 24
  • 34
Vinicius s.
  • 13
  • 2

1 Answers1

0

Cannot reference a table directly like that for dynamic parameter. DLookup should pull dynamic criteria from recordset and within loop. Don't use apostrophe delimiters for number type field parameter.

Remove unnecessary concatenation. 

Sub redefineHH()
Dim objRecordset As ADODB.Recordset
Set objRecordset = New ADODB.Recordset
objRecordset.Open "tbCustoProjeto", CurrentProject.Connection, , adLockBatchOptimistic
While objRecordset.EOF = False
    objRecordset.Fields.Item(13) = DLookup("[CustoTotalNivel]", "tbNivelNome2", _
        "nUsuario=" & objRecordset!NumUsuario & " AND Numeric <=" & objRecordset!DataNumero)
    objRecordset.UpdateBatch
    objRecordset.MoveNext
Wend
MsgBox ("Pesquisa Finalizada")
End Sub
June7
  • 19,874
  • 8
  • 24
  • 34
  • June7, thank you very much for your help. I can filter perfectly the field with: `objRecordset.Fields.Item(13) = DLookup("[CustoTotalNivel]", "tbNivelNome2", _ Numeric <= objRecordset!DataNumero)`....... and I can filter with: `objRecordset.Fields.Item(13) = DLookup("[CustoTotalNivel]", "tbNivelNome2", _ "nUsuario=" & objRecordset!NumUsuario)`............., but not with the both. Do you know why? (type-mismatch-error) – Vinicius s. May 04 '20 at 03:42
  • Since nUsuario is a number type field, remove apostrophe delimiters. I should have noticed that and removed them. Edited answer. – June7 May 04 '20 at 04:09