Auto Advancing to Next Record on Conditions Met

[J Bhujanga]

We must go through very large data sets, i.e. 100,000 to 200,000 records, each of which has many data fields. Among those fields there are 21 that we may want to flag for needing data, i.e. not zero or null. It may be that only 1 in 50 or even 1 in 1000 records have problems, so we don't want to chug along one record at a time.
So in my Current event I check all these fields will result in a record needing updating and if they're all good then I want to skip to the next record, otherwise I want to stop.
Everything essentially works except it tends to crash after skipping many records, and when it get's to one where I want it to stop, i.e. I don't even execute the statement that sends it to the next record, instea of just stopping it crashes there saying t can't go to the specified record.
The statement I'm using is:
DoCmd.GoToRecord acDataForm, "Accident Picker", acNext

Also, sometimes it says there's not enough memory to go on. This is usually after about 100 records have been skipped. Is there an easy way to do this that's not so unstable?

[andy49]

Can you use a filter to only list those you need to change?

Filter off = have all data

Filter on = have data you need to change.




Sent from my iPhone using Tapatalk

[ssanfu]

The Current event is the wrong place to have that line of code. Every time the record changes, the current event fires, which changes the record, which fires the current event, which.... well, you get the idea. Then you reach the end of the record set/ When the current event fires on the last record of the record set, the command tries to go past the last record and the error occurs.

There are two methods I would think about:
1) as Andy suggested, filter the record set in the form. The record set has 100,000 records, enter 1 or more criteria and filter the record set.
2) set criteria in the form query (WHERE condition in the query). This limits the records that are returned to the form. If you had 1 in 1,000 and 100,000 records in the domain, the query would only return 100 records, instead of the entire 100,000 records.

If all you are doing is editing specific records, I would pick option #2.
If you want to edit some records, the search/edit other records, I would use option #1.