Solved

How to best handle updating field in another table when check box is true

Posted on 2014-01-08
4
429 Views
Last Modified: 2014-01-10
I have a database where each submission will have multiple associated samples.  There is a check box to mark the submission closed when work is completed on it.  I also need to update the "completed" field for each sample associated with the submission.  I would like to do this automatically when the check box is "true" and undo it if the user makes an error and later unchecks the box.  Submissions and samples are two different tables related by Submission ID.  I have an update query to update in bulk all the samples that have been missed so far but I'm not sure that is the way to handle the day to day updates.  I'm also not sure how to run the update query in a silent mode that the user doesn't see.  Maybe VBA, macro or something else would  be better?  Any suggestions on the best way to handle it?
Thanks!
0
Comment
Question by:esbyrt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 250 total points
ID: 39767108
Here is a way:

Table a (aid string - key, f1 integer)
Code in Check43 After Update event.
If true update f1 to a value, otherwise update to another.


Private Sub Check43_AfterUpdate()
    DoCmd.SetWarnings False
    DoCmd.RunCommand acCmdSaveRecord
    If Check43 Then
        DoCmd.RunSQL "update a set f1=5 where aid='" & Me.aID & "'"
    Else
        DoCmd.RunSQL "update a set f1=6 where aid='" & Me.aID & "'"
    End If
    DoCmd.SetWarnings True
End Sub

Open in new window

0
 
LVL 36

Accepted Solution

by:
PatHartman earned 250 total points
ID: 39767139
Some unsolicited design advice.   If you have to run a bulk update to set and unset a bunch of values, the field is in the wrong table.  You need the checkbox to be in a higher level table so you only check one box on a form.
0
 

Author Closing Comment

by:esbyrt
ID: 39770088
Thank you hnasr for that bit of code, I'm sure I will use it somewhere.  However,  PatHartman gave me an AHA! moment with his comment.  I don't need to have a "completed" field for the samples when I can just run a query on the samples and filter the results by completed or not completed submissions.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39770645
Welcome!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How make one field or another required on a form 6 38
Please Explain What Reading a Zip file Back In Means 11 69
MS Access Query Question 2 36
Find unused columns in a table 12 77
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question