convert SQL to VBA (1 Viewer)

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

When you start using multiple Iif statements like this, it gets very complicated.
If you must do this, try adding one section at a time & check the output works before adding the next.

However this will get even worse to deal with every time you add a new currency.
I would change the approach completely
Create a table containing 3 fields, the 2currencies and a field giving the desired outcome.
Then write a query or function to select the required record from the table

Colin

Adding to ridders's comment, there are limits to the depth of nesting for IIF() and for the length of a query in general. You will almost certainly run into those limits sooner or later. Rethink this to at least a table-lookup for conversions. Try to find a generic formula or something else than this approach.

I also agree with ridders that this thing you are creating will be a nightmare to debug. Whereas if you COULD convert it to something that can do table lookups, your life will be SO much better.

Actually the query is running just fine. My problem is only to convert it to VBA.

Private Sub Xrate_AfterUpdate()
dim rst as recordset
set rst = currentdb.openrecordset("select * FROM YourExistingQuery")
Me.Xrate = rst!XRate

Thank Cronk, did as you suggested, but it is still not saving the value in the table....

After looking at what you posted, I don't SEE any SQL so I don't know what you are converting.

SQL requires a verb such as SELECT, SELECT INTO, INSERT INTO, UPDATE, or DELETE followed by a list of fields, a FROM clause listing tables (and possibly JOINs), an optional WHERE clause, and other potential qualifiers. I see none of those things. Don't be surprised if this doesn't work as expected. And I can't convert ANYTHING to SQL if there is no obvious SQL to be converted.

Please don't take this as an attempt to insult. It is quite common for new users to post what they think is crucial to our understanding of the problem only to find out that there is a disconnect between the post and the problem as we see it from the outside. You are often too close to the problem and thus have trouble stepping away from it to see it as WE would see it.

Thank you for your help. I did not post the full SQL from the query. The following id the formula I have in the query to pick up value for the "Xrate" field.

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)))))))

We come back to what is essentially a nested set of IIF cases. In the part of your code that starts with

Private Sub Xrate_AfterUpdate()
Me.Xrate =
(strSql = "IIf([Qcur]=""THB"" And [SupplierQuoteCurrency]=""CHF"",[Forms]![Quotations]![QuotationItems].[Form]![TC],

Click to expand...

That syntax includes Me.Xrate = (strSql= etc. etc. etc.

If I read this correctly, Me.Xrate will always equal FALSE (in Boolean) or 0 (in any numeric format). You are asking if variable strSQL equals something which appears to be numeric in format. The answer is very likely to be NO, they aren't equal.

This needs to be redesigned. In its current state, you are asking for difficulties. I believe this is a case where you have a two-key lookup table situation. You would have a key for QCur's value and another for SupplierQuoteCurrency's value.


Then you could do a DLookup for the two keys and for your "Not Like" case (which by the way should be <> meaning "not equal" because you don't use wild card characters), if the DLookup returned nothing, you just default to the OC case. Or something like that.

Ok, I am changing my approach to the exchange rate issue. I have now created currency sets, each set has one Xrate value based on a table where all Xrates are stored.

The issue I am facing now is that I need to store the currency into the items table, so I can create a relation between the tables.

I have tried and looked many forums to find the solution to store the value from one table to another but not successfull. For now I have one button with the code:

Private Sub Command88_Click()
Me.QuotationCurrency = DLookup("[QuoteCurrency]", "Quotations", "Quotations.quoteID=QuoteID")
End Sub

It works, but only on the record where the cursor is on. Cannot get it to work for all records on my continuous form.

How to use this DLookup function to store the value on all records ?

to clarify my previous post:

Setting of the currency for the quotation is on the main form based on "Quotations" table.

Currency of supplier is on continuous subform based on table "QuotationItems" table. All items in one quotations do not have the same supplier currency.

You should be able to use an update query or the equivalent as VBA code

Make the make the data source of the field as something like
DLookup("[QuoteCurrency]", "Quotations", "Quotations.quoteID=" & nz(me.QuoteID))