SELECT a field value into a user variable.

[cornedbeef007-groups]

How do I select a value into a user variable in VBA

Something like.

SELECT max(catalog_number) from my_list into myvariable WHERE show_id
= 1;


I then want to use myvariable later in an UPDATE query.

 

[XPS350]

How do I select a value into a user variable in VBA

Something like.

SELECT max(catalog_number) from my_list into myvariable WHERE show_id
= 1;

I then want to use myvariable later in an UPDATE query.

You can't use SELECT to select a single value. Use DLookup or, in this
case, DMax instead:

myvariable = DMax("catalog_number","my_list","show_id = 1")


Groeten,

Peter
http://access.xps350.com

 

[Bob Barrows]

How do I select a value into a user variable in VBA

Something like.

SELECT max(catalog_number) from my_list into myvariable WHERE show_id
= 1;


I then want to use myvariable later in an UPDATE query.

Don't bother. Incorporate that subquery into your update query (or DMax if
the subquery makes your query non-updateable)

Update sometable
set somefield = DMax("catalog_number","my_list","show_id = 1")
where ...

The benefit of doing it this way of course is that you don't have to worry
about concatenating delimiters and escaping characters in the data returned
from DMax if retrieving text or date/time data.

The only reason to use a variable is if you intended re-using the value
returned from the DMax or query multiple times in your procedure.

Without using a domain function (DLookup, DMax, etc.) the only alternative
is to use a recordset to retrieve the results of the query and then using
the value of the recordset field.

 

[cornedbeef007-groups]

Don't bother. Incorporate that subquery into your update query (or DMax if
the subquery makes your query non-updateable)

Update sometable
set somefield = DMax("catalog_number","my_list","show_id = 1")
where ...

The benefit of doing it this way of course is that you don't have to worry
about concatenating delimiters and escaping characters in the data returned
from DMax if retrieving text or date/time data.

The only reason to use a variable is if you intended re-using the value
returned from the DMax or query multiple times in your procedure.

Without using a domain function (DLookup, DMax, etc.) the only alternative
is to use a recordset to retrieve the results of the query and then using
the value of the recordset field.

Looks good. Thanks.