Pulling most recent record

[JeffreyDavid]

I have a table, ExchangeRate which has 2 fields - a date field and the exchange rate, and it gets updated manually everyday with that days exchange rate. I have a form with a text box and I want to display the most recent exchange rate.
How would one go about this ominous task?

 

[pono1]

Perhaps the easiest way is to put something like the following in the control source property of your textbox.

=DLookUp("ExRate","TblRates","ExDate=" & '#' & DMax("ExDate","TblRates") & '#')

• TblRates = Name of Table
• ExRate = Rate Field in TblRates.
• ExDate = Date Field in TblRates.

Since your table has so few fields and, I assume, holds only 1 record for each day of the year, this should be fine. But after about ten to fifteen years' worth of data is entered (in a multi-user database), code might be better -- that is, faster.

Very roughly...

'Pull today's rate from table...

Dim sSQL as string
Dim Rst as adodb.recordset 

Set rst = new adodb.recordset

sSQL = "SELECT TblRates.ExDate, TblRates.ExRate " & _
       "FROM TblRates " & _
       "WHERE TblRates.ExDate=" & '#' & Date & '#'

rst.Open sSQL, Currentproject.ActiveConnection
MyForm.MyTextBox = rst.fields("ExRate")

rst.close
Set rst = nothing

Regards,
Tim