Hi, I have one form to calculate selling prices based on various currencies and generating customer quotations.
Exchange rates and other parameters are entered on the MainForm
A SubForm is used and based on a query where I have all calculations in the query. The calculations are referring to parameters from the main form.
This works well as long I have the main form open, but I would like to have the calculated values stored in a table, so I can always re-open the quotation with the original values.
I have been trying to convert the SQL from my query to paste it in the "AfterUpdate" EventProcedure but not successfull.
Here is the SQL from Query:
Xrate: IIf([QCur]="THB" And [SupplierQuoteCurrency]="CHF",[Forms]![Quotations]![QuotationItems].[Form]![TC],
IIf([QCur]="THB" And [SupplierQuoteCurrency]="EUR",[Forms]![Quotations]![QuotationItems].[Form]![TE],
IIf([QCur]="USD" And [SupplierQuoteCurrency]="CHF",[Forms]![Quotations]![QuotationItems].[Form]![UC],
IIf([QCur]="CHF" And [SupplierQuoteCurrency]="EUR",[Forms]![Quotations]![QuotationItems].[Form]![CE],
IIf([QCur]="EUR" And [SupplierQuoteCurrency]="CHF",[Forms]![Quotations]![QuotationItems].[Form]![CE],
IIf([QCur]="USD" And [SupplierQuoteCurrency]="EUR",[Forms]![Quotations]![QuotationItems].[Form]![EU],
IIf([SupplierQuoteCurrency] Not Like "CHF" And [SupplierQuoteCurrency] Not Like "EUR" And [SupplierQuoteCurrency] Not Like "THB",[Forms]![Quotations]![QuotationItems].[Form]![OC],1)))))))
This is what I have converted so far but is not working:
Private Sub Xrate_AfterUpdate()
Me.Xrate =
(strSql = "IIf([Qcur]=""THB"" And [SupplierQuoteCurrency]=""CHF"",[Forms]![Quotations]![QuotationItems].[Form]![TC],
IIf([Qcur]=""THB"" And [SupplierQuoteCurrency]=""EUR"",[Forms]![Quotations]![QuotationItems].[Form]![TE],
Iif([Qcur]=""USD"" And [SupplierQuoteCurrency]=""CHF"",[Forms]![Quotations]![QuotationItems].[Form]![UC],
Iif([Qcur]=""CHF"" And [SupplierQuoteCurrency]=""EUR"",[Forms]![Quotations]![QuotationItems].[Form]![CE],
Iif([Qcur]=""EUR"" And [SupplierQuoteCurrency]=""CHF"",[Forms]![Quotations]![QuotationItems].[Form]![CE],
Iif([Qcur]=""USD"" And [SupplierQuoteCurrency]=""EUR"",[Forms]![Quotations]![QuotationItems].[Form]![EU],
Iif([SupplierQuoteCurrency] <> ""CHF"" And [SupplierQuoteCurrency] Not Like ""EUR"" And [SupplierQuoteCurrency] Not Like ""THB"",[Forms]![Quotations]![QuotationItems].[Form]![OC],1)))))))")
End Sub
Exchange rates and other parameters are entered on the MainForm
A SubForm is used and based on a query where I have all calculations in the query. The calculations are referring to parameters from the main form.
This works well as long I have the main form open, but I would like to have the calculated values stored in a table, so I can always re-open the quotation with the original values.
I have been trying to convert the SQL from my query to paste it in the "AfterUpdate" EventProcedure but not successfull.
Here is the SQL from Query:
Xrate: IIf([QCur]="THB" And [SupplierQuoteCurrency]="CHF",[Forms]![Quotations]![QuotationItems].[Form]![TC],
IIf([QCur]="THB" And [SupplierQuoteCurrency]="EUR",[Forms]![Quotations]![QuotationItems].[Form]![TE],
IIf([QCur]="USD" And [SupplierQuoteCurrency]="CHF",[Forms]![Quotations]![QuotationItems].[Form]![UC],
IIf([QCur]="CHF" And [SupplierQuoteCurrency]="EUR",[Forms]![Quotations]![QuotationItems].[Form]![CE],
IIf([QCur]="EUR" And [SupplierQuoteCurrency]="CHF",[Forms]![Quotations]![QuotationItems].[Form]![CE],
IIf([QCur]="USD" And [SupplierQuoteCurrency]="EUR",[Forms]![Quotations]![QuotationItems].[Form]![EU],
IIf([SupplierQuoteCurrency] Not Like "CHF" And [SupplierQuoteCurrency] Not Like "EUR" And [SupplierQuoteCurrency] Not Like "THB",[Forms]![Quotations]![QuotationItems].[Form]![OC],1)))))))
This is what I have converted so far but is not working:
Private Sub Xrate_AfterUpdate()
Me.Xrate =
(strSql = "IIf([Qcur]=""THB"" And [SupplierQuoteCurrency]=""CHF"",[Forms]![Quotations]![QuotationItems].[Form]![TC],
IIf([Qcur]=""THB"" And [SupplierQuoteCurrency]=""EUR"",[Forms]![Quotations]![QuotationItems].[Form]![TE],
Iif([Qcur]=""USD"" And [SupplierQuoteCurrency]=""CHF"",[Forms]![Quotations]![QuotationItems].[Form]![UC],
Iif([Qcur]=""CHF"" And [SupplierQuoteCurrency]=""EUR"",[Forms]![Quotations]![QuotationItems].[Form]![CE],
Iif([Qcur]=""EUR"" And [SupplierQuoteCurrency]=""CHF"",[Forms]![Quotations]![QuotationItems].[Form]![CE],
Iif([Qcur]=""USD"" And [SupplierQuoteCurrency]=""EUR"",[Forms]![Quotations]![QuotationItems].[Form]![EU],
Iif([SupplierQuoteCurrency] <> ""CHF"" And [SupplierQuoteCurrency] Not Like ""EUR"" And [SupplierQuoteCurrency] Not Like ""THB"",[Forms]![Quotations]![QuotationItems].[Form]![OC],1)))))))")
End Sub