See if this helps:
Code:
Public Sub example_sub()
On Error GoTo ErrHandler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qry As String
Set db = CurrentDb
qry = "SELECT field_1, field_2, field_3, field_4, field_5 FROM some_table WHERE id=666;"
Set rs = db.OpenRecordset(qry, dbOpenSnapshot)
'Refer to the docs on OpenRecordset:
' https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/database-openrecordset-method-dao
' use dbOpenDynaset instead of dbOpenSnapshot if you want to be able to edit records
' you can refer to fields by field name
Debug.Print rs!field_1
Debug.Print rs!field_2
Debug.Print rs!field_3
Debug.Print rs!field_4
Debug.Print rs!field_5
' or like this
Debug.Print rs.Fields("field_1")
Debug.Print rs.Fields("field_2")
Debug.Print rs.Fields("field_3")
Debug.Print rs.Fields("field_4")
Debug.Print rs.Fields("field_5")
' or refer to fields by a "column" number. First field starts at zero, not one.
Debug.Print rs.Fields(0)
Debug.Print rs.Fields(1)
Debug.Print rs.Fields(2)
Debug.Print rs.Fields(3)
Debug.Print rs.Fields(4)
' Edit a field (Make sure to use the dbOpenDynaset option when opening the recordset if you want to edit fields or add records)
rs.Edit
rs!field_1 = "Chicken McNuggets"
rs.Update
' add a new record
rs.AddNew
rs!field_1 = "Chicken McNuggets"
rs!field_2 = 461
rs!field_3 = Date
rs!field_4 = 987.55
rs!field_5 = "pink goo"
rs.Update
'always close stuff that you open when you're done with them
rs.Close
ExitHandler:
'Anytime you 'Set' an object equal to something you need to reset it equal to nothing when you're done with it.
'We previously 'Set' the db and rs variables, so now we need to 'unset' them.
'And use error handling like this to ensure your objects get cleaned up even if you get errors, and you will get errors...
Set rs = Nothing
Set db = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description, , "Error #" & Err.Number
Resume ExitHandler
End Sub