MS Access Row Number on Continuous Form

Numbering Continuous Form

MS Access Row Number on Continuous Form is one of the most asked around Microsoft Access learners. In fact, it gives your form a professional look. Moreover, row numbering on continuous form makes it easy for you to figure out the record number. In this tutorial, I will go through a simple way to loop through the record-set and set a sequential number for each record.

Firstly, create a field and name it “SN” in the table that is connected with the continuous form. Then, create a continuous form. Lastly, paste the below code under on load event of the form. In addition, please change the table name which appears at the below VBA code to your form name:

MS Access Row Number on Continuous Form VBA Code

				
					Private Sub Form_Load()

Dim intSN as Integer
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Table_Name")
intSN = 1

If Not(rs.EOF And rs.BOF) Then
	rs.MoveFirst
Do Until rs.EOF = True
	rs.Edit
	rs!SN = intSN
	rs.Update
	rs.MoveNext
	intSN = intSN + 1
Loop
End If

rs.Close
Set rs = Nothing

End Sub

As you can see, the field [SN] will get a sequential number and it will be displayed in your continuous form. However, what will happen if you need to filter the form or if you needed to delete a record between set of records? Everything will be missed up right! 


So, the solution for this is simply to re-use the above VBA code after you deleted the intended record within the command button. Similarly, if you need to filter the continuous form, you’ll have to re-paste the above code before the filtering codes but with minor adjustment to include only the records that match your filtering. For example, the below code will only number the records that have the same criteria as your query:

				
					Set rs = CurrentDb.OpenRecordset("SELECT * FROM Table_Name WHERE [Table_Field] = '" & Me.ComboBox1 & "'")

For more clarification, we are including a free downloadable example with all VBA codes you need.
The downloadable example will also include the below list besides numbering rows on a continuous form:

Download Sample

For more pro MS Access Programs, please visit our home page here.

To use Microsoft Access Programs.
You’ll need to have Microsoft Office installed including the bundle of MS Access or to download the free Microsoft Access 2016 run-time.