Results 1 to 2 of 2

SQL code inside VBA sub inside Switchboard not working

  1. #1
    Bob Blooms is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Aug 2012
    Location
    FL and New England
    Posts
    19

    SQL code inside VBA sub inside Switchboard not working

    My first try at getting a query to run from the Switchboard failed.
    On the Property sheet I selected the Event tab; On Click I selected [Event Procedure]


    Then I entered the following code. My expectation was to see a visible tab displaying the result of my query. The query works fine when I double click it in the Access Object window. I have no idea what to do to make it work.

    The query is intended to compare the names in two tables as they must be equal to perform a cost allocation query.

    Code:
    Private Sub Option4_Click()
    
    Dim mySQL As String
    
    mySQL = "SELECT [tblAuthorizedPositions.Name] AS [AuthorizedPositionsName],[tblCS3Data.Name] AS [CS3DataName] FROM [tblAuthorizedPositions]RIGHT JOIN [tblCS3Data] ON [tblAuthorizedPositions.Name] = [tblCS3Data.Name]ORDER BY [tblAuthorizedPositions.Name]"
    
    DoCmd.RunSQL mySQL
    
    End Sub

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,830
    If you want to see your results, you will need to use the OpenQuery command (opening it will run it automatically). To do this, you will also need to assign your SQL code to a pre-defined query, i.e.

    Code:
    Private Sub Option4_Click()
        
    Dim mySQL As String
    
    mySQL = "SELECT [tblAuthorizedPositions.Name] AS [AuthorizedPositionsName],[tblCS3Data.Name] AS [CS3DataName] FROM [tblAuthorizedPositions]RIGHT JOIN [tblCS3Data] ON [tblAuthorizedPositions.Name] = [tblCS3Data.Name]ORDER BY [tblAuthorizedPositions.Name]"
    
    CurrentDb.QueryDefs("MyQuery").SQL = mySQL
    
    DoCmd.OpenQuery "MyQuery"
    
    End Sub
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Using InStr() inside Mid()
    By urbi in forum Programming
    Replies: 12
    Last Post: 06-06-2012, 12:00 PM
  2. Replies: 15
    Last Post: 04-11-2012, 02:17 PM
  3. Iif inside an update satement.
    By meet_binay in forum Queries
    Replies: 1
    Last Post: 03-05-2011, 02:36 PM
  4. SQL inside DLookup()
    By jgelpi16 in forum Programming
    Replies: 2
    Last Post: 03-02-2011, 07:41 PM
  5. Iif inside a update satement.
    By meet_binay in forum Queries
    Replies: 0
    Last Post: 02-18-2011, 04:18 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums