Getting error for too few parameters in SQL string in VBA

Question

I have a SQL string in a set of code in VBA for Access. The code is:

Private Sub Command40_Click()

Dim strSQL As String
Dim db As DAO.Database
    

    strSQL = "Delete * From TEMP_AssignSequence"
    
    Set db = CurrentDb()
    db.Execute strSQL

    Call ResetSeed("TEMP_AssignSequence")
    
    strSQL = "PARAMETERS [Forms]![JobQuote]![JobID] Short;"
    strSQL = "INSERT INTO TEMP_AssignSequence ( CaptionText )"
    strSQL = strSQL & " SELECT tblContractors.Contractor"
    strSQL = strSQL & " FROM tblContractors INNER JOIN tblContractorJob ON tblContractors.ContractorID = tblContractorJob.ContractorID"
    strSQL = strSQL & " WHERE (((tblContractorJob.JobID)=[Forms]![JobQuote]![JobID]))"
    strSQL = strSQL & " GROUP BY tblContractors.Contractor"
    strSQL = strSQL & " Order By tblContractors.Contractor;"

    db.Execute strSQL



End Sub

When I build the query in the design window, it outputs the same exact string. However, when I try to run this code, I get the error 3061: Too few parameters. Expected 1

I am a little confused since this runs perfectly when in a query, but wont run via VBA as a string. I assume I am handling my WHERE incorrectly as far as VBA is concerned?

This line here is overwritten by the next line - `strSQL = "PARAMETERS [Forms]![JobQuote]![JobID] Short;"` — Vityata, Oct 21 '20 at 13:38

score 1 · Accepted Answer · answered Oct 21 '20 at 13:36

1

You're executing through DAO, which does not support form-based parameters. If you want to use form-based parameters, you can only execute through DoCmd.RunSQL:

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Alternately, you could use a querydef to add parameters.

See How do I use parameters in VBA in the different contexts in Microsoft Access? for more on which parameters are supported in which context.

answered Oct 21 '20 at 13:36

Erik A

31,639
12
42
67

Ah, that makes sense. Would I just set strSQL as a normal string, then execute via docmd rather then db.execute? – Tmyers Oct 21 '20 at 13:49
I'd never do that, but my databases all contain a small function that takes an SQL string and parameters as a paramarray, then executes that SQL string using the given parameters, so I tend to use that. – Erik A Oct 21 '20 at 13:55

Gustav · Answer 2 · 2020-10-21T13:50:09.740

0

The parameter you need to concatenate:

strSQL = "INSERT INTO TEMP_AssignSequence ( CaptionText )"
    strSQL = strSQL & " SELECT tblContractors.Contractor"
    strSQL = strSQL & " FROM tblContractors INNER JOIN tblContractorJob ON tblContractors.ContractorID = tblContractorJob.ContractorID"
    strSQL = strSQL & " WHERE (((tblContractorJob.JobID)=" & [Forms]![JobQuote]![JobID] & "))"

edited Oct 21 '20 at 13:50

answered Oct 21 '20 at 13:42

Gustav

53,498
7
29
55

I tried making that change and get a syntax error ```Expected: End of statement```. I am not well versed in SQL, so I always use the wizards to build it for me, so I have a rough time when it comes to correcting such errors. – Tmyers Oct 21 '20 at 13:47
It was a string error, not SQL. This is air code, of course. – Gustav Oct 21 '20 at 13:51

Getting error for too few parameters in SQL string in VBA

2 Answers2