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

Did you try to open the recordset and assign the max value to a variable by asisgning: sc1(0) ? (Max returns just 1 record). — Trace
– Trace, Commented Oct 1, 2012 at 13:48

Fionnuala · Accepted Answer · 2012-10-02 09:00:30Z

2

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

edited Oct 2, 2012 at 9:00

answered Oct 1, 2012 at 13:46

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Bob P Over a year ago

Hi Remou, is there another way of doing this without the max value?

Fionnuala Over a year ago

Hi Bob. I need some background. Is SCID an autonumber? What does Max(SCID) mean? Why do you need it?

Bob P Over a year ago

I use it to link Stock Order Items with a Stock Order in a one to many relationship (1 stock order has many stock order items) SCID is an autonumber in the Stock Order table, and i'm using MAX to link Stock Order Item records to a Stock Order.

Bob P Over a year ago

To clarify, when you first start the stock order process, you select the source product and a stock order is created with the source product, then you select the resulting product (stock order items) and they find the right stock order number by selecting the MAX (and therefore newest) number in the stock order table.

Fionnuala Over a year ago

"when you first start the stock order process, you select the source product and a stock order is created with the source product" How is the stock order created? Do you run sql/a query against a connection or database object in code? It is at this point that you need to get the SCID using @@identity. You certainly cannot depend on max being the newest if there is even one more person using the system. At the very least, you need a timestamp, and that is not perfect, either.

|

Alin I · Accepted Answer · 2012-10-01 13:50:51Z

0

try this:

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

answered Oct 1, 2012 at 13:50

Alin I

5901 gold badge7 silver badges24 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

10 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related