Reference a query result in VBA, could maybe use SQL?

Question

Basically I need to find the maximum value in a given dataset and record that value to apply it to another record.

I tried using the SQL "SELECT MAX(SCID) FROM [Stock Conversion];" but this just input this piece of SQL into the Field on the table.

Now i have created a query to find that value, is there a way to reference the result of the query in the code to achieive this?

Here is the code for context:

Private Sub listResult_DblClick(Cancel As Integer)

Dim sc1 As DAO.Recordset
Set sc1 = CurrentDb.OpenRecordset("Stock Conversion Items", dbOpenDynaset)
Dim strSource3 As String

strSource3 = DONT KNOW

sc1.AddNew
sc1.Fields("[SCID]").Value = strSource3
sc1.Fields("[Result PC]").Value = Me.listResult.Column(0)
sc1.Fields("[Status]").Value = "NEW"
sc1.Update

End Sub

Thanks in advance, Bob P

Answer 1

With MS Access, you can use domain aggregate functions:

strSource3 = DMax("MyField","MyTable")

If you wanted to use SQL, then:

Dim rs As Recordset

Set rs = CurrentDB.Openrecordset( _
   "SELECT MAX(SCID) As MaxSCID FROM [Stock Conversion];")
strSource3 = rs!MaxSCID

Be very careful with max values in a multi-user environment. They could change due to another user inputting data.

From chat

It appears that it may be possible to get the required ID from previous code using the LasModified property. This is far safer in a multi-user environment.

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Table1", dbOpenDynaset)

rs.AddNew
rs!AField = "abc"
rs.Update 
rs.Bookmark = rs.LastModified 
strSource3 = rs!SCID

Answer 2

try this:

strSource3 = dmax("[field_name]","table_name")