I need to develop a VBA DAO procedure to take the "Many" side values for a table , create a text string of its values,, and edit it back into the record on the "One" side of the relationship. For instance:
Table B - "Many" Side
SetNumber = 36 SetValue = 125
SetNumber = 36 SetValue = 130
SetNumber = 36 SetValue = 135
Table A - "One" Side
SetNumber = 36 SetValueTotals = 125, 130, 135
I am able to perform this procedure if I want to create a new record in Table A, but I need to find the existing record that contains the join field's value (SetNumber) in table A , and edit it so I can place the concatenated string into the SetValueTotal field. Thanks.
i would recommend not to have an extra column in table a, but instead use a function to retrieve the comma-separated list from table B, given a parameter SetNumber.
So you can use this for displaying the calculated list.
If you want to have a input field, where you enter an new number, which you use for an INSERT- or DELETE-Statement for table B, you will need an Query which will delete all entries from B, given a SetNumber, and INSERT into tableB all entries from the new list, parsing all Numbers out of the new field´s value. But be aware, that you have to do additional checks for unchanged entries, if tableB contains more columns then just SetNumber and SetValue. Then you have to remember old list, deleting only the missing Numbers in new field, and inserting only new numbers in new field.
This can´t be done easily, but it is possible. If you can provide more information about what you want to archive, maybe there is an easier way.
best regards,
r1sc