Access VBA: Concatenate dynamic columns and execute in loop

Question

My question is related to the following two Access tables, named Table_1 and Table_2.

The following codes aims to update [Table_2.CombinedField] column, by concatenating two other columns of the same table. One of the two columns must be [Table_2.BookName], the other column is defined in Table_1.
For example, as you can see in Table_1, novel BookType should use Author to concatenate with BookName, research BookType should use PublishYear etc. That means which column to be used for concatenate in Table_2 is based on Table_1. Ideally, the target result for following codes should be:

CombinedField
tom - titleA
john - titleB
2010 - titleC
2011 - titleD
company5 - titleE

However, as you see Table_2.CombinedField in above Table_2 screenshot. The code only used the first row of Table_1 (Author) and applies to all rows of Table_2.

Function CombineVariableFields_NoLoop()
On Error Resume Next

  Dim ws As Workspace
  Dim strSQL As String
  Dim fieldname As String

  fieldname = DLookup("[SelectCombineField]", "Table_1") 

  Set ws = DBEngine.Workspaces(0)
  Set db = CurrentDb()

On Error GoTo Proc_Err
  ws.BeginTrans

  strSQL = "UPDATE Table_2 INNER JOIN Table_1 ON Table_2.BookType = Table_1.BookType SET Table_2.CombinedField = [Table_2]![" & fieldname & "] & ' - ' & [Table_2]![BookName]" 
  db.Execute strSQL, dbFailOnError

  ws.CommitTrans

Proc_Exit:
  Set ws = Nothing
  Exit Function

Proc_Err:
  ws.Rollback
  MsgBox "Error updating: " & Err.Description
  Resume Proc_Exit

End Function

My question
I guess I should use something like loop. However, I don't really know how should I apply loop to the codes in this scenario. (sorry i am new to VBA). Below coding is something by my guess only, appreciate if someone can help to point out what exact codes should be in order to generate my target result for Table_2.CombinedField. Thanks a lot.

The following codes is only my guess

Function CombineVariableFields_Loop()
On Error Resume Next

  Dim ws As Workspace
  Dim strSQL As String
  Dim fieldname As String
  Set ws = DBEngine.Workspaces(0)
  Set db = CurrentDb()

On Error GoTo Proc_Err
  ws.BeginTrans

    Set rst = db.OpenRecordset("Select distinct SelectCombineField FROM Table_1", dbOpenDynaset)
    With rst
    Do While Not .EOF
       fieldname = DLookup("[SelectCombineField]", "Table_1", "BookType = " & DLookup("BookType", "Table_2"))
       strSQL = "UPDATE Table_2 INNER JOIN Table_1 ON Table_2.BookType = Table_1.BookType SET Table_2.CombinedField = [Table_2]![" & fieldname & "] & ' - ' & [Table_2]![BookName]"
       db.Execute strSQL, dbFailOnError
       .MoveNext
    Loop
    End With

  ws.CommitTrans

Proc_Exit:
  Set ws = Nothing
  Exit Function

Proc_Err:
  ws.Rollback
  MsgBox "Error updating: " & Err.Description
  Resume Proc_Exit

End Function

score 1 · Accepted Answer · answered Apr 12 '16 at 20:12

try this:

Set rst = db.OpenRecordset("Select SelectCombineField, BookType FROM Table_1 Where BookType In(Select Distinct BookType From Table_2)", dbOpenDynaset)
With rst
Do While Not .EOF
   strSQL = "UPDATE Table_2 SET Table_2.CombinedField =  [Table_2].[" & !SelectCombineField & "] & ' - ' & [Table_2].[BookName] Where BookType = '" & !BookType & "'"
   db.Execute strSQL, dbFailOnError
   .MoveNext
Loop
End With

score 0 · Answer 2 · answered Apr 12 '16 at 19:00

0

This may not be the BEST way, but it should work:

 "UPDATE Table_2 a INNER JOIN Table_1 b ON a.BookType = b.BookType" _
      & "SET Table_2.CombinedField = " _
           & "Iif(b.[SelectCombinedField = 'Author', a.[Author], " _
               & " Iif(b.[SelectCombinedField = 'PublishYear', a.[PublishYear], " _
                    & "a.[Publisher])) & ' - ' & a.[BookName]"

answered Apr 12 '16 at 19:00

OpiesDad

3,385
2
16
31

for this example better than `Iif` will be `Switch`, but both need use value from table_1 in conditions..., code will do several simple update's and there is no need write names of field. – Adam Silenko Apr 15 '16 at 14:26

Access VBA: Concatenate dynamic columns and execute in loop

2 Answers2

Linked