Thanks it works.

Greetings Hans
"Heiko" schreef in bericht
...
Hello Hans

With rs
.Open "SELECT * FROM Postcode" & " WHERE [Postcode] = '" &
target &
"'", cn, , , adCmdText
Do
If Not .EOF Then
waarde = .Fields(intColIndex).Value
soortnummer = .Fields(intColIndex + 1).Value
Select Case soortnumer
Case 0
vannummer0 = .Fields(intColIndex + 2).Value
totnummer0 = .Fields(intColIndex + 3).Value
Case 1
vannummer1 = .Fields(intColIndex + 2).Value
totnummer1 = .Fields(intColIndex + 3).Value
End Select
straatnummer = .Fields(intColIndex + 4).Value
plaatsnummer = .Fields(intColIndex + 5).Value
Else
antwoord = MsgBox("Deze postcode bestaat niet!")
.Close
TargetRange.Select
Exit Sub
End If
.MoveNext
Loop Until .EOF
.Close
End With

Heiko
:-)

"hans" wrote:

I can get my data out of access but i only find the first record.
Most of the time there ar two records in acces that i need to find.
I use the folloing code.

With rs
' open the recordset
'.Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable
' all records
.Open "SELECT * FROM Postcode" & " WHERE [Postcode] = '" & target
&
"'", cn, , , adCmdText
If Not rs.EOF Then
waarde = rs.Fields(intColIndex).Value
soortnummer = rs.Fields(intColIndex + 1).Value
Select Case soortnumer
Case 0
vannummer0 = rs.Fields(intColIndex + 2).Value
totnummer0 = rs.Fields(intColIndex + 3).Value
Case 1
vannummer1 = rs.Fields(intColIndex + 2).Value
totnummer1 = rs.Fields(intColIndex + 3).Value
End Select
straatnummer = rs.Fields(intColIndex + 4).Value
plaatsnummer = rs.Fields(intColIndex + 5).Value
Else
antwoord = MsgBox("Deze postcode bestaat niet!")
TargetRange.Select
Exit Sub
End If
'TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset
data
End With
rs.Close

How can i make de code loop?
once for case 0 and once for case 1

Greetings Hans