Copy data from certain fields in one record to all other records in a set of records (1 Viewer)

[ddorfer85]

Hello,

I'm fairly new to Access and programming and this is my first post. I hope I'm in the right category.

I have a form "procedures" with a subform "study programmes". Each "procedure" can include multiple "study programmes" (there is a one-to-many relationship between the tables, this works fine)

Periodically, certain fields (lets say fields X, Y and Z) in each "study programme" record need to be updated. Instead of having to manually update fields X, Y and Z for each study programme in the procedure, I want to just update one study programme and then have a button in the subform that will allow me to copy the contents of fields X, Y and Z to all other study programmes in the procedure.

Based on what I've read it seems like the best solution might be to create a DAO recordset consisting of only the study programmes in this procedure, however, I have never done this before and am not sure where to start. The other option I was thinking about was to move to the next record and copy the data while the procedure ID is the same:

Private Sub bttn_daten_uebernehmen_Click()
Dim verfid As Integer
Dim data As String

procid = Me.id_procedure
data = Me.report_date_ar

While Me.id_procedure = procid
DoCmd.GoToRecord , , acNext
Me.report_date_ar = data
Wend
End Sub

I placed the button for this in the subform. I succeeded in copying the data to the other study programmes in the procedure, however, the code started creating an endless number of new study programme records within the procedure, so this clearly is not the solution. Also, I need to copy the values of 10-25 fields and it seems like creating a variable for each is not the most efficient method. I feel like there must be a much better approach, would greatly appreciate any help!

 

[The_Doc_Man]

OK, let's get to basic here. With Access, if you have to copy/update so many things at once, the immediate question is whether your tables are built properly. This is your first post and you admit to being new to Access. Therefore, I want to give you advice to take a break and do some reading.

You can search this forum for articles on "Normalization" or you can search the web for articles on "Database Normalization." You have to qualify the topic for the general web because there are at least three or four other major uses of the word "Normalization" that don't apply here. If you search the web, start with articles from .EDU domains (usually colleges and universities). When you feel more comfortable with the topic, you could also look at .COM articles. The only reason I suggest .EDU domains first is because the .COM domains, more often than not, want to sell you something or what they are describing is proprietary and not necessary of immediately general applicability.

You need to understand normalization in order to avoid the mistake of designing things that need such heavy-duty updating.

Having answered the question as I did, I will address the other side of the issue - what you actually asked about. The best way to do what you want is PROBABLY going to be based on an UPDATE query with a good WHERE clause to provide selectivity. Recordsets make sense but what you are describing is something that probably can be handled by a judiciously filtered UPDATE.

 

[ddorfer85]

Hi The_Doc_Man,

thanks for the information. Ok, let me rephrase - I have been working with Access for about 9 months now, so I have a bit of experience but in comparison to many of the other people here I'm probably a newbie ). I am familiar with the concept of normalization. Maybe it helps if I am more specific about what I need to update.

For each study programme there are certification dates (date_certification_beginning; date_certification_end), unresolved issues (unresolved_issues), deadlines (deadline_resolve_issues), publish online option (publish_online), and 3 or 4 other fields, all of which CAN vary for each study programme in a procedure, but in 70-80% of the cases these fields are the same. In any case, it must be possible to edit this information for each study programme individually.

In most procedures there are 2-5 study programmes, but there are dozens or hundreds of procedures with study programmes requiring an update. In addition, the data is updated by 7-8 different people using the database, not just myself. I do not have all the information necessary to do these updates myself. Thus, update queries thus do not seem like a good option.

So, the button is meant to be of help in the 80% of cases where the information in the mentioned fields is identical for all the study programmes in the procedure. I hope this provides a clearer picture.