Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Unanswered: Duplicate an SQL function in VBA

    Hi all,

    What I am trying to accomplish if can be done is create in VBA what I had previously done by creating a Query and then a Macro to activate it. What it does simply is upon clicking a CheckBox to Remove the check from the Print status of any tables underlying that were accidentally left unchecked.

    Currently I have a query like this:
    Code:
    UPDATE OrderItems SET OrderItems.Print = 0
    WHERE (((OrderItems.Print)-1));
    That's the query to uncheck the CheckBox to print out my report.
    Next is this:
    Code:
    Function ClearPrint()
    On Error GoTo ClearPrint_Err
    
        DoCmd.OpenQuery "qryClearPrint", acNormal, acEdit
    
    
    ClearPrint_Exit:
        Exit Function
    
    ClearPrint_Err:
        MsgBox Error$
        Resume ClearPrint_Exit
    
    End Function
    This runs the macro to deselect the "Print" CheckBox.
    What I want to do is to do it ALL in VBA without having to write a query at all? Is that possible? If so, how would I write it all in VBA to find that box and uncheck it. Just a thought on something I was trying to do.

    thanks in advance,
    Bud

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    the lazy way to get into this is to go back to your query, select SQL view (top-left button) and copy paste the SQL into your code.

    your code needs to look something like below.

    once you get used to SQL you just build the string directly in your code

    izy

    Function ClearPrint()

    dim strSQL as string

    On Error GoTo ClearPrint_Err

    ' dont need this any more DoCmd.OpenQuery "qryClearPrint", acNormal, acEdit

    strSQL = "UPDATE OrderItems SET OrderItems.Print = 0 "
    strSQL = strSQL & "WHERE (((OrderItems.Print)-1));"

    'you will have some errors with the linefeeds - either stuff it all on one line
    'by deleteing the linefeeds or insert some strSQL = strSQL & " like i did above
    'watch out for spaces!!!!! e.g. ...Print = 0WHERE... wont work!

    'then

    docmd.setwarnings false 'if you don't want a warning msg
    docmd.runsql strSQL
    docmd.setwarnings true
    'if you turned them false
    'and you're done

    ClearPrint_Exit:
    Exit Function

    ClearPrint_Err:
    MsgBox Error$
    Resume ClearPrint_Exit

    End Function
    currently using SS 2008R2

  3. #3
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Use the Docmd.RunSQL command.

    What you will need is a string that contains the SQL statement. If you're new to writing SQL, then create a query that does what you want and then view it in "SQL View" (the same button that switches between design view and query view). This will show you the SQL statement for the query you created.

    The help file has details.

    Have fun!
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  4. #4
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Smile

    Many thanks to you both. btw I found out earlier on that to avoid the warning messages I simply do:

    CurrentDB.Execute ("qryClearPrint")

    That way I wouldn't accidentally forget to turn the warnings back on again. Actually I am doing okie dokie in learning both VBA and SQL from being on here. I have viewed my queries the way suggested and have learned some. Also, used the ConvertMacrosToCode to learn VBA. Still learning but thanks to peeps like you both.

    now back to work,
    Bud

Posting Permissions

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