CODEWITHSUNDEEP
sqlvbams-access
Tmyers
Tmyers

Reputation: 93

Getting error for too few parameters in SQL string in VBA

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?

Upvotes: 0

Views: 61

Answers (2)

Gustav
Gustav

Reputation: 55816

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] & "))"

Upvotes: 0

Erik A
Erik A

Reputation: 32642

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.

Upvotes: 1

Related Questions