OpenRecordset Filter

[Martin]

Hello,

I am trying to open a recordset but with a filter where a column called
"Updated" is the max record. The column "Updated" is a Date/Time field and
is updated when the record is added (this is done previously by using
rs.Adnew).

Here is the code:

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("tbl Resource", dbOpenTable)

Can anyone help me undersand where I need to put the where clause or filter?

Thanks in advance.

Martin

 

[Rick Brandt]

Hello,

I am trying to open a recordset but with a filter where a column
called "Updated" is the max record. The column "Updated" is a
Date/Time field and is updated when the record is added (this is done
previously by using rs.Adnew).

Here is the code:

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("tbl Resource", dbOpenTable)

Can anyone help me undersand where I need to put the where clause or
filter?

Dim dbs as Database
Dim rs as Recordset
Dim sql as String

sql = "SELECT TOP 1 * " & _
"FROM [tbl Resource] " & _
"ORDER BY Updated DESC"

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset(sql, dbOpenDynaset)

You can use TOP 1 and ORDER BY since what you want is the Max value. In other
situations the sql string would just include the appropriate WHERE clause.