Looping puzzle

[dantomi]

Very close to figuring this out. Can you help?

The code below works beautifully in extracting addresses for the
AddressData table, but it always ends on an error. After the last record
is "posted" to the table it cannot find another "ST" record. I have tried
numerous ways to exit the loop, such as EOF True statements, setting a
counter, setting an exit several lines before the number of records, etc.,
etc. Nothing seems to work.

The error occurs on the line of code that reads: Do Until r!Field1 = "ST"

The database below contains only the two affected tables, one query and its
module. the AddressData table is empty. Run the Macro. You will see the
error message, but you can see that it brought all the address data
correctly over to AddressData table.

(See attached file: AddressLoader.mdb)

 

[dantomi]

It's me again, here is the module:

Public Function MailToShipToAddresses()

Dim dbs As Database, r As Recordset, r2 As Recordset
Set dbs = CurrentDb

'Open the source table EXTRACT850
Set r = dbs.OpenRecordset("EXTRACT850", dbOpenDynaset)
Set r2 = dbs.OpenRecordset("AddressData", dbOpenDynaset)
Dim AddressType, Name, DODAAC, AddressLine1, _
AddressLine2, AddressLine3, AddressLine4, City, State, Zip, TempRelease As String

'Find first release and initialize TempRelease variable
'Find the first order record and index one line
r.MoveFirst

Do Until r.EOF

Do Until r!Field1 = "ST"
r.MoveNext
Loop
r.MoveNext
'We now know we are at the fist line of order data

TempRelease = r!Field3
Do Until r!Field1 <> &quot;ST&quot; 'This will run the loop through all lines of the order
'Build MailToAddress or ShipToAddress
Do Until TempRelease <> r!Field3
'loop to next &quot;N1&quot; record
Do Until r!Field4 = &quot;N1&quot; Or r!Field1 <> &quot;ST&quot;
r.MoveNext
Loop
'Initialize address variables
DODAAC = Null
Name = Null
AddressType = Null
AddressLine1 = Null
AddressLine2 = Null
AddressLine3 = Null
AddressLine4 = Null
City = Null
State = Null
Zip = Null

'Classify N1 record
Select Case r!Field5
Case &quot;31&quot;
AddressType = &quot;MailTo&quot;
Case &quot;BY&quot;
AddressType = &quot;BuyingAgency&quot;
Case &quot;PO&quot;
AddressType = &quot;PayingOffice&quot;
Case &quot;ST&quot;
AddressType = &quot;ShipTo&quot;
Case &quot;Z7&quot;
AddressType = &quot;MarkFor&quot;
Case Else
AddressType = &quot;&quot;
End Select

'Extract address detail
Name = r!Field6
DODAAC = r!Field8
r.MoveNext
Do While r!Field4 = &quot;N2&quot; Or r!Field4 = &quot;N3&quot; Or r!Field4 = &quot;N4&quot;
If r!Field4 = &quot;N2&quot; Then
AddressLine1 = r!Field5
AddressLine2 = r!Field6
End If
If r!Field4 = &quot;N3&quot; Then
AddressLine3 = r!Field5
AddressLine4 = r!Field6
End If
If r!Field4 = &quot;N4&quot; Then
City = r!Field5
State = r!Field6
Zip = r!Field7
End If
r.MoveNext
'Write data to the table
Loop
'Write address to table
If AddressType <> &quot;&quot; Then
r2.AddNew
r2!Release = TempRelease
r2!DODAAC = DODAAC
r2!AddressType = AddressType
r2!Name = Name
r2!AddressLine1 = AddressLine1
r2!AddressLine2 = AddressLine2
r2!AddressLine3 = AddressLine3
r2!AddressLine4 = AddressLine4
r2!City = City
r2!State = State
r2!Zip = Zip
r2.Update
End If

Loop

'We now know we are at the fist line of order data
Loop

Loop

DoCmd.Close acTable, &quot;AddressData&quot;, acSaveYes

End Function

 

[Sawatzky]

dear dantomi,

I assume you tried to use the eof like this


Do Until (r.eof) or (r!Field1 = &quot;ST&quot
r.MoveNext
Loop

as VB allways tests all conditions this won't help

try this

Do Until r!Field1 = &quot;ST&quot;
r.MoveNext
if r.eof then

exit do ' or sub or whatever
end if
Loop

BTW why don't you use the find for ADO or the findnext method for DAO recordsets??


HTH

regards Astrid