Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Do Loop or use End iF for search string | Excel Discussion (Misc queries) | |||
Find loop doesn't loop | Excel Discussion (Misc queries) | |||
Access Form In An Access Report (SubForm) Question | Links and Linking in Excel | |||
How do I search excel spreadsheets using multiple search criteria. | Excel Worksheet Functions | |||
search & copy with loop | Excel Programming |