By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 2,318 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

Update Concatenation query using VBA

P: 19
The following code is intended to update a comma delimited list of all matches in [Data - Inventory].Description using a keyword list from [Component Library].

The query defined as strSQL works perfectly as a standalone list. When implemented into this sub, the code returns error 3061 (too few arguments) on "CallCurrentdb.Execute"

A previous implementation of this code gave me this error, but was resolved by treating a mishandling of quotations and spacing.I fear I am missing a syntax error, or this subquery using the Concat function simply can not be used in this fashion.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdPartTypes_DblClick(Cancel As Integer)
  2.  
  3.     Dim db As DAO.Database
  4.     Dim rst As DAO.Recordset
  5.     Dim strSQL As String
  6.  
  7.     strSQL = _
  8. "SELECT x.CompID, Max(Concat(Nz(x.CompID), Nz(x.PartType))) AS Types " & _
  9. "FROM (SELECT [Data - Inventory].ID AS [CompID], [Component Library].Type AS [PartType] " & _
  10. "FROM [Component Library] INNER JOIN [Data - Inventory] ON [Data - Inventory].Description LIKE '*' &  [Component Library].Keyword & '*'" & _
  11. ") AS x GROUP BY x.CompID;"
  12.  
  13.     Set db = CurrentDb()
  14.     Set rst = db.OpenRecordset(strSQL)
  15.     With rst
  16.         If Not (.BOF And .EOF) Then
  17.             Call .MoveFirst
  18.             Do While Not .EOF
  19.                 strSQL = _
  20.                     "UPDATE [Data - Inventory] " & _
  21.                     "SET [Type] = " & !Types & _
  22.                     " WHERE [Data - Inventory].ID = " & !CompID
  23.                     Call CurrentDb.Execute(Query:=strSQL)
  24.                 Call .MoveNext
  25.             Loop
  26.         End If
  27.         Call .Close
  28.     End With
  29.     Set rst = Nothing
  30.     Set db = Nothing
  31.  
  32. End Sub
  33.  

Here is the Concat function, found in an older post on this site.
Expand|Select|Wrap|Line Numbers
  1. 'Concat Returns lists of items which are within a grouped field
  2. Public Function Concat(strGroup As String, _
  3.                        strItem As String) As String
  4.     Static strLastGroup As String
  5.     Static strItems As String
  6.  
  7.     If strGroup = strLastGroup Then
  8.         strItems = strItems & ", " & strItem
  9.     Else
  10.         strLastGroup = strGroup
  11.         strItems = strItem
  12.     End If
  13.     Concat = strItems
  14. End Function
  15.  
For clarity, the end goal is to find and concatenate the Part Type associated with each keyword match and update the [Data - Inventory].Type with the results.

See this older post for sample tables
Any suggestions are greatly appreciated.
Jan 21 '19 #1
Share this Question
Share on Google+
1 Reply


ADezii
Expert 5K+
P: 8,597
If you can Upload a functional subset of the Database, stripped of any sensitive data, I would be happy to have a look at it.
Jan 23 '19 #2

Post your reply

Sign in to post your reply or Sign up for a free account.