Update all records in subform (1 Viewer)

[kmanji]

I have created a Quotation form, that has a main form frmQuotationHeader, and subform frmQuotationLineItems. I have a UnitPrice field that is populated on the afterupdate event of the product selection. Ideally I would have solved this issue with an unbound form, but given that I want the price to be saved for the future when that particular quotation number is opened, I cannot have UnitPrice calculated on the fly.

My issue is that when a user has created a quotation in currency USD, and the client comes back and says quotation needs to be in GBP, and I change the currency in the header with an exchange rate, I have not been able to figure out how to have the afterupdate event of the selection field activated throughout the subform. I can only manage to do it for teh line that has focus, not the others.

For now, I have to keep triggering the GotFocus event of a field in the subform, but that is incredibly in efficient and prone to mistakes if i do not trigger the event on any line of the subform.

Any way I can get ALL line items with new UnitPrice when I update the currency in the header, knowing that I cannot use an unbound field, as I have to store UnitPrice for the future.

 

[JHB]

Use a recordset together with the subform's recordsetclone.
Or use an update query.

 

[Minty]

Use an update query on the after update of the header with a where condition that restricts the sub records to the quote you are on.

 

[sneuberg]

Usually you just update the underlying table of the subform and then requery the form.

 

[kmanji]

Sorry, I'm a relative newbie. Could you walk me through this? Or direct me to somewhere i can read-up?


Many thanks.

 

[jdraw]

kmanji,

Show us your tables and relationships (jpg )

For info on Update query.

Also, if you have to provide Quotes in different currencies, you might consider some mechanism to record in a standard currency and an exchange rate (with date and time stamp) to provide a calculation to other currencies. But exchange rates can be very volatile, so more thought/requirements/analysis are in order.

Good luck.

 

[kmanji]

Hi jdraw,

Attached a few tables relationships. Also took screenshots of how the currency rate is captured. Currency is not live, it is entered by the salesperson on the day of the quote. That rate is what the USD UnitPrice is supposed to multiply with to get the new UnitPrice in Tanzanian Shillings.

Like I noted earlier, it would solve if I would use an unbound field for UnitPrice, but I need to store the UnitPrice vaule for the future when we have to retrieve the quotation years later, when system prices have changed.

Please advise.

Kumail.

 

[jdraw]

So you need USD/GBP/TanzanianShillings or some combination (at least).

I realize you said the rates weren't live, but my guess is that you need:
-to know the USD to TanzShill and USD to GBP as at some DateOfQuote
in order to "translate" to another currency for display.

 

[kmanji]

Not really. For now we only have USD/TZS, and a pop-up asks for today's exchange rate when a user selects the secondary currency. Its manual, but works at our end as rates are provided daily to the salespersons to use as needed. One of the screenshots shows the currency rate below the currency select combobox, and one screenshot shows how i have managed it right now by having a user provide focus on the "code" field to update the new price. Its not efficient as is, and too prone to human error.

Im not sure if i have been able to explain the way its set-up satisfactorily...

 

[jdraw]

This is what gave me more than a hint

created a quotation in currency USD, and the client comes back and
says quotation needs to be in GBP

...rates are provided daily to the salespersons to use as needed..

This may be true, but do you record it so the info stays current with the quotation?

If it's working satisfactorily at the moment, then what is the issue?

Good luck with your project.

 

[kmanji]

The currency rate stays recorded.

The issue is not currency rate. This is the issue: each line item has a unit price in USD. After the whole quotation is made, and the user wants to change the currency to TZS, and changes the currency dropdown and insert the exchange rate - say 1usd to 2000tzs, i have no way of changing the line items unitprice from usd to tzs, unless i do so manually by firing off the gotfocus event of a field in each lineitem. This will be prone to human error and is not effective.

I would want - when a user updates currency type and currency tate, the afterupdate even of the currency type/rate to multiply all the uniprices of every entry in the lineitems to the TZS amount from the USD amount.

 

[jdraw]

Just typing while thinking. If you use USD for your base info, and you have a table with say USD to TZS and/or USD to GBP, then it seems you could have a query to adjust to the currency required. The issue is to record currency rates with dates so as changes occur, you can identify and use the appropriate rate.

Consider Part X whose Price is 25 USD (Dec10/2016). And ExchangeRate where 1 USD = 2123 TZS (on Dec10/2016) and the QuoteLine/OrderLine is for quantity 10 of Part X for Dec 10 2016.

Using Quantity*UnitPrice * ExchangeRate

If the RequiredCurrency is USD then the cost is
  Quantity * UnitPrice * ExchangeRate
      10      *   25    * 1                             =250 USD
If RequiredCurrency is TZS, then cost is
     10       *25     * 2123                          = 530750 TZS
 

where 
1 USD =  2123 TZS on Dec 10 2016

 

[kmanji]

Jdraw,

Please ignore how to sort currency. I have that sorted.

My question on this forum is how do all the unitprice's on each line item in a subform when i change an option on the main form. Thats it, whether its currency or something else.

 

[jdraw]

You display the appropriate currency depending on your requirements.

You could use a query, and use the exchange rate (for the proper date) to take part in the calculation of cost.

In overview: The cost, if USD is base, is qty*UnitPrice, otherwise Qty*UnitPrice*exchangerate

cost = Iif(ReqCurrency = USD, Qty*unitprice*1, qty*UnitPrice*exchangeRate)

 

[kmanji]

Jdraw,

Seems you do not understand what I am trying to achieve. Thanks for your help.

I will wait if anybody else can assist.

 

[jdraw]

Seems you do not understand what I am trying to achieve. Thanks for your help

.

Perhaps, but my guess is if you give the user the option to select a currency, then using that selection you can report/display the values you need.

You have not shown us where or how you want something displayed.

My suggestion was to store everything in 1 currency, keep an exchange rate and date for the other currencies, then when you report/display the info
-you determine the currency for display
-you run a query or function to apply the proper exchange rate to the stored unit price
-you display the data as per the requested currency.

Good luck with your project.

 

[Minty]

Can you post up the fields / controls on your form and the fields and table the sub-form are displaying. This should be a simple update query.

 

[sneuberg]

A simple update query, which I admit also suggesting, could become a messy solution if this is expanded to a lot of different currencies. Let's say the prices are converted from currency A to currency B but that turns out to be a mistake and they should have be converted to C. So is now it has to be converted form C to B or back to A and then to C. In any case this is going to get complicated. Jdraw's proposal of just keeping the stored values in dollars and applying conversion factor in the computations wouldn't have this drawback.

 

[kmanji]

Can you post up the fields / controls on your form and the fields and table the sub-form are displaying. This should be a simple update query.

The field in my form is:
in frmNewQuotation
user selects currency in dropdown Currency
and currency rate in field CurrencyRate which is filled by an inputbox when user selects Tanzanian Shilling as currency.

Note that there is only one pair of currency, not multiple, which keeps it simple. In this case, Tanzanian Shillings and US Dollars.

In the subform frmQuotationLineItemCurtains:
the afterupdate() event of the dropdown Catalogue activates the calculation in VBA that sets it as the value of UnitPrice of each line.

My challenge is when a user chooses currency in the form and sets the rate (manually) in the form, I want the afterupdate event of Catalogue on every line of the subform to kick off and update the UnitPrice of every line in the subform.

In addition, I have a number of tabbed subforms within the same form affected by this. In this case:
frmQuotationLineCurtains
frmQuotationLineRomans
frmQuotationLineTopDesign
frmQuotationLineAccessories

Would appreciate any assistance on this.

Thank you all.