?
Solved

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

Posted on 2014-01-08
4
Medium Priority
?
436 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 31

Assisted Solution

by:hnasr
hnasr earned 1000 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 39

Accepted Solution

by:
PatHartman earned 1000 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 31

Expert Comment

by:hnasr
ID: 39770645
Welcome!
0

Featured Post

Independent Software Vendors: 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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

650 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