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
– Vityata, Commented Oct 21, 2020 at 13:38

Erik A · Accepted Answer · 2020-10-21 13:36:10Z

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, 2020 at 13:36

Erik A

32.8k13 gold badges48 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tmyers Over a year ago

Ah, that makes sense. Would I just set strSQL as a normal string, then execute via docmd rather then db.execute?

Erik A Over a year ago

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.

Gustav · Accepted Answer · 2020-10-21 13:50:09Z

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, 2020 at 13:50

answered Oct 21, 2020 at 13:42

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

2 Comments

Tmyers Over a year ago

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.

Gustav Over a year ago

It was a string error, not SQL. This is air code, of course.

Collectives™ on Stack Overflow

Getting error for too few parameters in SQL string in VBA

2 Answers 2

2 Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Linked

Related