UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Updating Record Fields, Access 2016    
 
   
jmspisak777
post Jan 21 2019, 09:45 AM
Post#1



Posts: 8
Joined: 25-October 15



I have database with a table that is populated by importing updates weekly that are downloaded into excel workbook. This workbook has row of data for each discrepancy in an inspection, and each discrepancy for the same inspection shares a common Report Number (I use this field to link records on sub reports for the individual discrepancies) , other than a Discrepancy Field and Cause Code Field, the other info the record is mostly the same in each row.... such as supervisor name, date of inspection, work center name, etc...

I added two fields to the end of the table to which the workbook is imported to, Comments and Comments by fields, to store a supervisors response to the report and their name. I have an update form which groups the reports by report number (the Comments and Comments by fields are on main form), with a sub form for the discrepancies and cause codes. I want one response for each report and one respondent, not one response and respondent for each discrepancy, so I didn't put those fields on the sub form. My response form will only update one record with comments and respondent name for that report; so if it has 3 discrepancies in that report, only 1 row of the 3 records with the same report number is data added to other two are left blank.

I don't know how to update all 3 record lines at same time and can't seem to find a post or sample data base for this issue. Can anyone point me in the right direction?
Go to the top of the page
 
theDBguy
post Jan 21 2019, 11:29 AM
Post#2


Access Wiki and Forums Moderator
Posts: 75,595
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Welcome back! I'm not sure I am clear what the problem is? What exactly do you mean by this?

QUOTE
My response form will only update one record with comments and respondent name for that report;


Are you using code to make the update? Or, are you entering the information manually? If manually, are you saying you're able to enter data on the first record but the form won't allow you to enter data on the other records? If you're using code, can you show us the code?

QUOTE
I don't know how to update all 3 record lines at same time...


From what I understand, you added the new fields to the main form, so I am not sure what exactly you want to update in he subform.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
jmspisak777
post Jan 21 2019, 01:25 PM
Post#3



Posts: 8
Joined: 25-October 15



Hello,

Sorry to be so confusing. I'm entering records manually. The excel table I import data from is in such a format that each inspection is assigned one Record Number, each discrepancy doesn't have it's own ID number, so each row of table info is nearly all the same except for the few fields associated with the discrepancies.

So, created a form, with a sub form, that displays the inspection records, and uses a select query as a source to group the records by Record Number on the main form, so I don't get a separate records for each discrepancy. The sub form displays the each discrepancy for that inspection record, linked by Record Number. I currently have a the Comments block and Comments By block on the sub form, so a response is requested for each discrepancy. I just want one response for the one inspection report, but if move the Comments and Comments by block on the main form, it won't update each record associated with that Record Number, one record for each discrepancy. So I want to enter comments and a commentator name on the main form, and update those fields for all records in table with that same Record Number, whether there two discrepancies or four, etc...

Hope that makes sense. Thank you for your assistance!
Go to the top of the page
 
theDBguy
post Jan 21 2019, 02:24 PM
Post#4


Access Wiki and Forums Moderator
Posts: 75,595
Joined: 19-June 07
From: SunnySandyEggo


Hi,

If you want only one set of comments and name for each report, regardless of the number of discrepancies, then placing those fields in the reports table makes sense because it is not recommended to store duplicate information in multiple tables. So, if you already have the comments in the reports table, then there's really no need to store it again in the discrepancy table. But if you still want to do it, then you should be able to enter them manually, one at a time, in your subform. Otherwise, if you want to update all the discrepancies in one go, then you can use an UPDATE query to do it. However, I still recommend you don't store the comments in the discrepancies table because you can just use a regular query to display the comment and name from the reports table. If you post a copy of your db with test data, we can show you what we mean.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
jmspisak777
post Jan 21 2019, 07:31 PM
Post#5



Posts: 8
Joined: 25-October 15



Thanks again for help. Database and BE are attached.

I can keep the Comments and Comments by fields on the sub form, it's not that big of deal. I just thought it was redundant for the respondent. However, I run into the same problem when any field on the main form, which has multiple discrepancies, needs updated.... I have to manually update the field on each record for the inspection. I'd like to find a sample database to see how an update query would work for my situation.
This post has been edited by jmspisak777: Jan 21 2019, 07:35 PM
Attached File(s)
Attached File  LEAPBE.zip ( 86.87K )Number of downloads: 7
Attached File  LEAP_AID_Database_v6.zip ( 756.92K )Number of downloads: 7
 
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    24th June 2019 - 05:12 PM