how do i loop in a access search
 

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

how do i loop in a access search
 

Hans,

If you want to access the second record use

rs.MoveNext

If you want to loop through many records use something like:

Do Until rs.EOF
'Do something
Rs.MoveNext
Loop

--

John Green - Excel MVP
Sydney
Australia


"hans" wrote in message ...
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

how do i loop in a access search
 

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.

You will need to use the MoveNext method of the recordset.

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
change the above line to:
Do While Not rs.EOF

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
change the Else to:
rs.MoveNext
Loop

antwoord = MsgBox("Deze postcode bestaat niet!")
TargetRange.Select
Exit Sub
End If
the above 4 lines would have to be moved elsewhere or you could add
this line of code:
If rs.BOF and rs.EOF then

This rs would only be at the beginning and ending of the file at the
same time if there are no records.

'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




Hope this helps,

Matthew

how do i loop in a access search
 

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