Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2014
    Posts
    10

    Unanswered: Set Warnings False?????

    I have a split DB where multiple users are accessing the front end and storing data into the back end. In several forms I have append queries set up through buttons and have the "Set Warnings" option to False for each one of them. When I use the forms and click the buttons to run the append queries I have no problems. No warnings pop up. There are several users where this is not the case though, they get the warning messages when they run the append queries. Why does this happen and how can I fix it? Any help would be much appreciated.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Instead of using OpenQuery or another DoCmd macro-like command, you can use the Execute method of the CurrentDb object.

    2. Solutions:
    a) You convert all queries to dynamic queries. Suppose that now you have a Query named Query1, like this:
    Code:
    UPDATE [SomeTable] SET [ColumnX] = 'SomeValue' WHERE [ColumnY] = 'SomeCriteriaValue';
    In the CommandButton_Click event handler, you use:
    Code:
    Private Sub CommandButton_Click ()
    
        Dim stDocName As String
    
        stDocName = "Query2"
        DoCmd.SetWarnings False
        DoCmd.OpenQuery stDocName, acNormal, acEdit
        DoCmd.SetWarnings True
        
    End Sub
    You can convert to:
    Code:
    Private Sub CommandButton_Click ()
    
    '    Dim stDocName As String
    '
    '    stDocName = "Query2"
    '    DoCmd.SetWarnings False
    '    DoCmd.OpenQuery stDocName, acNormal, acEdit
    '    DoCmd.SetWarnings True
    
        Dim strSQL As String
        
        strSQL = "UPDATE [SomeTable] SET [ColumnX] = 'SomeValue' WHERE [ColumnY] = 'SomeCriteriaValue';"
        CurrentDb.Execute strSQL, dbFailOnError
        
    End Sub
    b) This can be tedious if many queries are in use. Another solution consists in keeping the queries and retrieving their SQL property:
    Code:
    Private Sub CommandButton_Click ()
    
    '    Dim stDocName As String
    '
    '    stDocName = "Query2"
    '    DoCmd.SetWarnings False
    '    DoCmd.OpenQuery stDocName, acNormal, acEdit
    '    DoCmd.SetWarnings True
    
        Dim qdf As DAO.QueryDef
        Dim strSQL As String
        
        Set qdf = CurrentDb.QueryDefs("Query2")
        strSQL = qdf.SQL
        CurrentDb.Execute strSQL, dbFailOnError
        qdf.Close
        Set qdf = Nothing
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by kmullins76 View Post

    ...I have a split DB where multiple users are accessing the front end and storing data into the back end...
    Is the above, in red, to be interpreted as saying that the multiple users access a single Front End, or does each user have a copy of the Front End on their respective hard drives?

    If the former is true, there is no limit to the kinds of problems you can encounter, including that which is now occurring.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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