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

Carol Davis 25 Reputation points
2026-09-21T21:12:39.59+00:00

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?

Microsoft 365 and Office | Access | For business | Windows
0 comments
Answer accepted by question author
Kai-L 19,920 Reputation points Microsoft External Staff Moderator
2026-09-21T22:14:25.26+00:00

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.  

Was this answer helpful?

1 person found this answer helpful.
0 comments

2 additional answers

Sort by: Oldest
Most helpful Newest Oldest
  1. Duane Hookom 26,940 Reputation points Volunteer Moderator
    2026-09-22T04:16:12.7766667+00:00

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

    Was this answer helpful?

    0 comments
  2. Carol Davis 25 Reputation points
    2026-09-22T11:47:18.8933333+00:00

    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

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.