Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.
Course Of The Month
10 days, 3 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Concatenate text string for "Many" side table values back into "One" side table
Posted on 2011-03-06
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.
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.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
3 Comments
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"
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
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"
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
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
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
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications. You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month10 days, 3 hours left to enroll
624 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.