Expected parameter when running query, runs fine with just clicking OK, but fails when run with VBA.

[sstiebinger]

All, I'm still pretty new to creating queries, so I'm sorry if this is a silly question...



I've got this query that works, but when I run it it asks for a parameter value for tblPricing.Partnum. If I just click OK without providing a value, it runs through as expected.

UPDATE tblPartsList RIGHT JOIN tblAll ON tblPartsList.Partnum=tblAll.PartNum SET tblPartsList.Partnum = tblAll.Partnum, tblPartsList.Price = tblAll.Price, tblPartsList.Vendor = tblAll.Vendor
WHERE tblPricing.Partnum IS NULL;

The problem is, I'm trying to run this from VBA with db.Execute and it immedietely throws an error for an expected parameter.

What is wrong with the query?

tblAll is a linked excel sheet with part numbers and pricing from vendors.
tblPartsList is an Access table.

The point of the query is to add new records to the Access table if partnum doesn't exist in the access table, but it does exist in the linked excel worksheet.

[JoeM]

If it is asking for a parameter, and you haven't set this up to be a parameter query, that means it does not recognize the field.
I think that issue is that in your parameter, you are trying to reference tblPricing.Partnum, but you haven't included the tblPricing table in your query (it would need to be in one of the FROM on JOIN clauses.

[sstiebinger]

Oh my... I feel like a fool.

Originally when I was mocking this up the access table was tblPricing and when I recreated it I changed it to tblPartsList but when I copied the query to the new DB I missed changing that....

sometimes it just takes a set of eyes that haven't been staring at it to notice the obvious... thanks.

Changed the reference to the proper table and it works as expected.

[sstiebinger]

Oops, I spoke too soon.

Running that as a query runs properly, but running the same query from VBA gives me a run-time error 3314 "You must enter a value in 'tblPartsList.Partnum' field.

Any ideas?

Here's the vba that I'm running... it errors on the line " db.Execute strSqlNew, dbFailOnError"

Private Sub PriceUpdate()
    Dim db As DAO.Database
    Dim strSqlNew As String
    Dim strSqlDisc As String
    Dim strSqlUpdate As String
    strSqlNew = "UPDATE tblPartsList RIGHT JOIN tblAll ON tblPartsList.Partnum=tblAll.PartNum " _
        & "SET tblPartsList.Partnum = tblAll.Partnum, tblPartsList.Price = tblAll.Price, " _
        & "tblPartsList.Vendor = tblAll.Vendor WHERE tblPartslist.Partnum IS NULL;"
    strSqlDisc = "UPDATE tblPartsList LEFT JOIN tblAll ON tblPartsList.Partnum=tblAll.PartNum " _
        & "SET tblPartsList.Legacy = True WHERE tblAll.Partnum IS NULL;"
    strSqlUpdate = "UPDATE tblPartsList INNER JOIN tblAll ON tblPartsList.Partnum=tblAll.PartNum SET tblPartsList.Price = tblAll.Price;"
    Set db = DBEngine(0)(0)
    db.Execute strSqlNew, dbFailOnError
    db.Execute strSqlDisc, dbFailOnError
    db.Execute strSqlUpdate, dbFailOnError
End Sub

Upon further thought, I think the issue is that the excel file has records with no part number, which I don't want to import, but the access tbl has data validation set to require a part number and not allow 0 length fields.

How do I catch that error and ignore it?

[sstiebinger]

Nevermind, I figured it out...

Private Sub PriceUpdate()
On Error GoTo Err_PriceUpdate
    DoCmd.SetWarnings False
    Dim db As DAO.Database
...
...
...
Exit Sub
Err_PriceUpdate:
    If Err = 3314 Then
        Err.Clear
    Else
        MsgBox Err.Description
    End If
Resume Next
End Sub