The solution is to use a query with selection criteria for your main form. Add a couple of text boxes with date fields or combos to give the user a starting point. The bound query will use the criteria fields on the main form header. You can still use a pass-through query but there is no need to. Just use linked tables and you'll be fine. You can also include a join to the mainform record and include criteria for the subforms as well.
We may be talking at cross purposes because that is exactly what I am already doing. The mainform is filtered to a single record and the recordsource is a pass through query from the SQL Server which pulls a single record. The mainform itself shows the budget line detail it can only be accessed by double clicking a budget line ID on a separate From that lists all budget codes.
I have tried multiple methods of filtering the mainform to a single record including:
Filtering when the calling form opens it from the double click event:
strWhereClause = "BL_ID = '" & Me.BL_ID & "' AND BL_Version = '" & Me.BL_Version & "'"
DoCmd.OpenForm "frmBudgetLineDetailed", acNormal, , strWhereClause, acFormReadOnly, acWindowNormal
Setting using Openargs on the calling form opens it from the double click eventto set up an ACE query for the recordsource as the main form opens:
DoCmd.OpenForm "frmBudgetLineDetailed", acNormal, , , acFormReadOnly, acWindowNormal, Me.BL_ID & "," & BL_Version
Using the double click event of the calling form to create a Passthrough Query QDF using DAO that returns a single record and using this as the recordsource for the main form.
All the above methods work none of them have any bearing on the fact the form takes around 10 seconds to open and Access reports (Not Responding)
This issue is down to the subforms, although these are filtered via the parent to child link of BL_ID, the server pulls 19,000 records and filters on the local machine. I can prove this by deleting the subforms and the main form then opens instantly.
As an experiment I decided to set the recordsource for both these sub forms as a Local passthrough query which is executed on the server. Then I opened each subform as a standalone form and they open fine. but the second I try to open up the main form containing these subforms I get the error shown below:
"You can't use a pass-through query or a non-fixed-column crosstab query as a record source for a subform or subreport"
You can still use a pass-through query ...
The above error occurs if I do.
Although interestingly it only occurs for the invoices sub form, the error does not occur for the purchase orders sub form even though both are using pass-through queries.
The pass-through query for the invoices subform is:
SELECT tblInvoicesCurrent.[Invoice Number]
, tblInvoicesCurrent.[Invoice Date]
, tblInvoicesCurrent.Currency
, tblInvoicesCurrent.[Invoice Nett Total]
, [Invoice Nett Total]/[Exchange Rate] AS GBPTotal
, tblInvoicesCurrent.[Invoice Tax]
, tblInvoicesCurrent.[Invoice Total]
, tblInvoicesCurrent.Terms
, tblInvoicesCurrent.Supplier
, tblInvoicesCurrent.[Supplier Code]
, tblInvoicesCurrent.[Receipt Date]
, tblInvoicesCurrent.[Due Date]
, tblInvoicesCurrent.[Posting Date]
, tblInvoicesCurrent.[PO Number]
, tblInvoicesCurrent.[Authorisation Status]
, tblInvoicesCurrent.[Posting Status]
, tblInvoicesCurrent.[Authorised by]
, tblInvoicesCurrent.[Rejected by]
, tblInvoicesCurrent.Department
, tblInvoicesCurrent.Company
, tblInvoicesCurrent.LastUpdated
, tblInvoicesCurrent.UniqueID
, IIf(([BudgetRef] is null),[tblInvoicesCurrent].[BudgetLine],[BudgetRef]) AS Budget
FROM (tblInvoicesCurrent LEFT JOIN tblInvoicesCustomFields ON tblInvoicesCurrent.UniqueID = tblInvoicesCustomFields.InvoiceUniqueID) INNER JOIN tblPurchaseOrdersCurrent ON tblInvoicesCurrent.[PO Number] = tblPurchaseOrdersCurrent.[PO Number]
WHERE IIf(([BudgetRef] is null),[tblInvoicesCurrent].[BudgetLine],[BudgetRef]) = 'ABC123'
. Still using pass-through query for main form and conventional client side Access queries for sub-forms.
