Results 1 to 9 of 9
  1. #1
    CementCarver's Avatar
    CementCarver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Location
    Canada
    Posts
    231

    Run DoCmd.RunSQL from MsgBox Input

    I need to update a certain field in the database via a MsgBox input.

    Does anyone have a snippet of code that they can share for this?

    Appreciate it....CementCarver

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    I did not test it. Here is some code I put together. You can incorporate a string and an integer in the msgbox. This is a vbYesNo. You can add a cancel in there too. I thing that is vbYesNoCancel. You can check "MsgBox" in the help files for reference.

    Code:
        Dim strItem As String
        strItem = Me.ItemNum.Value
        
        Dim intCount As Integer
        intCount = Me.RecordsetClone.RecordCount
        Dim Msg, Style, Title, Response
        Msg = " Do you want to remove all " & intCount & _
        " items '" & strItem & "' from the current Recordset ?" ' Define message.
        Style = vbYesNo + vbQuestion + vbDefaultButton2    ' Define buttons.
        Title = "Verify Removal"    ' Define title.
        Response = MsgBox(Msg, Style, Title)
        If Response = vbYes Then    ' User chose Yes.
        [Insert your do something here]
        Else
        MsgBox "You chose No. ", vbInformation, "Action Stopped"
        Exit Sub
        End If

  3. #3
    CementCarver's Avatar
    CementCarver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Location
    Canada
    Posts
    231
    Thanks ItsMe,

    Is there no DoCmd.RunSQL to run after the msgbox takes the input from the user?

    I'm confused with your code sample. Seems too much for me.....CC

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    It is a complex example, for a MsgBox.

    I did not address the actual issue of running a query. I provided an example of how to ask the user for input and determine whether or not to run the query. the example is a MsgBox.

    The example is good because it has the ability to communicate a string variable and a number data type variable within the message box. This complicates the example. However, it is important to communicate dynamic information to the user so they cvan provide an appropriate answer.

    Here is a sample yes no that is a little more basic. You can try it out in a sample form.
    Place this code in a blank form with a control button' click event.
    Code:
        Dim Msg2, Style2, Title2, Response2 'Make some declarations
        Msg2 = " Access wants to know the answer. Is it Yes or No?  "   'Define message
        Style2 = vbYesNo + vbInformation + vbDefaultButton1    ' Define buttons.
        Title2 = "Question"    ' Define title.
        Response2 = MsgBox(Msg2, Style2, Title2) 'This is where the message box pops up for the user
        
        'Now you need to handle the user input
        'The user has two options in this case
        'Yes or No. We will handle the input with an
        'If Then Else statement
        
        If Response2 = vbYes Then    ' User chose Yes.
        MsgBox "Congratulations, you chose Yes. "
        Else
        MsgBox "You chose No. Goodbye! ", vbInformation, "Action Stopped"
        Exit Sub
        End If

  5. #5
    CementCarver's Avatar
    CementCarver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Location
    Canada
    Posts
    231
    thanks ItsMe, so after the user elects to pick yes from the msgbox, under the line MsgBox "Congratulations...." you'd put the DoCmd.RunSQL statement? If so, how do you incorporate the msgbox input box into the DoCmd.RunSQL statement?

    CementCarver

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by CementCarver View Post
    If so, how do you incorporate the msgbox input box into the DoCmd.RunSQL statement?

    CementCarver
    I don't believe it is possible to incorporate a MsgBox into an SQL query.

    Your action query is totally separate from your MsgBox. You place the name of your action query directly after the docmd and the action query runs, unimpeded.

    Docmd.RunSQL strSQLUpdate

    In this example, the string variable, "strSQLUpdate", will execute. If you place this within the If, Then, Else statement. It will run without any further user interaction.

  7. #7
    CementCarver's Avatar
    CementCarver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Location
    Canada
    Posts
    231
    I was hoping to take an input from the user, keyed in while the message box is active, and then take that keyin and incorporate it into the strSQLUpdate statement.

    When you say "In this example...." the strSQLUpdate will execute.... but there is no such line in your example above, so I'm confused again. Sorry.

    CC

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    When I look at post # 1, I see two separate actions. I see an Action Query and I see a MsgBox.

    Post #7 leads me to believe that you want the user to help build the Action Query via a MsgBox. It is difficult to see this reading post #1 because this is not a conventional approach.

    There is a way to have an input box pop up. Not sure how to describe it because I do not employ it. Basically, it acts like a MsgBox but has a field that the user types text into. A message box is only going to return Yes, No, or Cancel.

    If you need user input to create an Action Query, you need to use many features that a form has. You may want to combine the form's recordset with comboboxes and list boxes. I usually use a series of cascading dependent comboboxes to create an SQL query. You can place them in an unbound form.

    The first MsgBox example I posted would already know what the Action query is. It has the capability to communicate some of what the SQL is to the user via a string variable and an integer variable. The user reviews the message and then commits or declines. The If, Then, Else statement manages the user input and decides whether or not to RUN the predetermined Action Query.

  9. #9
    CementCarver's Avatar
    CementCarver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Location
    Canada
    Posts
    231
    Thank you for a great explanation of the code you've commented on. And yes, I do agree that your code does determine what the user is wanting to do at that time. I appreciate your support and in depth explanations. CementCarver

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

Similar Threads

  1. DoCmd.RunSQL
    By Access_Blaster in forum Programming
    Replies: 6
    Last Post: 07-23-2013, 09:27 PM
  2. my first DoCmd.RunSQL
    By broecher in forum Programming
    Replies: 4
    Last Post: 11-05-2010, 09:35 PM
  3. docmd.runsql update
    By Mitch_Aus in forum Programming
    Replies: 2
    Last Post: 10-05-2010, 09:45 AM
  4. Problem with DoCmd.RunSQL
    By Peter01 in forum Programming
    Replies: 2
    Last Post: 09-10-2009, 07:11 PM
  5. docmd.runsql not working
    By Rameez in forum Programming
    Replies: 3
    Last Post: 08-07-2009, 10:07 AM

Tags for this Thread

Posting Permissions

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