Why the query is not reading the value entered on runtime?

[prabha_friend]

ECS Total:

SELECT Count(ECS.[Supply / Source]) AS [CountOfSupply / Source], Sum(Abs([Base Currency Amount])) AS ABS
FROM ECS LEFT JOIN [LOB Mapping] ON ECS.[Source Field Value] = [LOB Mapping].SRC_FLD_VALUE
WHERE (((ECS.[Effective Date])=[Forms]![Launch Pad]![txtCOB]));

'ECS
Set wbInputBook = Workbooks.Open(Me.txtECS.Value, ReadOnly:=True)
With wbInputBook
    .Sheets("Summary").Delete   'Unwanted Sheet (Hardcoded)
    With .Sheets(1)
    COBDate = .Range("B5").Value
    [I][B]Me.txtCOB.Value = COBDate
[/B][/I]    If Nz(DCount("*", "List of COBs"), 0) > 0 Then
        MsgBox "COB(" & COBDate & ") Already Processed. Hence Quitting"
        Exit Sub
    End If
        .Rows("1:22").Delete    'Headers
        For Each Header In .UsedRange.Rows(1).Cells
            Header.Value = Replace(Header.Value, Chr(10), "")
        Next Header
    End With
    .SaveAs CurrentProject.Path & "\Temp.xlsx", xlWorkbookDefault
    DoCmd.TransferSpreadsheet acImport, , "ECS", CurrentProject.Path & "\Temp.xlsx", True
    Me.Requery
[B][I]    Set rsTemp = CurrentDb.OpenRecordset("ECS Total")[/I][/B]
End With
wbInputBook.Close

Why am I getting "Too few parameters. Expected 1." Here?

Even the Physical Query "ECS Total" is not getting the run-time updated textbox's value. How to make the value entered in the textbox on runtime? and How to make the query read the value?

 

[namliam]

Because in the query you are fetching data from a form

When running a query from code that doesn't work... You either have to feed the parameter or substitute it in the SQL

 

[spikepl]

You should work on you googling skills. Googling is often as worthwhile as coding. This error is one of the most common with plenty of cases and advice out there. Use AWF for stuff you cannot google yourself. You search for Access error XXXX and see what comes up. Do that next time.

AN example for setting parameters for a stored query: http://www.access-programmers.co.uk/forums/showthread.php?t=281910

 

[prabha_friend]

Done with the following:

     Set MyQDef = CurrentDb.QueryDefs("ECS Total")
    With MyQDef
        For Each myParam In .Parameters
            With myParam
                .Value = Eval(.Name)
            End With
        Next myParam
    End With
    Set rsTemp = MyQDef.OpenRecordset