Update field in subform from field in Main

[jpkeller55]

I know this is probably easy but my brain is fried and can't figure this out. I have a main form with a field called Pentry. The subform has a field called PentryResp. I would like to have the value from Pentry update the value of PentryResp on the subform after update on the main form. Thanks for any assistance.

[HiTechCoach]

Jim,

Sounds like you might be duplicating data if the records are related.

If the have a a one-to-one ore one-to-many relationship then why would you need to duplicate the data when you can just look it up?

You shou8dl avoid duplicating data in multiple places. This makes data entry a lot more work and prone to error. Which cause data integrity issues. Which leads to user thing that Access is "bad".

If you really need to duplicate the data then I have a few questions:

How is the sub form's record source related to the parent form's record source?

Are there multiple records in the sub form? Is yes, which record(s) will you want to update?

[jpkeller55]

Hi Coach,

Thanks for taking time to respond. I guess it does sound like I am duplicating data in a sense and I probably need to rethink my approach on what I am trying to do. Basically, I have a database that we are using to build questionnaires that are composed of questions and responses. I have a table that stores the text of the questions and the question attributes and another table that stores the responses related to the question by QuestID. Questions from this database are then entered into our company’s software that presents the questionnaire to the end user based on the attributes of the question that we defined as we were building the question. As questions from the database are loaded into our software, the developer checks a box on the database question form indicating that the question has been entered (Pentry). As these entries are done, we QA what shows up in the company software and often find the need to edit our questions and responses in our Access database. When we make an edit to the question or a response, the Pentry flag is automatically removed. We then have another query that is run that looks for questions with the Pentry flag unchecked. This is how our developers know that there is a question to edit. I have created a form that will show the question and the responses that have been modified (it only shows the responses that have been modified and not all the responses related to that question). I currently do not have a flag associated with the responses and so when a question that has had a previous edit shows, it is showing all the responses that may have had a previous edit done at an earlier time. My thought was that I could add a flag to the responses and copy the result from the question flag for each response that is showing up on our edit form. That way, if future edits were done, only the newly edited responses would show up and not all the ones that have had edits. I know this sounds pretty complex and I probably need to rethink how I am capturing the response data for my edit form as right now without a flag, I am looking at an AuditLog in the response to see if there is an entry in the field to determine when to show on the edit form. I am thinking I need to add the flag and that will be the indicator that an edit to a response has been done. Maybe an example would help.

Suppose we have the following question with responses that have been entered into our software already (with Pentry and PentryResp checkbox on each = to -1 indicating that the question has been updated to the software):

Q1: What color is your car? (Pentry checkbox = -1)
R1: Red (PentryResp checkbox = -1)
R2: Green (PentryResp checkbox = -1)
R3: Blue (PentryResp checkbox = -1)
R4: Black (PentryResp checkbox = -1)
R5: White (PentryResp checkbox = -1)

Now suppose we make an edit to the question and several of the responses. Because not all the responses need editing, I only want to display on the edit form those responses that have been edited (PentryResp = 0)

Q1: What color is your automobile? (Pentry checkbox = 0)
R2: Forest Green (PentryRespcheckbox = 0)
R4: Midnight Black (PentryResp checkbox = 0)

When we made the edits to the question and the response, I have code that will update the value of the Pentry and PentryResp on each record to 0. So when we run the edit query for the edit form only these 3 lines will display. On the edit form, there is the Pentry checkbox on the main form which holds the question which the developer checks as he has made the edits to the question and responses thus changing the value of Pentry to -1 on the main form. So I would like the PentryResp value for each of the responses on the subform to update to -1 as well when the question Pentry indicator has been checked on the main form so that instead of looking like this:

Q1: What color is your automobile? (Pentry checkbox = -1)
R2: Forest Green (PentryResp checkbox = 0)
R4: Midnight Black (PentryResp checkbox = 0)

It would look like this:

Q1: What color is your automobile? (Pentry checkbox = -1)
R2: Forest Green (PentryResp checkbox = -1)
R4: Midnight Black (PentryResp checkbox = -1)

I am open to other ways to do this but thought the easiest would be to just copy the value of the Pentry indicator from the question on the Pentry value for each of the responses on the subform.

Thanks again for considering my post.

[NTC]

in the AfterUpdate event of Pentry

you would put
Me.SubFormName.Form!PentryResp = me.Pentry

[jpkeller55]

I tried Me.SubFormName.Form!PentryResp = me.Pentry but get an error message
Compile error: Method or Data member not found

[jpkeller55]

I have been trying just about every permutation of syntax I can find and nothing works. I either get a message that says Compile error: Method or Data member not found or when I try this code [code]Forms("frmQuestionUpdates").Controls("frmResponseU pdatesSubform").Form.Controls("PentryResp").Value = Me.PEGAentryCompile error: Method or Data member not found or when I use this code

Forms("frmQuestionUpdates").Controls("frmResponseUpdatesSubform").Form.Controls("PentryResp").Value = Me.Pentry

I get a message Run-time error '2465' Microsoft Office Access can't find the field 'frmResponseUpdates Subform' referred to in your expression.

It this even possible to do? What am I doing wrong??

[NTC]

the syntax I provided is correct.
the error message seems to point to the issue: can't find the field 'frmResponseUpdates Subform'

when you type: me.subformname the autosense should auto complete/find that subform object name.... check the property of the perimeter of this object so that you are certain of its object name.

[jpkeller55]

OK, thanks NTC. I have built a small test DB which shows what happens with the code:

 Me.frmResponses.Form!Pentry = Me.Pentry

. If you open the form frmQuestBuild and try checking the Pentry checkbox on the main form, it only updates the first record on the subform. I need it to update all the records that are showing on the subform. How can I do that? Thanks!

[HiTechCoach]

I would run an update query if you need to update multiple records.

I have created an example that shows how to select all the record or un-select all.

See this example:

Batch Printing and Save As PDF (2007/2010)

Batch Printing and save as snapshot (2000 - 2007)
** Access 2010 has dropped the support for the snapshot format.

[NTC]

HTC's advice is exactly correct, as usual.

I did not look at the attachment.

In general one should question why one needs to do this in terms of the underlying table design. A batch update has its usefulness; but it is possible that the field needing update shouldn't be located in that table in the first place, and in turn should be in a separate table with a join. This would allow one to simply change the single field - as long as one can join to it then you don't need it repeating in other table's records.... But this design idea is general and may not address the specifics of your situation as I haven't looked at your db.