Darren Bartrup
Well-known Member
- Joined
- Mar 13, 2006
- Messages
- 1,297
- Office Version
- 365
- Platform
- Windows
Not sure how to explain this one.
I have a table with 184 records in it.
returns every record in the same order as the table.
does the same, but brings back only the sAddress field.
But if I ask it to look at the bCleansed boolean field where all values are FALSE it returns the full recordset again, but starts at record 10 and loops around so the first 10 records are attached to the bottom. I've tried here - NOT bCleansed and bCleansed = FALSE and bCleansed = 0 - but they all return the same.
Not a bad thing, after all the position of data in a table isn't relevant.
But if I use the query in the code below it misses records - I've stepped through the code, and although it's not quite perfect - the last record in a batch is also the first record in the next batch - it seems to suddenly miss a bunch of records for no reason that I can see.
Does anyone have any ideas as to why this behaviour occurs?
I have a table with 184 records in it.
Code:
SELECT *FROM tbl_NewInstructions
Code:
SELECT sAddress
FROM tbl_NewInstructionsBut if I ask it to look at the bCleansed boolean field where all values are FALSE it returns the full recordset again, but starts at record 10 and loops around so the first 10 records are attached to the bottom. I've tried here - NOT bCleansed and bCleansed = FALSE and bCleansed = 0 - but they all return the same.
Code:
SELECT sAddress
FROM tbl_NewInstructions
WHERE NOT bCleansedBut if I use the query in the code below it misses records - I've stepped through the code, and although it's not quite perfect - the last record in a batch is also the first record in the next batch - it seems to suddenly miss a bunch of records for no reason that I can see.
Does anyone have any ideas as to why this behaviour occurs?
Code:
Private Const BATCH_SIZE As Long = 10
Public Sub CleanseRecords()
Dim db As dao.Database
Dim rs1 As dao.Recordset
Dim rs2 As Variant
Dim sBatch As String
Dim lRecordCount As Long
Dim lSeekRecord As Long
Dim x As Long
Set db = CurrentDb
Set rs1 = _
db.OpenRecordset("SELECT * FROM tbl_NewInstructions WHERE NOT bCleansed", dbOpenDynaset)
lRecordCount = 0
With rs1
If Not .EOF Then
.MoveLast
.MoveFirst
Do While Not .EOF
lRecordCount = lRecordCount + 1
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'If the record count has not reached the batch size or end of file then '
'add the address to the batch string. '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If lRecordCount <= BATCH_SIZE Or .EOF Then
sBatch = sBatch & """" & rs1!sAddress & ", " & rs1!sPostCode & ""","
End If
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'If the record count is equal to the batch size or end of file then '
'process the addresses within the batch string. '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If lRecordCount = BATCH_SIZE Or .EOF Then
sBatch = left(sBatch, Len(sBatch) - 1)
Debug.Print sBatch
UpdateSwitchboardProgress ("Cleaning records " & .AbsolutePosition + 1 - BATCH_SIZE & _
" to " & .AbsolutePosition + 1 & " of " & .RecordCount)
DoEvents
Set rs2 = CleansePlus_Batch_Cleanse_v1_00(LICENSE_KEY, sBatch, "StrictProperty", 5, True, False)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'As we have been stepping through the recordset we need to move '
'back by the same amount of steps to the start of this batch. '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
For x = BATCH_SIZE - 1 To 1 Step -1
rs1.MovePrevious
Next x
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Now step through the cleansed address recordset and the '
'database recordset placing clean addresses in the correct record. '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
For x = 1 To BATCH_SIZE - 1
.Edit
rs1!sLine1 = IIf(rs2.Fields("Line1") = "", Null, rs2.Fields("Line1"))
rs1!sLine2 = IIf(rs2.Fields("Line2") = "", Null, rs2.Fields("Line2"))
rs1!sLine3 = IIf(rs2.Fields("Line3") = "", Null, rs2.Fields("Line3"))
rs1!sLine4 = IIf(rs2.Fields("Line4") = "", Null, rs2.Fields("Line4"))
rs1!sLine5 = IIf(rs2.Fields("Line5") = "", Null, rs2.Fields("Line5"))
rs1!sCleanTown = IIf(rs2.Fields("PostTown") = "", Null, rs2.Fields("PostTown"))
rs1!sCleanCounty = IIf(rs2.Fields("County") = "", Null, rs2.Fields("County"))
rs1!sCleanPostCode = IIf(rs2.Fields("Postcode") = "", Null, rs2.Fields("Postcode"))
rs1!bCleansed = True
.Update
rs1.MoveNext
rs2.MoveNext
Next x
''''''''''''''''''''''''''''''''''''''''''''''
'Reset the counter and batch address string. '
''''''''''''''''''''''''''''''''''''''''''''''
If lRecordCount = BATCH_SIZE Then
lRecordCount = 0
sBatch = ""
End If
End If
If lRecordCount <> 0 Then
.MoveNext
End If
Loop
End If
End With
UpdateSwitchboardProgress ("Removing invalid addresses.")
DoCmd.SetWarnings False
DoCmd.OpenQuery "DDL_Delete_NoCleanAddress"
DoCmd.SetWarnings True
UpdateSwitchboardProgress ("")
End Sub
