Auto cycling through records

[Peter Whitfield]

I am setting up a database that imports an unknown number of records to a form. The form should ONCurrent is set to run a macro which validates the data and sets certain SetValue's dependant on the validation in the macro. I set the last line of the macro to Go To Record, Next. When run the form cycles through the records until the 140th where an error message "End of subset" is received and you have to click OK numerous times. When it restarts it does the next 140 records and stops again etc. At the end the error messages cannot be stopped and I have to shut down Access.

Anyone know what Im doing wrong?

If I remove the next record command and manually cycle through its fine (but a pain when theres thousands of records!)

Thanks

Peter

 

[Jack Cowley]

Could you not use an update query to do this on the data in your table? Your approach, in my opinion, is not the way to attack your problem. A query or code is the way to go...

 

[Peter Whitfield]

Jack,
In one table there are approximately 70 different validations being carried out, Im fairly new to this but it seems to me that I would have to carry out a large number of queries to perform the validation in this code. Am I correct in that assumption or just missing an easy way via Queries?

 

[Pat Hartman]

Forms are used for interactive processing. You need to process a batch of records. Either use queries to do the updating as Jack suggested or create a subroutine in a standard code module. Use DAO or ADO to read through and update the recordset. The following is a sample of DAO code that loops through a recordset based on a query and updates each row.

Public Function RenumberSeq()
Dim WgtDB As Database
Dim QD1 As QueryDef
Dim TempSet1 As Recordset
Set WgtDB = CodeDb
Set QD1 = WgtDB.QueryDefs!QRenumberSeq
Set TempSet1 = QD1.OpenRecordset
Do Until TempSet1.EOF   'there is no weightmaster in process
    TempSet1.Edit
    gSeqNum = gSeqNum + 10
    TempSet1!BCR_SEQ = gSeqNum
    TempSet1!REC_TYP = "X"         ' change rec typ temporarily to avoid seq # dups
    TempSet1.Update
    TempSet1.MoveNext              ' Locate next record
Loop
Set QD1 = WgtDB.QueryDefs!QRenumberSeqUpdate  ' reset rec typ to U
    QD1.Execute
End Function