Default value in Form field (2 Viewers)

Siegfried

Registered User.
Local time
Today, 04:19
Joined
Sep 11, 2014
Messages
105
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:
Code:
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:
Code:
DMax("ExchangeRateDate";"ExchangeRateT";"CurrencyID=" & [CurrencyID])

But the combobox remains blank.
 

jdraw

Super Moderator
Staff member
Local time
Yesterday, 22:19
Joined
Jan 23, 2006
Messages
14,318
What is CurrencyID?
 

Siegfried

Registered User.
Local time
Today, 04:19
Joined
Sep 11, 2014
Messages
105
Hi Jdraw,

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

CurrencyT

CurrencyIDcurrency
1​
EUR
2​
GBP
3​
HKD
4​
USD
5​
NOK
 

Siegfried

Registered User.
Local time
Today, 04:19
Joined
Sep 11, 2014
Messages
105
Below screenshot of the form
1583346364862.png
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:19
Joined
Oct 29, 2018
Messages
18,972
Hi. Default Value is for new records only as soon as you enter the form, at which time your CurrencyID is null.
 

jdraw

Super Moderator
Staff member
Local time
Yesterday, 22:19
Joined
Jan 23, 2006
Messages
14,318
Do you have a column CurrecncyID in your table ExchangeRateT?
Your
DMax("ExchangeRateDate";"ExchangeRateT";"CurrencyID=" & [CurrencyID])

indicates it should.
 

Siegfried

Registered User.
Local time
Today, 04:19
Joined
Sep 11, 2014
Messages
105
Do you have a column CurrecncyID in your table ExchangeRateT?
Your
DMax("ExchangeRateDate";"ExchangeRateT";"CurrencyID=" & [CurrencyID])

indicates it should.

Yes, I have.
1583346908912.png
 

Siegfried

Registered User.
Local time
Today, 04:19
Joined
Sep 11, 2014
Messages
105
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).
1583347053047.png
 

Siegfried

Registered User.
Local time
Today, 04:19
Joined
Sep 11, 2014
Messages
105
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.

1583347951168.png
 

Attachments

  • 1583347822228.png
    1583347822228.png
    25.2 KB · Views: 384

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:19
Joined
Oct 29, 2018
Messages
18,972
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

Registered User.
Local time
Today, 04:19
Joined
Sep 11, 2014
Messages
105
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.

1583348529389.png


1583348562119.png
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:19
Joined
Oct 29, 2018
Messages
18,972
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

I’m here to help
Staff member
Local time
Yesterday, 19:19
Joined
Oct 29, 2018
Messages
18,972
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

Registered User.
Local time
Today, 04:19
Joined
Sep 11, 2014
Messages
105
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.
1583349000770.png


1583348876365.png
 

Siegfried

Registered User.
Local time
Today, 04:19
Joined
Sep 11, 2014
Messages
105
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

Registered User.
Local time
Today, 04:19
Joined
Sep 11, 2014
Messages
105
Yes, indeed.
OK, I'm trying to combine the DMax with DLookUp.


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

no luck with this one :unsure:
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:19
Joined
Oct 29, 2018
Messages
18,972
Code:
=DLookUp("ExchangeRateID";"ExchangeRateT"=DMax("ExchangeRateDate";"ExchangeRateT";"CurrencyID=" & [CurrencyID]))

no luck with this one :unsure:
You could try something like:
Code:
=DLookup("ExchangeRateID";"ExchangeRateT";"ExchangeRateDate=#" & 
Format(DMax("ExchangeRateDate";"ExchangeRateT","CurrencyID=" & [CurrencyID]),"yyyy-mm-dd") & "#")
(untested)
Hope it helps...
 

Users who are viewing this thread

Top Bottom