Working with the last entry in a table

[Thomasso]

Hi everyone,

can you please help me tackle an issue that I am dealing with?



I have a table called tblMarketData where I manually store today's currency exchange rates and VAT.

I want to be able to work with today's data (therefore the latest entry). The entry pop-up automatically opens when I start the database for the first time that day, so when the DB is opened, it always has today's data.

What I want to do:

• show today's currency exchange rates in the main menu form header
• make calculations with them (for example products value in a different currency)
• and so on...

So basically I need some kind of a global access to the numbers. I want to use it in expression builder, queries, maybe even VBA.

What would be the most ellegant way to do it?

Thanks a lot!

Best regards,
Tomas

[orange]

Do you save each day's exchange rates, or only those for today?
If the latter, then why not update a tblCurrentExchangeRates with today's data?
You could reference the table records throughout your application.

If you save each day's rates then you could create a recordset based on the table and a
WHERE clause to get data with the latest exchangeRateDate.

Good luck with your project.

[JoeM]

Does your date field have a time component too, or is it just date?
If just date, you can easily create a query to dynamically return the data for the current date by putting the following on the Criteria line under your date field (in Query Builder):
=Date()

[Thomasso]

Thank you guys both for the input!

1. I store each day's rates

2. No time, date only. I was able to create the qryTodayMarketData, which has 1 record with today's data.


My question is how to use these values? For example... Let's say that I want to display today's VAT (from qryTodayMarketData) in the main menu form (frmMainMenu). I tried adding a text box and in the expression builder I wrote: =[qryTodayMarketData]![VAT] ... Unfortunately it returned the "NAME?" error. I know that this is wrong, but I am not sure what is the correct way.

And another example - let's say that I have a query with products on stock. The product prices are listed in USD. I want to show their cumulative value in EUR. This I feel that I know how to do. I would just create a query and use the above query to feed the data. Right?

But I am stomped when it comes to using it in forms.

Thanks again!

Tomas

[JoeM]

You can use Queries as the Data Source of your Form just as easily as you can Tables.
So, instead of using the Table as the Data Source of your Form, simply use qryTodayMarketData.
Then you can just drag and drop the VAT field to the Form (in the Form Builder).

In your query (qryTodayMarketData), you can also add a calculated field to convert the values from USD to EUR, and use that in your Form too.