How can I get a sequential numbered list for a Microsoft 365 Access SQL Dynamic Query

Question

How can I get a sequential numbered list for a Microsoft 365 Access SQL Dynamic Query

Carol Davis 25

I have a table named WaterTestChemUset that has 25 fields. I want to be able to select the data from

any of these fields using a combo box located on a form.

The following SQL query selects all records from the underlying table WaterTestChemUset great but I need to have a reference to the location in the underlying table of the values returned. The table has a field named RecordNum, but I don’t know how to include that field in the query.

Private Sub FieldNameQuerybutton_Click()

Dim strField As String

Dim strSQL As String

Dim qdf As DAO.QueryDef

' Check if a field is chosen

If IsNull(Me.SelectField) Then

MsgBox "Please choose a field first.", vbExclamation

Exit Sub

End If

' Get the dynamically chosen field name

strField = Me.SelectField

' Build the SQL string dynamically

strSQL = "SELECT [" & strField & "] FROM [WaterTestChemUset];"

' Option A: Update a saved query named "WaterTestFieldName0q" and open it

On Error Resume Next

Set qdf = CurrentDb.QueryDefs("WaterTestFieldName0q")

If Err.Number <> 0 Then

' Create the query if it doesn't exist

Set qdf = CurrentDb.CreateQueryDef("WaterTestFieldName0q", strSQL)

Else

' Update existing query SQL

qdf.SQL = strSQL

End If

On Error GoTo 0

' Open the query to display all data from that chosen field

DoCmd.OpenQuery "WaterTestFieldName0q", acViewNormal

End Sub

How can I add the field RecordNum as a second field to the above query or add a subquery into the

Above query to provide a sequential number for each record selected?

0 comments

Answer accepted by question author

Kai-L 19,920 Microsoft External Staff Moderator

Dear Carol,

From my research, you do not need a subquery if you only want to display the existing RecordNum field alongside the field selected from the combo box. Replace your current SQL line with:

strSQL = "SELECT [RecordNum], [" & strField & "] " & _
 "FROM [WaterTestChemUset] " & _
 "ORDER BY [RecordNum

The query will return the columns in this order:

RecordNum | Selected field

ORDER BY [RecordNum] sorts the results by that field. If RecordNum contains gaps, such as 1, 2, 5, and 8, those gaps will remain because this displays the stored values rather than generating a new sequential number.

If you need a newly generated sequence of 1, 2, 3, and so on regardless of the stored RecordNum values, that is a different requirement and would need additional query logic. For most cases, including and sorting by the existing RecordNum field is the simpler and more reliable approach.

I hope this helps. If you specifically need a new consecutive sequence rather than the existing RecordNum, please let me know.

If the answer is helpful, please click "Yes" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in the forum documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

0 comments

2 additional answers

Your answer

Answer 1

Carol Davis 25

Thank you Kai-L

I had to rewrite the SQL as;

strSQL = "SELECT [RecordNum], [" & strField & "]" & "FROM [WaterTestChemUset] " & "ORDER BY [RecordNum]"

It works perfectly and I will use this method in several other applications.

Best Regards

Kai-L 19,920 Reputation points Microsoft External Staff Moderator

2026-09-22T15:06:50.49+00:00

Dear Carol,

I’m glad I could provide helpful information to resolve this issue. If you have any further questions or need additional assistance down the road, feel free to post back. Not only I, but other professionals in the forum are always here to help. Thank you again for being a valued member of the Q&A community and I wish you all the best!

Answer 2

Duane Hookom 26,940 Volunteer Moderator

If you want to generate a sequential number, you need to describe what field(s) determine the ordering of the number.

0 comments