Hi,
I have a table "Suppliers" and a table "SuppliersApprovalDocs". Both a a relationship on the SuppliersID field. Within the SuppliersApprovalDocs table there is a date field [DocExpiryDate] and a chk field for [DocRenewalReceived].
If the DocExpiryDate field = <Date() and the DocRenewalRecieved field = 0 then the document is both out of date and no renewal has been received therefore that supplier has an expired document and no longer meets the company approval requirements and needs to be flagged as 'NOT APPROVED'. The supplier may of course have several expired documents in some cases.
I created a totals query "qrySupplierApprovalTotals" to show the number of documents each supplier has that are effectively not approved.
I then have a 'frmPurchaseOrders" form based on "qryPurchaseOrders". I wanted to bring the totals query "qrySupplierApprovalTotals" above in as a source to this main query so on the PurchaseOrder form I could use conditional formatting to show in RED if that supplier was now no longer approved by simply saying If "Approved
" >0 then format RED otherwise format GREEN.
Trouble is when I bring the totals query in as datasource to main qryPurchaseOrders it then locks the main query for editing. I knew you could not edit a totals query but didn't realise if used as a source in this manner it would do the same thing.
Is there any way round this or am I looking at it in the wrong way?
I have a table "Suppliers" and a table "SuppliersApprovalDocs". Both a a relationship on the SuppliersID field. Within the SuppliersApprovalDocs table there is a date field [DocExpiryDate] and a chk field for [DocRenewalReceived].
If the DocExpiryDate field = <Date() and the DocRenewalRecieved field = 0 then the document is both out of date and no renewal has been received therefore that supplier has an expired document and no longer meets the company approval requirements and needs to be flagged as 'NOT APPROVED'. The supplier may of course have several expired documents in some cases.
I created a totals query "qrySupplierApprovalTotals" to show the number of documents each supplier has that are effectively not approved.
I then have a 'frmPurchaseOrders" form based on "qryPurchaseOrders". I wanted to bring the totals query "qrySupplierApprovalTotals" above in as a source to this main query so on the PurchaseOrder form I could use conditional formatting to show in RED if that supplier was now no longer approved by simply saying If "Approved
" >0 then format RED otherwise format GREEN.
Trouble is when I bring the totals query in as datasource to main qryPurchaseOrders it then locks the main query for editing. I knew you could not edit a totals query but didn't realise if used as a source in this manner it would do the same thing.
Is there any way round this or am I looking at it in the wrong way?