Solved

How do I get fix code to not get Run time Error 2147217900

Posted on 2011-02-25
4
350 Views
Last Modified: 2013-11-28
I am trying to troubleshoot a database that after the user enter the necessary dates and hit enter gets a run time error 2147217900(80040e14) Synatx error (missing operator) in query expression [ItemNumber] > 0 and [SwitchboardID]=" & Me.[SwitchboardID]

Below is the Code
' Open the table of Switchboard Items, and find
    ' the first item for this Switchboard Page.
    Set con = Application.CurrentProject.Connection
    stSql = "SELECT * FROM [Switchboard Items]"
    stSql = stSql & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" & Me.[SwitchboardID]
    stSql = stSql & " ORDER BY [ItemNumber];"
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open stSql, con, 1   ' 1 = adOpenKeyset
Could someone please help.  Thanks
0
Comment
Question by:deefel
  • 2
4 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 50 total points
Comment Utility
maybe [SwitchboardID] is Text Data Type

    stSql = "SELECT * FROM [Switchboard Items]"
    stSql = stSql & " WHERE [ItemNumber] > 0 AND [SwitchboardID]='" & Me.[SwitchboardID] & "'"
    stSql = stSql & " ORDER BY [ItemNumber];"
 
0
 
LVL 84
Comment Utility
Also make sure you've spelled everything correctly.
0
 
LVL 15

Expert Comment

by:cquinn
Comment Utility
You don't use CreateObject ti instantiate a recordset in Access

0
 
LVL 84
Comment Utility
You don't TYPICALLY use CreateObject to build a Recordst, but there's nothing wrong with using CreateObject to build a Recordset (other than you don't get Intellisense).

In fact there is a new issue out that could force you to use Late Binding to build ADO recordsets:

http://social.msdn.microsoft.com/Forums/en-US/windowsgeneraldevelopmentissues/thread/3a4ce946-effa-4f77-98a6-34f11c6b5a13/?prof=required

Seems that Servic Pack 1 for Win7 can break the mosadoxx.dll associations (the ID for that interface changed), which means you must either (a) register an earlier version of that TypeLib or (b) user Late Binding.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

771 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now