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
Solved

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

Posted on 2014-01-08
4
428 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 35

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
set focus to tabbed sub form 10 37
Problem with vba code 4 36
Signature required on a report 5 25
how to insert parameter value in table 2 16
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…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

790 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