Solved

Concatenate text string for "Many" side table values back into "One" side table

Posted on 2011-03-06
3
397 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:skennelly
3 Comments
 
LVL 6

Accepted Solution

by:
TinTombStone earned 500 total points
ID: 35046474
This should work

I have named the table OneSide and ManySide, you will need to change them in the code. (do a find & replace)

And the field name are SetNumber, SetValue and SetValueTotals, again change them if required

Also it is only working for one SetNumber, 36, which is set manualy at the top.  You may want to add an inputbox or connect it to a form to make it more flexible

Sub DoSetValuesTotals()

Dim db As DAO.Database, rst As DAO.Recordset
Dim strsql As String, strResult As String
Dim SetNumber As Long

    SetNumber = 36
   
    Set db = CurrentDb
    strsql = "SELECT ManySide.SetNumber, ManySide.SetValue FROM ManySide WHERE (((ManySide.SetNumber)=" & SetNumber & "));"
    Set rst = db.OpenRecordset(strsql)
    Do Until rst.EOF
        strResult = strResult & rst!SetValue & ", "
        rst.MoveNext
    Loop
    If Not strResult = "" Then strResult = Left(strResult, Len(strResult) - 2)
    Set rst = db.OpenRecordset("OneSide", dbOpenDynaset)
    rst.FindFirst "[SetNumber] = " & SetNumber
    If rst.NoMatch Then
        MsgBox "No match for set " & SetNumber & " found"
        Exit Sub
    End If
    rst.Edit
    rst!setvaluetotals = strResult
    rst.Update
   
    rst.Close
End Sub

0
 

Expert Comment

by:r1sc
ID: 35046478
Hi skennelly,
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
0
 

Author Closing Comment

by:skennelly
ID: 35046621
Thank you TinTombStone. I was able to work the code to successfully complete my task. I have the "Many" side table connected to a form, so I can handle that part of the solution to your code. Many thanks and appreciation!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now