Thanks Nick. Glad we are on the right track. Now, we need to use the loop function so we can achieve what you need.
The solution consists of three steps:
1) Create a Query based on the invoice table. Under criteria in Query Design View, Type "Is Null" without quotes. Note that in SQl view the statement is (replace names where necassary).
- SELECT TableName.FieldName1, TableName.FieldName2
-
FROM TableName
-
WHERE (((TableName.FieldName1) Is Null));
-
Of course you can modify it in the way you want depending on your needs. For example you can add more criteria (Invoice No, Date..etc).
2) Create a Multiple Item Form based on the just created Query.
3) Create a button and attach the following code to it:
- Do
-
FieldName = DMax("FieldName", "TableOrQuery") + 1
-
Recordset.MoveNext
-
Loop Until Recordset.EOF
-
That is it, you are done. Furthermore, if you want not to get any error when there are no records, you can add If statement at the beginning like : If RecordCount = 0
Msg Box "No Record" Else..
Here is my favorite part and one thing I like about Access & VBA.
You can create a button that will do it for you (Open the form, update records, close back the record, give message of success or no result when there are no records). This will make your database looks extremely professional because all what the user would see is that button.
I have attached a database with the example.
The form with the button is the one displayed at opening. Once you click on the button "update records", All records will be updated. If the table (invoice table) contains no records without invoice number, you will get the message "no new records". If there are new records with no invoice number, the records will be updated with the required number (DMax + 1) and you will get the message "records updated".
The table has some missing invoice numbers; so first time you click "update records" the records will be updated. Next time it won't as all records will have invoice numbers.
It is simple and works great. I hope it will help you with this issue as well with future issues.
Regards,
Ali