Sub-queries with textbox-based parameter failing with Error 3061 (AC2007) (1 Viewer)

[AOB]

Hi guys,

I'm trying to perform a DELETE query at run-time. The criteria for deleting records is somewhat complex due to various one-to-many dependancies between the tables, overlapping of date fields etc., so in order to restrict the DELETE to only those records which qualify, I've set up a load of smaller queries to produce the list of eligible records.

There is only one parameter required - a 'CutOff' date which is arbitrarily chosen by the user and is available in a textbox control on an open form when the code is triggered.

Here is the main DELETE query :

DELETE DISTINCTROW [tblComments].* 
FROM [tblComments]
INNER JOIN [qryCommentsToPurge] 
  ON [tblComments].[CommentID] = [qryCommentsToPurge].[CommentID] 
WHERE [tblComments].[CommentID] = [qryCommentsToPurge].[CommentID]

I've put qryCommentsToPurge and all the cascading sub-queries below - have highlighted in red where the parameter value is required (it is used in the queries to identify the records which must be retained, i.e. not deleted)

Probably information-overload, so apologies for that, but just wanted to give the full picture...

Each of the individual sub-queries works fine and correctly (they return the correct population of records that they were designed to)

If I run the sub-queries manually, I am prompted to provide the CutOff date, as you would expect - and the recordset returned in each case is correct for that date.

If, however, I run the DELETE at run-time (with the form containing that textbox open and containing a valid date value) I get a 3061 error :

Too few parameters. Expected 1.

Suggesting that the queries can't pull the parameter value from the textbox on the form. But I've stuck a watch in the debugger on [Forms]![frmArchive]![txtCutOffDate] and I'm getting the correct date value? The form is open and available at the time.

So why can't the query see it? I use these kinds of control references elsewhere in other queries and they work fine. I don't understand why it's a problem here?

qryCommentsToPurge :

SELECT [tblComments].*
FROM [tblComments] 
INNER JOIN [qryIssuesToPurge] 
  ON [tblComments].[IssueID] = [qryIssuesToPurge].[IssueID]
ORDER BY [tblComments].[CommentID];

qryIssuesToPurge :

SELECT [tblIssues].*
FROM ([tblIssues] 
LEFT JOIN [qryIssuesAndAssociatedBalancesToRetain] 
  ON [tblIssues].[IssueID] = [qryIssuesAndAssociatedBalancesToRetain].[IssueID]) 
LEFT JOIN [qryBalancesAndAssociatedIssuesToRetain] 
  ON [tblIssues].[IssueID] = [qryBalancesAndAssociatedIssuesToRetain].[IssueID]
WHERE [qryIssuesAndAssociatedBalancesToRetain].[IssueID] Is Null 
  AND [qryBalancesAndAssociatedIssuesToRetain].[IssueID] Is Null
ORDER BY tblIssues.IssueID;

qryIssuesAndAssociatedBalancesToRetain :

SELECT [tblIssues].[IssueID], [tblIssues].[ValueDate], [tblIssues].[IssueResolveDate], [qryAssociatedBalancesPerIssue].[BalanceID], [qryAssociatedBalancesPerIssue].[BalanceDate]
FROM [tblIssues] 
LEFT JOIN [qryAssociatedBalancesPerIssue] 
  ON [tblIssues].[IssueID] = [qryAssociatedBalancesPerIssue].[IssueID]
WHERE [tblIssues].[IssueResolveDate] > [COLOR=red][Forms]![frmArchive]![txtCutOffDate][/COLOR] 
  OR [tblIssues].[IssueResolved] = False 
  OR ([tblIssues].[FollowUp] = True 
    AND [tblIssues].[FollowUpResolved] = False)
ORDER BY [tblIssues].[IssueID], [qryAssociatedBalancesPerIssue].[BalanceID];

qryBalancesAndAssociatedIssuesToRetain :

SELECT [tblBalances].[BalanceID], [tblBalances].[BalanceDate], [tblBalances].[Flag], [tblBalances].[BalanceResolved], [qryAssociatedIssuesPerBalance].[IssueID], [qryAssociatedIssuesPerBalance].[ValueDate], [qryAssociatedIssuesPerBalance].[IssueResolveDate]
FROM [tblBalances] 
LEFT JOIN [qryAssociatedIssuesPerBalance] 
  ON [tblBalances].[BalanceID] = [qryAssociatedIssuesPerBalance].[BalanceID]
WHERE [tblBalances].[BalanceDate] > [COLOR=red][Forms]![frmArchive]![txtCutOffDate][/COLOR] 
  OR ([tblBalances].[Flag] = True 
    AND [tblBalances].[BalanceResolved] = False)
ORDER BY [tblBalances].[BalanceID], [qryAssociatedIssuesPerBalance].[IssueID];

qryAssociatedBalancesPerIssue :

SELECT [tblIssues].[IssueID], [tblBalances].[BalanceID], [tblBalances].[BalanceDate]
FROM [tblIssues] 
INNER JOIN [tblBalances] 
  ON [tblIssues].[AccountID] = [tblBalances].[AccountID]
WHERE [tblBalances].[BalanceDate] >= [tblIssues].[ValueDate] 
  AND (([tblBalances].[BalanceDate] < [tblIssues].[IssueResolveDate]) 
    OR [tblIssues].[IssueResolved] = False)
ORDER BY [tblIssues].[IssueID], [tblBalances].[BalanceID];

qryAssociatedIssuesPerBalance :

SELECT [tblBalances].[BalanceID], [tblBalances].[BalanceDate], [tblIssues].[IssueID], [tblIssues].[ValueDate], [tblIssues].[IssueResolveDate]
FROM [tblBalances] 
INNER JOIN [tblIssues] 
  ON [tblBalances].[AccountID] = [tblIssues].[AccountID]
WHERE [tblIssues].[ValueDate] <= [tblBalances].[BalanceDate] 
  AND (([tblIssues].[IssueResolveDate] > [tblBalances].[BalanceDate]) 
    OR [tblIssues].[IssueResolved] = False)
ORDER BY [tblBalances].[BalanceID], [tblIssues].[IssueID];

 

[AOB]

Problem solved / workaround reached...

It seems the textbox on the form is out of scope for the sub-query. In order to get around the problem, I set up a temporary local table (one field, one record) via VBA at runtime (because the textbox, and its value, is available to the code)

Dim dbs As Database
Dim tdf As TableDef
Dim strSQL As String
 
Set dbs = CurrentDb
 
With dbs
 
  For Each tdf In .TableDefs
    If tdf.Name Like "tblTempCutOffDate" Then
      .TableDefs.Delete tdf.Name
      Exit For
    End If
  Next tdf
 
  strSQL = "SELECT #" & Format([Forms]![frmArchive]![txtCutOffDate], "mm/dd/yyyy") & "# AS CutOffDate " & _
           "INTO [tblTempCutOffDate]"
 
  .Execute strSQL
 
End With

Then modified my sub-queries to reference the temp table instead of the form control :

SELECT [tblIssues].[IssueID], [tblIssues].[ValueDate], [tblIssues].[IssueResolveDate], [qryAssociatedBalancesPerIssue].[BalanceID], [qryAssociatedBalancesPerIssue].[BalanceDate]
FROM tblIssues 
LEFT JOIN qryAssociatedBalancesPerIssue ON [tblIssues].[IssueID] = [qryAssociatedBalancesPerIssue].[IssueID]
WHERE [tblIssues].[IssueResolveDate] >= 
  [COLOR=red](SELECT Max([tblTempCutOffDate].[CutOffDate]) [/COLOR]
[COLOR=red]   FROM [tblTempCutOffDate])[/COLOR] 
OR [tblIssues].[IssueResolved] = False 
OR ([tblIssues].[FollowUp] = True 
  AND [tblIssues].[FollowUpResolved] = False)
ORDER BY [tblIssues].[IssueID], [qryAssociatedBalancesPerIssue].[BalanceID];

Then just drop the temp table when done

Means the queries can only work when the process is done via the VBA (but that's not necessarily a bad thing for me!)

Anyway, just thought I'd post this for anyone hitting the same problem...

Have a good weekend!