Solved

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

Posted on 2014-01-08
4
426 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 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 34

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

910 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now