Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ACCESS 2010: VBA query error.

Posted on 2014-01-23
15
Medium Priority
?
1,012 Views
Last Modified: 2014-03-06
Once again, I have inherited a database from a user that no longer works at my company.  There is a module that is designed to update several tables and it keeps erroring out at one point while trying to run a query.  I have not gone this deep in VBA programming and I have not been able to figure it out.  The error message has something to do with the inner joins.  Here is a sample of the query, but I will attach the actual code from the module:

SELECT QTY.FMS_KEY, QTY.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_RECORD AS REC  
  INNER JOIN (FMS_DB_FO_SUPPLIER AS SUP  
  INNER JOIN FMS_DB_FO_PO_HEADER AS POH  
  ON SUP.SUPPLIER_ID = POH.SUPPLIER_ID)  
  ON (REC.PO_NUMBER = POH.PO_NUMBER) AND (REC.PO_YEAR = POH.PO_YEAR))  
  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;
0
Comment
Question by:zepold
  • 5
  • 3
  • 2
  • +3
15 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39805238
it will help if you upload a copy of the db
0
 
LVL 25

Expert Comment

by:chaau
ID: 39805257
What is the error message you are getting?
0
 

Author Comment

by:zepold
ID: 39806457
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.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:zepold
ID: 39806468
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.
0
 
LVL 25

Expert Comment

by:chaau
ID: 39806576
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.
0
 

Author Comment

by:zepold
ID: 39806609
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.
0
 

Author Comment

by:zepold
ID: 39806614
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.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 39874699
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?
0
 
LVL 46

Expert Comment

by:aikimark
ID: 39874702
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.
0
 
LVL 13

Expert Comment

by:John Mc Hale
ID: 39875594
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!
0
 
LVL 13

Expert Comment

by:John Mc Hale
ID: 39875772
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
0
 
LVL 13

Accepted Solution

by:
John Mc Hale earned 2000 total points
ID: 39875781
zepold,

Correction:

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
Set qdef = CurrentDb.QueryDefs("tmpQ5vG7PPk1Y66dag_rrs3")
If (Err.Number <> 0) Then
    Set qdef = New DAO.QueryDef
    qdef.Name = "tmpQ5vG7PPk1Y66dag_rrs3"
    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;"
    Set prm = qdef.Parameters()
    CurrentDb.QueryDefs.Append qdef
End If
Query_Exists:
On Error GoTo Err_h
    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)
    qdef.Parameters("[rstFO_Barcodes]![fms_key]") = [rstFO_Barcodes]![fms_key]
    qdef.Parameters("[rstFO_Barcodes]![pack_id]") = [rstFO_Barcodes]![pack_id]
    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:
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


-------------------
Sample Output
-------------------
Field: FMS_KEY, Value: 1
Field: PACK_ID, Value: B
Field: FO_STATUS, Value:
Field: FO_DATE, Value: 16/02/2014
Field: Year, Value: 2014
Field: Fw, Value: 7
Field: UNIT, Value: F05F
Field: PARTNUMBER, Value: ABC123
Field: OPER_FROM, Value: JJ
Field: OPER_TO_REPNO, Value: GER
Field: SUPP_NAME, Value: ACME
0
 

Author Closing Comment

by:zepold
ID: 39910113
This one worked.  Thank you all for your help.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question