VBA (DOA) in Access 2003, I am a new at this. The following code generates an error 3020; explanations under help don't help me. Would appreciate a short line of assistance. ExistingReceiptNos as Recordset Recordarray() as Integer (dynamic initialized with ReDim) Recordcounter as Long Receipt Number is Table Field as Double Number If (recordcounter - 1) = ExistingReceiptNos("Receipt Number") Then ExistingReceiptNos("Receipt Number") = ExistingReceiptNos("Receipt Number") + 1 End If Regards, Wilfried

It would help if you told us what error 3020 is. This is probably where the error is occurring. ExistingReceiptNos("Receipt Number") = ExistingReceiptNos("Receipt Number") + 1 You are trying to change a record without first using then Recordset.Edit command. Another thing puzzles me. Why Recordarray() as an integer. Record numbers are longs and using an integer you are going to be restricting your self to just over 32,000 of whatever it is for. Stuart

Hello Stuart, The error message is 'Update or CancelUpdate without AddNew or Edit. (Error 3020)', exactly what you are indicating. The error occurs in the line highlighted by you. The recordarray() is integer since the maximum no of records theoretically could be 200 (the no of receipts in a receipt book). There are at present 3 receiptbooks, each with 200 receipts. So I have to change the code, but only after it will work for one book (receipt no. 601-800). The initial code was kindly forwarded by you on the 27 Oct. I have done some modifications although I am not sure whether I did the right thing. For you info, here is the code: Option Compare Database Dim ExistingReceiptNos As Recordset Dim sqlselect As String Dim TotalRecords As Long Dim recordarray() As Integer Dim item As Long Dim recordcounter As Long Sub FindMissingNos_Click() Set Database = currentdb sqlselect = "SELECT Members.[Receipt Number] FROM Members WHERE (((Members.[Receipt Number]) > 600 And (Members.[Receipt Number]) < 801)) ORDER BY Members.[Receipt Number]" Set ExistingReceiptNos = currentdb.OpenRecordset(sqlselect) ExistingReceiptNos.MoveLast TotalRecords = ExistingReceiptNos.recordcount ExistingReceiptNos.MoveFirst ReDim recordarray(TotalRecords) recordcounter = ExistingReceiptNos(item) Do While Not ExistingReceiptNos.EOF Debug.Print recordcounter Debug.Print ExistingReceiptNos("Receipt Number") If recordcounter <> ExistingReceiptNos("Receipt Number") Then _ recordarray(item) = ExistingReceiptNos("Receipt Number") Debug.Print recordarray(item) item = item + 1 recordcounter = recordcounter + 1 ExistingReceiptNos.MoveNext Debug.Print ExistingReceiptNos("Receipt Number") Debug.Print If (recordcounter - 1) = ExistingReceiptNos("Receipt Number") Then ExistingReceiptNos("Receipt Number") = ExistingReceiptNos("Receipt Number") + 1 End If Loop ExistingReceiptNos.Close Database.Close End Sub

In that case you need to modify the code: If (recordcounter - 1) = ExistingReceiptNos("Receipt Number") Then ExistingReceiptNos.Edit ExistingReceiptNos("Receipt Number") = ExistingReceiptNos("Receipt Number") + 1 ExistingReceiptNos.Update ExistingReceiptNos.Refresh End If The Refresh command is only needed is you wish to keep the current record as the active record otherwise you could use MoveFirst or MoveLast. What you are trying to do is fraught with problems. Sooner or later something will go wrong and your receipt numbers will get out of sequence. What may be a better approach is to create 200 records with just the Receipt number field completed then edit each record as required. When a new receipt book comes into use, create another 200 records with the new receipt numbers. You can create the records in a loop using the AddNew command and just insert the receipt numbers from 1-200. Stuart

Hello Stuart, This piece of code is only to account for some people which paid by cheque for up to 5 individual members, i.e. I got 5 records with the same receipt number. Thanks for your quick help. I know that this way is very clumsy and might be troublesome. But at this time and with my knowledge I thought that had to do. I certainly can do this manually without too much effort, but I like the challenge and to work things out (there is no pressure since I am retired and this is all voluntary work. I will try your suggestion and keep you posted. Regards, Wilfried