how do i loop in a access search

  • Thread starter Thread starter hans
  • Start date Start date
H

hans

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
 
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
 
hans said:
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.
Click to expand...

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
Click to expand...
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
Click to expand...
change the Else to:
rs.MoveNext
Loop
antwoord = MsgBox("Deze postcode bestaat niet!")
TargetRange.Select
Exit Sub
End If
Click to expand...
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
Click to expand...


Hope this helps,

Matthew
 
Thanks it works.

Greetings Hans
Heiko said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top