recherche de valeur access dans vba

Sub recherche()

Dim exc As ADODB.Connection
Dim base As access.Application



Set base = CreateObject("access.Application")

Static ac As access.Application: Set ac = access.Application
ac.OpenCurrentDatabase ("C:\Users\nono\Desktop\essai.accdb")
ac.DoCmd.OpenTable ("table principale")

base.Visible = True

nom_base = ac.CurrentObjectName
Set exc = CurrentProject.Connection
'base.CurrentDb.OpenRecordset


MsgBox (nom_base)

'fichier interne
a = Application.GetOpenFilename(, , "ouverture des extracts BNP")
If a = faux Then
MsgBox ("opération annulée")
Set base = Nothing
End
End If

'ouverture fichier
Workbooks.Open Filename:=a
'recup nom fichier
file_int = ActiveWindow.Caption

Range("D65536").Select
Selection.End(xlUp).Select
fin_fact = Selection.row

Windows(file_int).Activate

For i = 2 To fin_fact

Datef = Cells(i, 2).Value
quant = Cells(i, 4)
sens = Cells(i, 3)
annee = Cells(i, 7)
Prix = Cells(i, 12)

For Each c In c

Application.StatusBar = " i: " & i & " e name : " & e.Name & " z:" & z

rtestfinal = 0
rtest1 = 0
rtest2 = 0
rtest3 = 0
rtest4 = 0
rtest5 = 0
' rtest6 = 0
' rtest7 = 0

If Prix = myrecordset.Fields(17) Then

If Datef = myrecordset.Fields(3) Then
rtest1 = 1
End If

' If testmois = Cells(z, 10) Then
' rtest2 = 1
' End If

If annee = myrecordset.Fields(12) Then
rtest3 = 1
End If

' If testpdt = Cells(z, 8) Then
' rtest4 = 1
' End If

If quantite = myrecordset.Fields(5) Then
rtest5 = 1
End If

If sens = myrecordset.Fields(4) Then
rtest6 = 1
End If




rtestfinal = rtest1 + rtest3 + rtest5 + rtest6 + rtest7


If rtestfinal = 7 Then

Cells(z, 17).Value = Cells(z, 17).Value & " pointée " & i
Windows(file_ext).Activate
Cells(i, 15).Value = Cells(i, 15).Value & "pointée extract" & ":" & z & "--" & ":" & e.Name
Range("A" & i & ":O" & i).Interior.ColorIndex = 35

End If

Else: rtestfinal = 0

End If

Next c

Windows(file_int).Activate
Next i
Set base = Nothing

End Sub

Cliquez pour agrandir...