check string value

[sal21]

I have a var nmaed myvar (dim as string).
For example myvar:
245
145-458
145-987-689
147-789-777-145
...

Have a table access Mytable with one field named CODE.
Just a conn apened mycon.
i use jet ado and vba for excel

I nee to check if each item into string var are present into the field CODE
if one or more item not is present into the field CODE Msgbox("xxxx-yyyyy.... not is into the CODE field")

Please with Sql strig code
I hope understand me.

[HansV]

Code: Select all

Dim arr() As String
Dim rst As New ADODB.Recordset
Dim i As Integer

rst.Open "MyTable", mycon, adOpenKeyset, adLockOptimistic, adCmdTableDirect
For i = LBound(arr) To UBound(arr)
  rst.MoveFirst
  rst.Find "CODE='" & arr(i) & "'"
  If rst.EOF Then
    MsgBox arr(i) & " not found"
  End If
Next i
rst.Close

[sal21]

Code: Select all

Dim arr() As String
Dim rst As New ADODB.Recordset
Dim i As Integer

rst.Open "MyTable", mycon, adOpenKeyset, adLockOptimistic, adCmdTableDirect
For i = LBound(arr) To UBound(arr)
  rst.MoveFirst
  rst.Find "CODE='" & arr(i) & "'"
  If rst.EOF Then
    MsgBox arr(i) & " not found"
  End If
Next i
rst.Close

sorry but i cannot test now...
tell you some tomorrow...
Sal.

Tested... but have error on:

For I = LBound(ARR) To UBound(ARR)

if the var contain also one code string for example:

VAL_CODICE="979"

Code: Select all

Sub VERIFICA_CODICI(VAL_CODICE)

Dim ARR() As String
Dim RST As New ADODB.Recordset
Dim I As Integer

RST.Open "INDICATORI_NUM", CN, adOpenKeyset, adLockOptimistic, adCmdTableDirect

For I = LBound(ARR) To UBound(ARR)
  RST.MoveFirst
  RST.Find "ID_INDICATORE='" & ARR(I) & "'"
  If RST.EOF Then
    MsgBox ARR(I) & " not found"
  End If
Next I
RST.Close

End Sub

Peraphs resolved my self:

Sub VERIFICA_CODICI(VAL_CODICE)

Dim ARR() As String
Dim RST As New ADODB.Recordset
Dim I As Integer

RST.Open "INDICATORI_NUM", CN, adOpenKeyset, adLockOptimistic, adCmdTableDirect

ARR = Split(VAL_CODICE, "-")
For I = LBound(ARR) To UBound(ARR)
RST.MoveFirst
RST.Find "ID_INDICATORE='" & ARR(I) & "'"
If RST.EOF Then
MsgBox "CODICE: " & ARR(I) & " NON TROVATO. IMPOSSIBILE INSERIRE"
End If
Next I
RST.Close

End Sub