Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need an Access VBA code loop

Posted on 2016-09-22
7
Medium Priority
?
68 Views
Last Modified: 2016-09-22
Hello,

I have a table with multiple columns and each time the columns can change from say 8 - 10 fields. My ask is I concatenate all columns in one field called [Combine]. I have created update individual queries.
But what I'm trying to do is create vba code with some SQL loop update, that does the same thing as my queries, even when the fields changes from 8-10...
Please see attached Access DB; table [Members]; and the 7 queries I've created...
Thanks!
Update-db.accdb
0
Comment
Question by:tyruss8
[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
  • 3
7 Comments
 
LVL 22
ID: 41811203
instead of this structure for your table, it would be better to set up a related table with a foreign key field linked to the primary key of the main table.  Then each answer would be in a different record and you wouldn't have to worry about how many fields there are -- and then here is code to loop and combine you can call to concatenate values in related records
'~~~~~~~~~~~~~~~~ testLoopAndCombine
Sub testLoopAndCombine()
'test LoopAndCombine
   Dim sTablename As String _
      , sIDFieldname As String _
      , sTextFieldname As String _
      , nValueID As Long _
      , sFieldSortBy As String
   
   sTablename = "MyTablename"
   sIDFieldname = "MyNumericForeignKeyFieldname" 'if FK is not a n umber, you will need to add delimiters to LoopAndCombine where it is referenced
   sTextFieldname = "Description of Fieldname"
   nValueID = 138 'some number you know is in the table
   sFieldSortBy = "FieldnameToSortBy"
   
   MsgBox LoopAndCombine(sTablename, sIDFieldname, sTextFieldname, nValueID, , , , sFieldSortBy)

End Sub

'~~~~~~~~~~~~~~~~ LoopAndCombine
Function LoopAndCombine( _
   psTablename As String _
   , psIDFieldname As String _
   , psTextFieldname As String _
   , pnValueID As Long _
   , Optional psWhere As String = "" _
   , Optional psDeli As String = ", " _
   , Optional psNoValue As String = "" _
   , Optional psOrderBy As String = "" _
   ) As String
's4p
'loop through recordset and combine values to one string
   
   'NEEDS REFERENCE
   'a Microsoft DAO Library
   ' -- OR --
   ' Microsoft Office #.0 Access Database Engine Object Library
   
   'PARAMETERS
   'psTablename --> tablename to get list from
   'psIDFieldname --> fieldname to link on (ie: "BookID")
   'psTextFieldname --> fieldname to combine (ie: "PageNumber")
   'pnValueID --> actual value of ID for this iteration ( ie: [BookID])
   'psWhere, Optional  --> more criteria (ie: "Year(PubDate) = 2006")
   'psDeli, Optional  --> delimiter other than comma (ie: ";", Chr(13) & Chr(10))
   'psNoValue, Optional  --> value to use if no data (ie: "No Pages")
   'psOrderBy, Optional  --> fieldlist to Order By
   
   'Set up error handler
   On Error GoTo Proc_Err
      
   'dimension variables
   Dim rs As DAO.Recordset _
      , vAllValues As Variant _
      , sSQL As String
    
   vAllValues = Null
  
   sSQL = "SELECT [" & psTextFieldname & "] " _
       & " FROM [" & psTablename & "]" _
       & " WHERE [" & psIDFieldname _
       & "] = " & pnValueID _
       & IIf(Len(psWhere) > 0, " AND " & psWhere, "") _
       & IIf(Len(psOrderBy) > 0, " ORDER BY " & psOrderBy, "") _
       & ";"
       
   'open the recordset
   Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)
      
   'loop through the recordset until the end
   With rs
      Do While Not rs.EOF
         If Not IsNull(.Fields(psTextFieldname)) Then
   
            '~~~~~~~~~~~~~~~~~~~~~~~~~ CHOOSE ONE
   
            '---- if field value is numeric
            vAllValues = (vAllValues + psDeli) _
             & Trim(.Fields(psTextFieldname))
   
            '---- uncomment if you want quotes around data
            'vAllValues = (vAllValues + psDeli) _
             & " '" & Trim(.Fields(psTextFieldname)) & "'"
            '~~~~~~~~~~~~~~~~~~~~~~~~~
          End If
         .MoveNext
      Loop
   End With 'rs
      
   If Len(vAllValues) = 0 Then
      vAllValues = psNoValue
   End If
 
   
Proc_Exit:
   'close the recordset
   rs.Close
   'release the recordset variable
   Set rs = Nothing
    
   LoopAndCombine = Trim(Nz(vAllValues, ""))
   Exit Function
   
'if there is an error, the following code will execute
Proc_Err:
   MsgBox Err.Description, , _
     "ERROR " & Err.Number _
      & "   LoopAndCombine"
 
   Resume Proc_Exit
   Resume
End Function

Open in new window

0
 
LVL 11

Expert Comment

by:Craig Yellick
ID: 41811204
You can combine all fields in a single query. Having to deal with nulls and adding a delimiter makes the statement a bit ugly but it's a copy+paste job in the SQL text mode window of the query designer.

update Members set Combine = iif(S01 is null, "", S01 & "; ")  & iif(S02 is null, "", S02 & "; ")  ...
0
 
LVL 11

Expert Comment

by:Craig Yellick
ID: 41811224
If you want to do it in a code loop, it'd look like this.

  Dim rs As Recordset
  Set rs = CurrentDb.OpenRecordset("select * from Members")
  While Not rs.EOF
    Dim i As Integer
    Dim fld As String
    Dim s As String
    s = ""
    For i = 1 To 12
      fld = "S" & Right("0" & i, 2)
      If Not IsNull(rs(fld)) Then s = s & rs(fld) & "; "
    Next
    rs.Edit
    rs!Combine = s
    rs.Update
    rs.MoveNext
  Wend

Open in new window

0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:tyruss8
ID: 41811456
Hi Crystal, for the suggestion and code I will look into it, but I'm afraid I may not understand the full code as my experience in vba is intermediate at best.
thanks
0
 

Author Comment

by:tyruss8
ID: 41811460
Hi CraigYellick,

I've copied and pasted your suggested vba code and got an error "item not found in this collection"?

Thanks
0
 
LVL 11

Accepted Solution

by:
Craig Yellick earned 2000 total points
ID: 41811474
You probably don't have 12 columns of data.  I wrote the code anticipating that you'd have 10+ columns and would  have to adjust for the zero-padded numbers.

Change the line "For i = 1 To 12" to whatever upper limit is present in your table.
0
 

Author Comment

by:tyruss8
ID: 41811617
Thanks CraigYellick worked like charm appreciate it!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

719 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