Query issues in Form (1 Viewer)

[AnilBagga]

I have a continuous subform where I am facing an issue.

The record source of the subform is included as Picture 1. The record source in datasheet view is Picture 2

The Subform in Form view is Picture 3. The problem I face is that the moment I start a new record in the sub form I get an error in one field see ID no 16. When I save the form, I get message as in Picture 4.

The qry used in the sub form qryRealisation is shown in Picture 5.

If I close the form and reopen it , there is no error and all data is in place. It is a requery issue possibly and I tried requery in after update of different fields and also on Current events of the sub form, but cannot resolve the issue.

The 2 expressions which are giving an error are as below

ExRate: IIf([SalesCurr]="USD",DLookUp("USD","tblexchangerates","month(monthyear)=" & Month([pricedate]) & " and year(monthyear)=" & Year([pricedate]) & ""),IIf([SalesCurr]="EUR",DLookUp("EUR","tblexchangerates","month(monthyear)=" & Month([pricedate]) & " and year(monthyear)=" & Year([pricedate]) & ""),DLookUp("GBP","tblexchangerates","month(monthyear)=" & Month([pricedate]) & " and year(monthyear)=" & Year([pricedate]) & "")))

SalesRateINR: [salesrate]*[ExRate]

SalesINRPerkg: (IIf([tblsalesdata].[UOMID]=1,[salesrateinr]/1000/[convrate]*[BagWtCorr],IIf([tblsalesdata].[UOMID]=2 Or [tblsalesdata].[UOMID]=3 Or [tblsalesdata].[UOMID]=11,[salesrateinr]/1000/[convrate],IIf([tblsalesdata].[UOMID]=4 Or [tblsalesdata].[UOMID]=5 Or [tblsalesdata].[UOMID]=6 Or [tblsalesdata].[UOMID]=7,[salesrateinr]/1000/([convrate]*[FinalGSM]*[widthbagorfabric]),[salesrateinr]/1000/([convrate]*[FinalGSM])))))*(1-Nz([Discounttotal],0))-Nz([appfreight],0)

Can someone help

 

[Ranman256]

do not use Dlookups in queries. Queries ARE the dlookup.
your codes EUR, GBP, etc, should be in a table, and that table should be joined in your query here.

if needed, make a single query, say Q1 that pulls only money codes and any time/month calculations. then add that into your main query.
don't overcomplicate the main query with lots of IIF's, you may get a QUERY TOO COMPLICATED error.

you cannot put a function in quotes: "month(monthyear)" , it will not resolve. Put functions in the query ,since you wont be using dlookups.

 

[Pat Hartman]

There are issues with your joins.

1. The Relationships are not set up correctly. Relationships defined in the relationship window are used to enforce Referential Integrity and are a necessary safety feature of all relational databases.
2. Several PKs are left at "ID" rather than given a proper name
3. Join lines in the query are not PK to FK. For example the join between qryCustDiscount and CustDiscTbl should be from a long integer in the query to the ID field in the table. The queries are obfuscating the schema but I only see ONE join line that is correct and that is the one between tblSalesData and tblStdUOM.

On the Customer RFQ form, it makes no sense to have Consignee in both the customer record and the sales data record. There is also no reason to show the two ID fields on the subform.

tblTotalFabricCost has no primary key. IF FGCode is unique, it could be the PK.