ACCESS 2010: VBA query error.

it will help if you upload a copy of the db

What is the error message you are getting?

The code stops on this line:

Set rstBarcode = dbs.OpenRecordset(strSql, dbOpenSnapshot, dbReadOnly)

The error message is:

Run-time error '3135':
Syntax error in JOIN operation.

The database contains linked tables to a secure Oracle database.  It would do no good to upload this database.  Plus, the data is proprietary and cannot be shared.

Just wondering if you could check the values in the "tbl_6months_fo" table in the following columns:
[tbl_6months_fo].[fms_key] and [tbl_6months_fo].[pack_id] .

Can you please check to see if there are any values that contain apostrophe, or some other suspicious special characters. If there are, please list these characters here, and we will help you to fix the code to work around these characters.

Here is some of the data in this table that uses special characters:

GEES/ACSC, HOWMET - WHITEHALL, 8-29, >30, <8

The rest of the data is numerical or alphanumerical.

I think the problem lies in how the original programmer nested the inner joins.  When I first looked at it, I noticed a lot of syntax errors.  I am not familiar with nesting JOINS and I could not figure out how to fix the error I am getting at this point.

The first step is to paste the SQL you posted into Access's query editor and try to view the query.  Does it throw errors?  Can you sort those out and get well-formed SQL that returns the records you expect?

I think the easiest solution will be to create a new query that joins these tables on the specified fields.  Switch over to SQL view and copy the FROM clause.

Paste the clipboard contents into this query, replacing the FROM clause.

Hi zepold,

First observation:

DatePart('yyyy',[FO_DATE],2,2) AS `Year`

note I surrounded Year with tickmarks as Year is a reserved word in MS Access VBA.

I simulated the table design and query and came up with:

SELECT QTY.FMS_KEY, REC.PACK_ID, QTY.FO_STATUS, QTY.FO_DATE, DatePart('yyyy',[FO_DATE],2,2) AS `Year`, DatePart('ww',[FO_DATE],2,2) AS Fw, Mid([ADN_NO],1,4) AS UNIT, REC.PARTNUMBER, REC.OPER_FROM, REC.OPER_TO_REPNO, SUP.SUPP_NAME
FROM FMS_DB_FO_QUANTITY AS QTY INNER JOIN ((FMS_DB_FO_PO_HEADER AS POH INNER JOIN FMS_DB_FO_SUPPLIER AS SUP ON POH.SUPPLIER_ID = SUP.SUPPIER_ID) INNER JOIN FMS_DB_FO_RECORD AS REC ON (POH.PO_NUMBER = REC.PO_NUMBER) AND (POH.PO_YEAR = REC.PO_YEAR)) ON (QTY.PACK_ID = REC.PACK_ID) AND (QTY.FMS_KEY = REC.FMS_KEY)
WHERE (((QTY.FMS_KEY)=[rstFO_Barcodes]![fms_key]) AND ((QTY.PACK_ID)=[rstFO_Barcodes]![pack_id]))
ORDER BY QTY.FO_DATE;

Hope this works out!

zepold,

It looks like this query called within a module and passed the parameters [rstFO_Barcodes]![fms_key] and [rstFO_Barcodes]![pack_id] from another open Recordset.

If so, you can code execution of this query thus:

Public Sub TestQuery()
On Error GoTo Err_h
Dim rst_Results As DAO.Recordset
Dim rstFO_Barcodes As DAO.Recordset
Dim fldField As New DAO.Field
Dim qdef As DAO.QueryDef
Dim prm As DAO.Parameter

On Error Resume Next
' try set querydef
Set qdef = CurrentDb.QueryDefs("tmpQ5vG7PPk1Y66dag_rrs3")
' Err.Number<>0 means it doesn't already exist
If (Err.Number <> 0) Then
    ' create new querydef
    Set qdef = New DAO.QueryDef
    ' with an unusual name
    qdef.Name = "tmpQ5vG7PPk1Y66dag_rrs3"
    ' set SQL
    qdef.SQL = "PARAMETERS [rstFO_Barcodes]![fms_key] Text ( 255 ), [rstFO_Barcodes]![pack_id] Text ( 255 );" & _
    "SELECT QTY.FMS_KEY, REC.PACK_ID, QTY.FO_STATUS, QTY.FO_DATE, DatePart('yyyy',[FO_DATE],2,2) AS [Year], DatePart('ww',[FO_DATE],2,2) AS Fw, Mid([ADN_NO],1,4) AS UNIT, REC.PARTNUMBER, REC.OPER_FROM, REC.OPER_TO_REPNO, SUP.SUPP_NAME " & _
    "FROM FMS_DB_FO_QUANTITY AS QTY INNER JOIN ((FMS_DB_FO_PO_HEADER AS POH INNER JOIN FMS_DB_FO_SUPPLIER AS SUP ON POH.SUPPLIER_ID = SUP.SUPPIER_ID) INNER JOIN FMS_DB_FO_RECORD AS REC ON (POH.PO_YEAR = REC.PO_YEAR) AND (POH.PO_NUMBER = REC.PO_NUMBER)) ON (QTY.FMS_KEY = REC.FMS_KEY) AND (QTY.PACK_ID = REC.PACK_ID) " & _
    "WHERE (((QTY.fms_key) = ""'"" & [rstFO_Barcodes]![fms_key] & ""'"") And ((QTY.pack_id) = ""'"" & [rstFO_Barcodes]![pack_id] & ""'"")) " & _
    "ORDER BY QTY.FO_DATE;"
    ' append to QueryDefs collection
    CurrentDb.QueryDefs.Append qdef
End If
Query_Exists:
On Error GoTo Err_h
    ' I set up this test to load a "fake" barcodes recordset
    Set rstFO_Barcodes = CurrentDb().OpenRecordset("FMS_BARCODE", dbOpenSnapshot)
    If Not rstFO_Barcodes.EOF Then rstFO_Barcodes.MoveLast
    If Not rstFO_Barcodes.BOF Then rstFO_Barcodes.MoveFirst
While (Not rstFO_Barcodes.EOF)
    ' load query parameters from barcode data
    qdef.Parameters("[rstFO_Barcodes]![fms_key]") = [rstFO_Barcodes]![fms_key]
    qdef.Parameters("[rstFO_Barcodes]![pack_id]") = [rstFO_Barcodes]![pack_id]
    ' execute parameterized query
    Set rst_Results = qdef.OpenRecordset(dbOpenSnapshot)
    If Not rst_Results.EOF Then rst_Results.MoveLast
    If Not rst_Results.BOF Then rst_Results.MoveFirst
    While (Not rst_Results.EOF)
        For Each fldField In rst_Results.Fields
            Debug.Print "Field: " & fldField.Name & ", Value: " & fldField.Value
        Next fldField
        rst_Results.MoveNext
    Wend
    rstFO_Barcodes.MoveNext
Wend
Exit_h:
' clean up
Set fldField = Nothing
Set rst_Results = Nothing
Set qdef = Nothing
Set rstFO_Barcodes = Nothing
Exit Sub
Err_h:
MsgBox Err.Description
Resume Exit_h
End Sub

This one worked.  Thank you all for your help.