Set a field/value equal to a SQL statement 3

[kjv1611]

Is there a particular context to set the value of a table or field equal to a SQL statement within VBA?

For instance, I want to have a query return one value, and have that value entered into a table. I would like to do this via SQL within my VBA procedure.

I may be just totally forgetting or overlooking something, but I just can't seem to get this accomplished at the moment.

Any thoughts/ideas?

Here's the data:
I have an ID field in one table. In updating another table's new records, I want to include the "MAX" value of the ID field in the original table.

So, I would like to do something like this:

Private Sub Test()
  Dim db as DAO.Database
  Dim rs as DAO.Recordset
  Dim strSQL as String
  Set db = CurrentDb
  Set rs = db.OpenRecordset("TableDetail")

  strSQL = "SELECT Max([ImportID]) AS ImpID FROM tblHist;

  rs.Fields("OtherID") = DoCmd.RunSQL (strSQL)
  
  rs.Close
  db.Close
  Set rs = Nothing
  Set db = nothing
End Sub

I'm sure that won't work, but I am hoping that someone can straiten me out here, or show me a keyword that would make since on further searching this one out.

Thanks in advance for any advice/info.

--

"If to err is human, then I must be some kind of human!" -Me

 

[Remou]

You may wish to consider Nz, which would cover both possibilities.


WHERE Nz([order],0)=0

 

[kjv1611]

Good thought. I've used that before, but haven't used it all that much. It's probably one I should train myself to use more frequently, I would imagine.

--

"If to err is human, then I must be some kind of human!" -Me