Solved Copy Data code (1 Viewer)

[rondon]

I posted this in Forms but think I need a code solution- I have 3 tables all linked by a Report_ID. I would like to populate a field from the other form frmRecommendations if a value is entered into a field Agencies_ID that matches a recommendation number in frmRecommendations . I have attached a screen shot of the relationship and forms it is a lot clearer there. As usual any help appreciated. I also understand that I have most like approached this with the wrong structure but if code can solve the problem that would be great.

 

[Pat Hartman]

1. Tables store data not forms. Forms are the window into what's in the tables. They also provide the means to add/change/delete data as well as to view it.
2. Do NOT duplicate data in other tables. Agency does not belong in tbl_Reports_tabled. That creates a pathological connection. How can you have many agencies related to a report but store a single Agency in the report record.
3. If you haven't gotten very far in your development (and even if you think it is too much work), I recomend that you change your column and other object names to follow best practices naming standards. NO spaces, NO special characters. ONLY A-Z, a-z, 1-9, and the underscore "_".
4. Your code should be generating the RecNum. Do not leave this to the user. When a new record is added to tbl_Recommendations, use:

If IsNull(Me.RecNum) Then
    RecNum = Nz(DMax("RecNum", "tbl_Recommendation", "Report_ID = " & Me.Report_ID), 0) +1  In the form's BeforeUpdate event.
End If

5. Finally, we're getting to your question. If Agencies are conneted to Recommendations, then your relationship is incorrect. Remove Report_ID from tbl_Agencies_ID (a better name might be tbl_AgencyAssigned and change the PK name accordingly) and replace it with Rec_ID. Use a combo on the Recommendations subform to pick the agency to assign. If multiple agencies can be assigned to the same recommendation, then you have a many-many relationship and you need a fourth table (usually called a Junction table) between tbl_Recommendations and tbl_Agencies_ID. The junction table typically contains just two fields. The two FK's of the tables being related.

 

[rondon]

Thank you for the comprehensive response. I will take the time and address the points 1-4 and fix up all of the naming conventions and add the code. I will restructure my tables as per 5 and see if that helps me understand it more. I will get back to you once I have completed the above. Again thanks Ron

 

[Micron]

I like to think these links will set a newbie on the right path when naming things:
Naming conventions
- http://access.mvps.org/access/general/gen0012.htm
- https://www.access-programmers.co.uk/forums/showthread.php?t=225837

What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html

 

[rondon]

1. Tables store data not forms. Forms are the window into what's in the tables. They also provide the means to add/change/delete data as well as to view it.
2. Do NOT duplicate data in other tables. Agency does not belong in tbl_Reports_tabled. That creates a pathological connection. How can you have many agencies related to a report but store a single Agency in the report record.
3. If you haven't gotten very far in your development (and even if you think it is too much work), I recomend that you change your column and other object names to follow best practices naming standards. NO spaces, NO special characters. ONLY A-Z, a-z, 1-9, and the underscore "_".
4. Your code should be generating the RecNum. Do not leave this to the user. When a new record is added to tbl_Recommendations, use:

If IsNull(Me.RecNum) Then
    RecNum = Nz(DMax("RecNum", "tbl_Recommendation", "Report_ID = " & Me.Report_ID), 0) +1  In the form's BeforeUpdate event.
End If

5. Finally, we're getting to your question. If Agencies are conneted to Recommendations, then your relationship is incorrect. Remove Report_ID from tbl_Agencies_ID (a better name might be tbl_AgencyAssigned and change the PK name accordingly) and replace it with Rec_ID. Use a combo on the Recommendations subform to pick the agency to assign. If multiple agencies can be assigned to the same recommendation, then you have a many-many relationship and you need a fourth table (usually called a Junction table) between tbl_Recommendations and tbl_Agencies_ID. The junction table typically contains just two fields. The two FK's of the tables being related.

1. Tables store data not forms. Forms are the window into what's in the tables. They also provide the means to add/change/delete data as well as to view it.
2. Do NOT duplicate data in other tables. Agency does not belong in tbl_Reports_tabled. That creates a pathological connection. How can you have many agencies related to a report but store a single Agency in the report record.
3. If you haven't gotten very far in your development (and even if you think it is too much work), I recomend that you change your column and other object names to follow best practices naming standards. NO spaces, NO special characters. ONLY A-Z, a-z, 1-9, and the underscore "_".
4. Your code should be generating the RecNum. Do not leave this to the user. When a new record is added to tbl_Recommendations, use:

If IsNull(Me.RecNum) Then
    RecNum = Nz(DMax("RecNum", "tbl_Recommendation", "Report_ID = " & Me.Report_ID), 0) +1  In the form's BeforeUpdate event.
End If

5. Finally, we're getting to your question. If Agencies are conneted to Recommendations, then your relationship is incorrect. Remove Report_ID from tbl_Agencies_ID (a better name might be tbl_AgencyAssigned and change the PK name accordingly) and replace it with Rec_ID. Use a combo on the Recommendations subform to pick the agency to assign. If multiple agencies can be assigned to the same recommendation, then you have a many-many relationship and you need a fourth table (usually called a Junction table) between tbl_Recommendations and tbl_Agencies_ID. The junction table typically contains just two fields. The two FK's of the tables being related.

Thanks, yes I have created new tables including a junction table and it appears to be working. Although I have spent some time with the old DB, I am going to start from new using the new structure.

 

[rondon]

I like to think these links will set a newbie on the right path when naming things:
Naming conventions
- http://access.mvps.org/access/general/gen0012.htm
- https://www.access-programmers.co.uk/forums/showthread.php?t=225837

What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html

Thanks for this I am starting from new as I see my attention to detail needs to improve

 

[Micron]

Thanks for this I am starting from new

Then as long as you're starting over, I suggest you look at some other basics. If just one of these prevents you from falling into a common trap it will be time well spent (some of the articles seem sparse, but there are links within them that are worth following):

Normalization Parts I, II, III, IV, and V

What Is Normalization, Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

and/or

Database Normalization – Holowczak.com Tutorials

holowczak.com

Entity-Relationship Diagramming: Part I, II, III and IV

Entity-Relationship Diagramming: Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

How do I Create an Application in Microsoft Access?

How do I Create an Application in Microsoft Access?

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
About Multi Value Fields - http://www.mendipdatasystems.co.uk/multivalued-fields/4594468763

 

[rondon]

1. Tables store data not forms. Forms are the window into what's in the tables. They also provide the means to add/change/delete data as well as to view it.
2. Do NOT duplicate data in other tables. Agency does not belong in tbl_Reports_tabled. That creates a pathological connection. How can you have many agencies related to a report but store a single Agency in the report record.
3. If you haven't gotten very far in your development (and even if you think it is too much work), I recomend that you change your column and other object names to follow best practices naming standards. NO spaces, NO special characters. ONLY A-Z, a-z, 1-9, and the underscore "_".
4. Your code should be generating the RecNum. Do not leave this to the user. When a new record is added to tbl_Recommendations, use:

If IsNull(Me.RecNum) Then
    RecNum = Nz(DMax("RecNum", "tbl_Recommendation", "Report_ID = " & Me.Report_ID), 0) +1  In the form's BeforeUpdate event.
End If

5. Finally, we're getting to your question. If Agencies are conneted to Recommendations, then your relationship is incorrect. Remove Report_ID from tbl_Agencies_ID (a better name might be tbl_AgencyAssigned and change the PK name accordingly) and replace it with Rec_ID. Use a combo on the Recommendations subform to pick the agency to assign. If multiple agencies can be assigned to the same recommendation, then you have a many-many relationship and you need a fourth table (usually called a Junction table) between tbl_Recommendations and tbl_Agencies_ID. The junction table typically contains just two fields. The two FK's of the tables being related.

I have enter the code but the RecNum is set as a number field and has a zero when a new record is created is this preventing the code from working

Private Sub RecNum_BeforeUpdate(Cancel As Integer)
If IsNull(Me.RecNum) Then
RecNum = Nz(DMax("RecNum", "tblRecommendation", "ReportID = " & Me.ReportID), 0) + 1
End If
End Sub