Results 1 to 3 of 3
  1. #1
    stephenvought is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Aug 2018
    Posts
    5

    AfterUpdate OpenQuery/UpdateQuery from a combo box

    I really didn't know what to title this, but hopefully this will help:

    I received help in this forum regarding an "Update Query", which helped tremendously. However, I am having a hard time with how to perform the "After Update", since I want to run the Query after updating a specific "completed date". By specific, I mean that the subform has a "validation type" (combo box), "completed by" (combo box), and "completed date" (text box). As a form, they create a table with the "validation type" as the rows of the first column, with the other two as additional columns. What I want to do is to perform the "After Update" upon entering the "completed date" OR "completed by" box that is in the same row as the "validation type" named "MVS Sent". The "validation types" are named, and ordered by numbers 1 through 37, with 37 being the "MVS Sent".


    Any help would be greatly appreciated. I can provide tables, relationships, etc., if needed to clarify.



    Thanks!!

  2. #2
    krausr79 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2014
    Posts
    16
    Ok, if I follow what you're saying you want the update to happen after both combo boxes are filled out. This solution assumes you're using VBA and that the combo boxes are empty until they choose something. I made a quickie form with 2 combo boxes (0 and 2) and put a subroutine call in each of them. Then in the subroutine I check if either is empty and exit if so. If not, it continues on to do stuff:

    Code:
    Private Sub Combo0_AfterUpdate()
    Call CheckThenUpdate
    End Sub
    
    
    Private Sub Combo2_AfterUpdate()
    Call CheckThenUpdate
    End Sub
    
    
    Private Sub CheckThenUpdate()
    If Nz(Me.Combo0, "") = "" Or Nz(Me.Combo2, "") = "" Then Exit Sub
    'do update query here
    End Sub

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Also need to check for "validation type" - 37 ("MVS Sent")

    Here is my version of the code:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub txtCompletedDt_AfterUpdate()   'text box for Completed date
        Call CheckThenUpdate
    End Sub
    
    Private Sub cboCompletedBy_AfterUpdate()  'combo box for Completed By
        Call CheckThenUpdate
    End Sub
    
    Private Sub CheckThenUpdate()
        If Me.cboValidationType = 37 And IsDate(Me.txtCompletedDt) And Len(Trim(Me.cboCompletedBy & vbNullString)) > 0 Then
            'do update query here
            CurrentDb.Execute "MyUpdateQuery", dbFailOnError
        End If
    End Sub
    BTW, this code is in a form module, not a standard module.

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

Similar Threads

  1. Openquery asks twice
    By damiancds in forum Queries
    Replies: 3
    Last Post: 08-06-2016, 03:55 AM
  2. DoCmd.OpenQuery really necessary?
    By Bleekscheetje in forum Programming
    Replies: 3
    Last Post: 09-05-2015, 08:44 AM
  3. Replies: 1
    Last Post: 10-07-2014, 12:42 PM
  4. Replies: 2
    Last Post: 06-09-2012, 01:56 AM
  5. Replies: 36
    Last Post: 10-04-2011, 11:19 PM

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