Home  Fees/Services  Access 2010 Template  Access Tutorial  Access Download  Articles  Search  Contact  Privacy  Links

Detail Master Update Query in Visual Basic
Visual Basic Child-Parent Update Query






Visual Basic Tutorials:
Access-Google Earth
Age Calculation
Change To Proper Case
Email via Gmail #1
Send Gmail Email #2
Inactivity Logout Code
Outlook Email
Read Email Access
Email Attachment
Send Outlook Email
Running Sum
Denormalize Records
Stock Quotes
CreateQueryDef
Find Database Path
Detail-Master Update
Data Field Validation
Field Value New-Old
FindFirst
Access Version
Global Variable Parameter
Global Variables
Active Labels
Files List Box
Mail Merge
Quick Sort
Recordset Filters
Reference Form Field
Select Case
Access Transactions


Visual Basic Function Examples


Detail - Master Query in Visual Basic

Set status fields of master record based on characteristic of detail records

In this visual basic example let us assume we have two tables in a master/detail relationship. The main table contains Tasks which much be completed.  The detail table contains one or more Action Items for each task. The two tables are linked by Task_ID.  All Action Items must be completed before the Task is complete.  We want to mark the task as done when all the action items have been completed.





15 years ago we were putting Access database on the web using Active Server Page (ASP) programming language for the user interface. See examples of Access on the Internet.


We have a visual basic AfterUpdate trigger associated with the Completed field in the Action Items form.  Here is the code to update the master table (Tasks) when all items are completed for the task.

Private Sub Action_Complete_AfterUpdate()

    If Me.Action_Complete=true then

        DoCmd.RunSQL ("Update Tasks set Task_Complete=True " & _
        " Where Tasks.Task_ID in " & _
        " (Select Task_ID from Action_Items where Task_ID=" & Me.Task_ID & _
        " Having Max(Action_Items.Action_Completed)=-1 " & _
        " Group By Task_ID)")

    Else

        DoCmd.RunSql ("Update Tasks set Task_Complete=False " & _
        " where Task_ID=" & Me.Task_ID)

    End If

End Sub

Ok... this is more of an SQL example than a visual basic example.  But this is a perfect example of creating a very simple solution to a potentially very complicated task.   The key to creating highly maintainable Access databases is to have a consultant who knows both SQL programming and visual basic programming.

Here is the trick to this query... If all the Action_Completed Yes/No fields are true (-1) then the Max of them will be -1.  If any are not true (0) then the Max will be 0.

You should have indexes on all table fields mentioned in the SQL statement.










Blue Claw Database Design Downloadable Tutorial:
Multi Select List Box Query Parameters  








A Blue Claw Software Design Template:

Corporate Document Control Solution
 










A Blue Claw Database Design Article:

Why Choose Microsoft Access
 

Microsoft Office:
MS Access 2000 Through 2016 and Office 365 & Sharepoint


Contact Information

Copyright 2000-2017 Blue Claw Database Design

Microsoft Access 2007, 2010, 2013 & 2016