Default value in Form field (1 Viewer)

[Siegfried]

Dear experts,

I’m trying to set a default for a field (ExchangeRateID) on my form but I’m not getting any value (?)
On my form PurchaseOrderItemsFSub (the recordsource is the table PurchaseOrderItemT) I have following fields:

POItemID PK
POID FK
ProductID FK
ProductBrandID FK
ProductModel
ProductDescription
Quantity
UnitPrice
Discount
CurrencyID FK
ExchangeRateID FK

The field ExchangeRateID I have converted to a combobox and results the exchange rates from table ExchangeRateT which are sorted by currency and descending by date.

Control Source: ExchangeRateID
Row Source:

SELECT ExchangeRateT.ExchangeRateID, CurrencyT.CurrencyName, ExchangeRateT.ExchangeRate, ExchangeRateT.ExchangeRateDate FROM CurrencyT INNER JOIN ExchangeRateT ON CurrencyT.CurrencyID = ExchangeRateT.CurrencyID ORDER BY CurrencyT.CurrencyName, ExchangeRateT.ExchangeRateDate DESC;

Query1​
ExchangeRateID​	currency​	RoE:​	Date:​
5​	EUR	0,9162​	01-mrt-20​
3​	EUR	0,8054​	01-feb-20​
1​	EUR	0,8423​	01-jan-20​
6​	USD	1​	01-mrt-20​
2​	USD	1​	01-feb-20​
4​	USD	1​	01-jan-20​

The combobox ExchangeRateID I want to show the recent RoE (ExchangeRate) based on the CurrencyID selected (CurrencyID field has default EUR) for each new record.
The default value should be the most current exchange rate.
In the Query1 example: the default currency rate when choosing EUR would be 0,9162 as the date March 1st 2020 is the most recent date.

I was trying to enter following function in the property sheet of the combobox under the Default Value:

DMax("ExchangeRateDate";"ExchangeRateT";"CurrencyID=" & [CurrencyID])

But the combobox remains blank.

 

[jdraw]

What is CurrencyID?

 

[Siegfried]

Hi Jdraw,

CurrencyID list the available currencies.
Table: CurrencyT
CurrencyID PK
CurrencyName (EUR, USD, NOK, HKD, GBP)

CurrencyT

CurrencyID	currency
1​	EUR
2​	GBP
3​	HKD
4​	USD
5​	NOK

 

[Siegfried]

Below screenshot of the form

 

[theDBguy]

Hi. Default Value is for new records only as soon as you enter the form, at which time your CurrencyID is null.

 

[jdraw]

Do you have a column CurrecncyID in your table ExchangeRateT?
Your
DMax("ExchangeRateDate";"ExchangeRateT";"CurrencyID=" & [CurrencyID])

indicates it should.

 

[Siegfried]

Do you have a column CurrecncyID in your table ExchangeRateT?
Your
DMax("ExchangeRateDate";"ExchangeRateT";"CurrencyID=" & [CurrencyID])

indicates it should.

Yes, I have.

 

[Siegfried]

Hi. Default Value is for new records only as soon as you enter the form, at which time your CurrencyID is null.

I set default value for CurrencyID to EUR (CurrencyID = 1).

 

[theDBguy]

I set default value for CurrencyID to EUR (CurrencyID = 1).
View attachment 79565

Okay, that should help. But in the screenshot you posted in Post #4, the Exchange Rate combobox is not empty. Which combobox were you saying was empty?

 

[Siegfried]

Okay, that should help. But in the screenshot you posted in Post #4, the Exchange Rate combobox is not empty. Which combobox were you saying was empty?

Hi
Below is a screenshot of the form when starting a new record, as you can see, the default CurrencyID is there, EUR, but the ExchangeRateID remains empty.

 

[theDBguy]

Hi
Below is a screenshot of the form when starting a new record, as you can see, the default CurrencyID is there, EUR, but the ExchangeRateID remains empty.

View attachment 79567

Hi. Did you try preceding the expression with an equal symbol? Just checking...

 

[Siegfried]

Hi. Did you try preceding the expression with an equal symbol? Just checking...

Yes, I did.

 

[Siegfried]

The value of the combo box will show the exchange rate but from the drop down you can select by month. Property setting column widths.

 

[theDBguy]

Yes, I did.
View attachment 79568

Okay, couple of things. Your combobox row source is bound to the ExchangeRateID but probably hiding it and the CurrencyName and only showing the ExchangeRate column. However, your DefaultValue is looking up the ExchangeRateDate, which will "never" (at least, I don't think it will) match any ExhangeRateID from your Row Source.

 

[theDBguy]

The value of the combo box will show the exchange rate but from the drop down you can select by month. Property setting column widths.

View attachment 79570

View attachment 79571

You'll probably need to amend your DMax() expression to include a DLookup() of the ID column to match the row source of your combobox. In other words, if the combobox is bound to a number field, then the default value has to be a number too, not a date value. Hope that makes sense...

 

[Siegfried]

Okay, couple of things. Your combobox row source is bound to the ExchangeRateID but probably hiding it and the CurrencyName and only showing the ExchangeRate column. However, your DefaultValue is looking up the ExchangeRateDate, which will "never" (at least, I don't think it will) match any ExhangeRateID from your Row Source.

I kept ExchangeRate and ExchangeRateDate both visible.

 

[theDBguy]

I kept ExchangeRate and ExchangeRateDate both visible.
View attachment 79573

View attachment 79572

Don't think it matters. The point was the Bound Column. If the combo is bound to a numeric value, then the Default Value should also be a number - not a date. Agree?

 

[Siegfried]

Don't think it matters. The point was the Bound Column. If the combo is bound to a numeric value, then the Default Value should also be a number - not a date. Agree?

Yes, indeed.
OK, I'm trying to combine the DMax with DLookUp.

 

[Siegfried]

Yes, indeed.
OK, I'm trying to combine the DMax with DLookUp.

=DLookUp("ExchangeRateID";"ExchangeRateT"=DMax("ExchangeRateDate";"ExchangeRateT";"CurrencyID=" & [CurrencyID]))

no luck with this one

 

[theDBguy]

=DLookUp("ExchangeRateID";"ExchangeRateT"=DMax("ExchangeRateDate";"ExchangeRateT";"CurrencyID=" & [CurrencyID]))

no luck with this one

You could try something like:

=DLookup("ExchangeRateID";"ExchangeRateT";"ExchangeRateDate=#" & 
Format(DMax("ExchangeRateDate";"ExchangeRateT","CurrencyID=" & [CurrencyID]),"yyyy-mm-dd") & "#")

(untested)
Hope it helps...