Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2012
    Posts
    7

    Unanswered: Simple VB query help

    I have a bunch of reports I need to convert from Query design to VBA in MS Access. Access will not recognize the DoCmd.OpenQuery command. What do I use instead? Thanks for your help.

    Private Sub Cabinet_Assy_Time2_Click()
    Dim CabinetAssyTimeButton As String

    'Build SQL statement
    CabinetAssyTimeButton = "SELECT [Cabinet Time].Cabinet, [Cabinet Time].TimeToReceiveCabinet, [Cabinet Time].TimeToWeldDoor,"
    CabinetAssyTimeButton = CabinetAssyTimeButton + " Nz([TimeToReceiveCabinet],0)+Nz([TimeToWeldDoor],0)"
    CabinetAssyTimeButton = CabinetAssyTimeButton + " AS TotalCabinetAssyTime"
    CabinetAssyTimeButton = CabinetAssyTimeButton + " FROM [Cabinet Time]"
    CabinetAssyTimeButton = CabinetAssyTimeButton + " WHERE ((([Cabinet Time].Cabinet) = '20104-101')) Or ((([Cabinet Time].Cabinet) = '20104-102'))"

    DoCmd.OpenQuery "CabinetAssyTimeButton"

    End Sub

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Kevin426 View Post
    I have a bunch of reports I need to convert from Query design to VBA in MS Access. Access will not recognize the DoCmd.OpenQuery command. What do I use instead? Thanks for your help.
    You need to create the query first (more precisely add a QueryDef object to the current database):
    Code:
    Private Sub Cabinet_Assy_Time2_Click()
        
        Dim CabinetAssyTimeButton As String
        Dim dbs As DAO.Database
        Dim qdf As DAO.QueryDef
        
        'Build SQL statement
        CabinetAssyTimeButton = "SELECT [Cabinet Time].Cabinet, [Cabinet Time].TimeToReceiveCabinet, [Cabinet Time].TimeToWeldDoor,"
        CabinetAssyTimeButton = CabinetAssyTimeButton + " Nz([TimeToReceiveCabinet],0)+Nz([TimeToWeldDoor],0)"
        CabinetAssyTimeButton = CabinetAssyTimeButton + " AS TotalCabinetAssyTime"
        CabinetAssyTimeButton = CabinetAssyTimeButton + " FROM [Cabinet Time]"
        CabinetAssyTimeButton = CabinetAssyTimeButton + " WHERE ((([Cabinet Time].Cabinet) = '20104-101')) Or ((([Cabinet Time].Cabinet) = '20104-102'))"
        Set dbs = CurrentDb
        For Each qdf In dbs.QueryDefs
            If qdf.Name = "CabinetAssyTimeButton" Then Exit For
        Next qdf
        If qdf Is Nothing Then Set qdf = dbs.CreateQueryDef("CabinetAssyTimeButton")
        qdf.sql = CabinetAssyTimeButton
        dbs.QueryDefs.Refresh
        Set qdf = Nothing
        Set dbs = Nothing
        DoCmd.OpenQuery "CabinetAssyTimeButton"
            
    End Sub
    Note: I did not check the validity of the SQL statement.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •