?
Solved

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

Posted on 2014-01-08
4
Medium Priority
?
439 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
  • 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 40

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month16 days, 22 hours left to enroll

864 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