display errors with loop

[fanfan2]

I have the following:

Do While Not recordSetx.EOF
if <condition> then
msgbox ("error" & error)
end if
recordsetx.movenext
loop

This displays the error messages in hundreds of small message box windows one after another, I have over 1000 errors, is there a way that the error messages can be displayed (listed) in one window?

thanks very much for any help.

 

[Beetle]

How about just creating a recordset of only those records that meet the error condition then display a message box of how many records have errors.

Dim strSQL As String
Dim strMsg As String
Dim rs As DAO.Recordset

strSQL = "Select * From YourTable Where <condition>"

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

rs.MoveLast
rs.MoveFirst

strMsg = "There are " & rs.RecordCount & " records with errors."

MsgBox strMsg

If you need more details about each record you could use a similar record set to open a form displaying the records that have errors.

 

[fanfan2]

Sorry I didn't get it...
In my case in order to find the errors I have to use a loop to go through the recordset ( can't do it with a simple select statement). But this way I can only find errors one by one, which is a pain. I wonder how I could display all the errors at once. thanks.

Private Sub Command6_Click()
Dim curDatabase As Object
Dim rsts As Recordset
Dim column3Previous As String
Dim column3next As String
Dim column1Next As Double
Dim column2Previous As Double
Dim column2next As Double

Set curDatabase = CurrentDb
Set rsts = curDatabase.OpenRecordset("tblHs")
rsts.MoveFirst
column3Previous = rsts("FID").Value
column2Previous = rsts("t").Value
rsts.MoveNext

Do While Not rsts.EOF
column3next = rsts("FID").Value
column1Next = rsts("f").Value
column2next = rsts("t").Value

If column3Previous = column3next And column1Next <> column2Previous Then

MsgBox (column3Previous & "error " & column1Next & " " & column2Previous)

End If

column3Previous = column3next
column2Previous = column2next
rsts.MoveNext

Loop

Set rsts = Nothing
Set curDatabase = Nothing
End Sub

 

[spikepl]

Replace

MsgBox

with

debug.print

 

[Beetle]

If I understand correctly, you want to loop through the recordset and compare each successive record to the previous one. If the "FID" values match but the "f" value of the current record does not match the "t" value of the previous record then the record is in error. You want to create a list of these records that are in error (of which you say you have 1000 or more) and either display it or print it.

Then what? Presumably these errors need to be corrected. Are the users then going to manually go through and correct 1000 or more errors? What needs to be done to correct the error? If it is simply a matter of making the values match than that could be done programmatically also, using an update query, without all the brain damage.

Having said that, there is another inherent problem with what you are attempting that should be addressed.

Tables have no intrinsic order. It's just a "bucket of records". Opening a recordset on a table does not guarantee that it will open in any particular order (unless you specify the order using a query with an Order By clause). Looping through a recordset like this and comparing the "current" record to the "previous" record has no real meaning because you have no way of knowing what order the records are in to begin with. If there is a field in your table that can be used to determine the order, like a Date/Time field, or a sequential ID (not Autonumber, because that is only guaranteed to be unique, not sequential), then you should use that to make sure the records are in the correct order before you loop through them.

However, again, I'm not sure I see the purpose in looping through a record set and generating a list of records that are in "error". It would seem much more practical to correct them as you go if possible.

Going forward, if this table contains records that users are going to be adding to in the future, I would strongly recommend that you do this validation at the point of data entry (on your form - if you're not doing so already) so as to avoid having to go back and correct errors after the fact.

 

[fanfan2]

Thanks for your comments.
Actually I just want to find all the errors and display, I wouldn't correct those as someone else will have to go through the errors base on the original files - there's no "pattern" or "rule" to correct them.
I agree I should make triggers to catch errors at the data entry, but these errors were the old data, the new data is ok .
BTW, the debug.print didn't work.