Update DLookUP function automatically (1 Viewer)

P

PhilUp

Registered User.
Local time
Today, 00:29
Joined
Mar 4, 2015
Messages
60
Hello,

I am trying to have some DLookUp functions updated automatically.

I have one table where I enter the monthly average exchange rate of currencies, the entry is made in the beginning of the following month.

I have one table, where I enter all invoices and date of invoice. At the time when I enter the date, the monthly rate is not yet available.

I have this code that is working ok, as long I have the Xrate already in the table:

Private Sub InvDate_AfterUpdate()
Me.[XrateAtInvoiceDate] = DLookup("[XrateInvDate]", "XrateQueryInvDate", "InvoiceID =" & Nz([InvoiceID], 0))
End Sub


How to have the values updated when I enter the exchange rate ?

Thank you for helping,
Philippe
 
Minty

Minty

AWF VIP
Local time
Today, 08:29
Joined
Jul 26, 2013
Messages
10,371
I would create a form that you enter the rates in. Put a command button on the form that runs an update query to update any invoices without an exchange rate ?
 
P

PhilUp

Registered User.
Local time
Today, 00:29
Joined
Mar 4, 2015
Messages
60
Thank you Minty,

I have already the form to enter the exchange rates. But the DLookUp function is on another form ( invoice Form).

How to generate the update ?
 
Cronk

Cronk

Registered User.
Local time
Today, 17:29
Joined
Jul 4, 2013
Messages
2,772
If you are entering/editing the exchange in the form, use the AfterUpdate of the text box to execute an update query to change all invoices with the same date.

If this is too cryptic and you want specific code, provide the name of your invoice table, the name of the exchange rate field and the name of the text box on the form where the exchange rate is entered/changed.
 
P

PhilUp

Registered User.
Local time
Today, 00:29
Joined
Mar 4, 2015
Messages
60
The name of my invoice table is "InvoiceTable"
The name of exchange rate form is "Xrates Monthly Means"
it contains following text boxes:

EUR
USD
THB to CHF
THB to EUR
THB to USD



There are several fields because we are handling two offices in different countries: Branch "XCH" will calculate equivalent value in CHF. Branch "XTH" will calculate in THB


The LookUp functions is getting the correct currency exchange rate from a query "XrateQueryInvDate" with the following formula:

XrateInvDate: IIf([InvoiceTable]![Branch]="XCH" And [InvoiceTable]![Currency]="EUR",[XRates Monthly Means]![EUR],IIf([InvoiceTable]![Branch]="XCH" And [InvoiceTable]![Currency]="USD",[XRates Monthly Means]![USD],IIf([InvoiceTable]![Branch]="XTH" And [InvoiceTable]![Currency]="CHF",[XRates Monthly Means]![THB to CHF],IIf([InvoiceTable]![Branch]="XTH" And [InvoiceTable]![Currency]="EUR",[XRates Monthly Means]![THB to EUR],IIf([InvoiceTable]![Branch]="XTH" And [InvoiceTable]![Currency]="USD",[XRates Monthly Means]![THB to USD],1)))))


Hope it understandable, thanks for help.
 
Cronk

Cronk

Registered User.
Local time
Today, 17:29
Joined
Jul 4, 2013
Messages
2,772
No, it's not very understandable to me. I avoid complicated nested IIf's - too easy to get it wrong, too hard to understand when modifying it later.

On the other hand, a set of nested if/elseif statements in a function is a lot easier to understand and maintain. Such as

Code: 
Function XrateInvDate(pBranch As String, pCurrency As String, pDate As Date)
   If pBranch = "XCH" And pCurrency = "EUR" Then
      XrateInvDate = DLookup("[EUR]", "[XRates Monthly Means]", "Format(XRateDate,'yyyymm')=" & Format(pDate, "yyyymm"))
   ElseIf pBranch = "XCH" And pCurrency = "USD" Then
      XrateInvDate = DLookup("[USD]", "[XRates Monthly Means]", "Format(XRateDate,'yyyymm')=" & Format(pDate, "yyyymm"))
   ElseIf pBranch = "XTH" And pCurrency = "CHF" Then
      XrateInvDate = DLookup("[THB to CHF]", "[XRates Monthly Means]", "Format(XRateDate,'yyyymm')=" & Format(pDate, "yyyymm"))
   ElseIf pBranch = "XTH" And pCurrency = "EUR" Then
      XrateInvDate = DLookup("[THB to EUR]", "[XRates Monthly Means]", "Format(XRateDate,'yyyymm')=" & Format(pDate, "yyyymm"))
   ElseIf pBranch = "XTH" And pCurrency = "USD" Then
      XrateInvDate = DLookup("[THB to USD]", "[XRates Monthly Means]", "Format(XRateDate,'yyyymm')=" & Format(pDate, "yyyymm"))
   Else
      XrateInvDate = 1
   End If
   
End Function
Note I didn't see anything to indicate the date format you have in your exchange rate lookup table, so you might have to change the above code.
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:29
Joined
May 7, 2009
Messages
19,233
or you can use Switch instead of IIF:

Code: 
XrateInvDate: Switch([InvoiceTable]![Branch]="XCH" And [InvoiceTable]![Currency]="EUR",[XRates Monthly Means]![EUR], _
[InvoiceTable]![Branch]="XCH" And [InvoiceTable]![Currency]="USD",[XRates Monthly Means]![USD], _
[InvoiceTable]![Branch]="XTH" And [InvoiceTable]![Currency]="CHF",[XRates Monthly Means]![THB to CHF], _
[InvoiceTable]![Branch]="XTH" And [InvoiceTable]![Currency]="EUR",[XRates Monthly Means]![THB to EUR], _
[InvoiceTable]![Branch]="XTH" And [InvoiceTable]![Currency]="USD",[XRates Monthly Means]![THB to USD])
 
P

PhilUp

Registered User.
Local time
Today, 00:29
Joined
Mar 4, 2015
Messages
60
Thanks a lot, yes, it is much easier this way.

So when will this code run ? At the time I enter the exchange rate in the form [XRates Monthly Means] ?

where shall I put the code ?
 
Minty

Minty

AWF VIP
Local time
Today, 08:29
Joined
Jul 26, 2013
Messages
10,371
Add it to the after Update event on the form control.
 
Cronk

Cronk

Registered User.
Local time
Today, 17:29
Joined
Jul 4, 2013
Messages
2,772
The code can go either in the AfterUpdate event or be in a module called from the AfterUpdate event. The latter is better if the code is to be run from more than one place eg other forms.

The original post:
Code: 
Private Sub InvDate_AfterUpdate()
Me.[XrateAtInvoiceDate] = DLookup("[XrateInvDate]", "XrateQueryInvDate", "InvoiceID =" & Nz([InvoiceID], 0))
End Sub

Based on using a function as in my last post, you would have (depending on your control names)

Code: 
Private Sub InvDate_AfterUpdate()
   Me.[XrateAtInvoiceDate] = XrateInvDate(Me.txtBranch, Me.txtCurrencyType, Me.TransactionMonth)
End Sub
 
P

PhilUp

Registered User.
Local time
Today, 00:29
Joined
Mar 4, 2015
Messages
60
Thanks for help, but I still don't quite understand how to set it up.

What I wnat to achive is :

When entering the exchange rate in the "form Xrates Monthly Means" I want the correct exchange rate ( either "EUR" or "USD" or "THB to CHF" or "THB to EUR" or "THB to USD" from query "XrateQueryInvDate") to go in the table InvoiceTable!XrateAtInvoiceDate
 
P

PhilUp

Registered User.
Local time
Today, 00:29
Joined
Mar 4, 2015
Messages
60
This is the query design for "XrateQueryInvDate"

 
P

PhilUp

Registered User.
Local time
Today, 00:29
Joined
Mar 4, 2015
Messages
60
This is the query design for "XrateQueryInvDate"
 

Attachments

  • XrateQueryInvDate.jpg
    XrateQueryInvDate.jpg
    95.3 KB · Views: 115
Cronk

Cronk

Registered User.
Local time
Today, 17:29
Joined
Jul 4, 2013
Messages
2,772
Run an update query to update data entered before exchange rates are known.
 
P

PhilUp

Registered User.
Local time
Today, 00:29
Joined
Mar 4, 2015
Messages
60
Yes, maybe this is the easiest way. Thanks.
 
You must log in or register to reply here.

Similar threads

M
Auto update of exchange rates
Replies
3
Views
429
SusieEdwards
S
M
Updating another field in the same record on update of another field
Replies
2
Views
314
The_Doc_Man
The_Doc_Man
N
Solved How to avoid the domain functions in Ms Access
Replies
13
Views
561
nector
N
C
Invalid Use of Null
2
Replies
20
Views
527
chuckcoleman
C
B
Dlookup problem
2
Replies
21
Views
830
ebs17
E

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom