Please find a Sample Database that I have attached for my problem.
I have a form called "frmdata" that has a "commoditynum" field and two subforms - "sfrmrsoavail" (representing all Available RSO records) and "sfrmrsoselected" (representing all Selected RSO records) for a particular commoditynumber on top of this form.
I have two tables - tblrsoavail (that stores all RSO Available records) and tblrsoselect (that stores all selected RSO records for a particular commodity number)
Recorsource of frmdata is tblselectrso
Recordsource of subform "sfrmrsoavail" is qryrsoavail and recordsource of subform "sfrmrsoselected"
Here are the Business Requirements:
1. For every commoditynumber on this form, I should be able to Add RSO Records from Left to Right. I should also be able to Remove RSO Records from the Selected records on the Right.
2. If I open the table "tblrsoavail" and delete any record from it (for instance "rso2" then when I open "frmdata" I should not see rso2 in the Available List. Also, all records in frmdata that had rso2 selected should be removed automatically.
3. If I Edit any of the records in "tblrsoavail" then "tblrsoselected" should automatically get changed with the edited values. For instance, If I change the value of "rso1" to "test" in "tblrsoavail" then when I open "frmdata", the subform "sfrmrsoavail" should reflect this change AND the subform "sfrmrsoselected" should also reflect this change for the records where "rso1" is selected.
My Problem: I was able to achieve a solution to this problem with my attached database. But I am sure many experts in this forum would say this database is not normalized or it has a BAD Design. I have created subqueries for each RSO Fields selected. So, I can only have fixed RSOs (i have designed this for only 5 RSOs). If the user adds more RSOs, then I need to create more RSO fields on tblselect and make subqueries for them again.
I want the number of RSOs added to the table "tblrsoavail" to be dynamic and my method will not work there.
Can I ask any experts here to take a look at my db and see if there is a better way to achieve a solution without doing it the way I have accomplished it? I hope I can find a better method than what I have done here. Many Thanks!
Note: The Arrow buttons on "frmdata" is not yet coded.
I have a form called "frmdata" that has a "commoditynum" field and two subforms - "sfrmrsoavail" (representing all Available RSO records) and "sfrmrsoselected" (representing all Selected RSO records) for a particular commoditynumber on top of this form.
I have two tables - tblrsoavail (that stores all RSO Available records) and tblrsoselect (that stores all selected RSO records for a particular commodity number)
Recorsource of frmdata is tblselectrso
Recordsource of subform "sfrmrsoavail" is qryrsoavail and recordsource of subform "sfrmrsoselected"
Here are the Business Requirements:
1. For every commoditynumber on this form, I should be able to Add RSO Records from Left to Right. I should also be able to Remove RSO Records from the Selected records on the Right.
2. If I open the table "tblrsoavail" and delete any record from it (for instance "rso2" then when I open "frmdata" I should not see rso2 in the Available List. Also, all records in frmdata that had rso2 selected should be removed automatically.
3. If I Edit any of the records in "tblrsoavail" then "tblrsoselected" should automatically get changed with the edited values. For instance, If I change the value of "rso1" to "test" in "tblrsoavail" then when I open "frmdata", the subform "sfrmrsoavail" should reflect this change AND the subform "sfrmrsoselected" should also reflect this change for the records where "rso1" is selected.
My Problem: I was able to achieve a solution to this problem with my attached database. But I am sure many experts in this forum would say this database is not normalized or it has a BAD Design. I have created subqueries for each RSO Fields selected. So, I can only have fixed RSOs (i have designed this for only 5 RSOs). If the user adds more RSOs, then I need to create more RSO fields on tblselect and make subqueries for them again.
I want the number of RSOs added to the table "tblrsoavail" to be dynamic and my method will not work there.
Can I ask any experts here to take a look at my db and see if there is a better way to achieve a solution without doing it the way I have accomplished it? I hope I can find a better method than what I have done here. Many Thanks!
Note: The Arrow buttons on "frmdata" is not yet coded.
Last edited:
