Acess VBA - Update Variable Column (Too few parameters error)

Question

I am new to VBA in Access database, the following code tries to combine two columns in Table_2, but one of the column name needs to be defined by a field value from Table_1, I tried to run the code, but it returns "Error updating: Too few parameters. Expected 1." I am not sure where is the problem.

Appreciate if someone can help. Thanks a lot.

Function test()
On Error Resume Next
  Dim strSQL As String
  Dim  As String
  Dim  txtValue As String
  txtValue = Table_1![Field_A]

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

On Error GoTo Proc_Err
  ws.BeginTrans

  strSQL = "UPDATE Table_2 INNER JOIN Table_1 ON Table_2.id = Table_1.id SET Table_2.Field_Y = Table_2!txtValue & Table_2![Field_Z]"
  db.Execute strSQL, dbFailOnError

  ws.CommitTrans

Proc_Exit:
  Set ws = Nothing
  Set db = Nothing
  Exit Function

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

UPDATE: The following codes are with actual field names:

Function CombineVariableFields()
On Error Resume Next

  Dim ws As Workspace
  Dim strSQL As String
  Dim fieldname As String
  fieldname = Table_1![SelectCombineField]

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

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]"
  Debug.Print strSQL
  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

Below are screenshots of the two tables

Are you sure you are not trying to do something like this? "UPDATE Table_2 INNER JOIN Table_1 ON Table_2.id = Table_1.id SET Table_2.Field_Y = " & txtValue & "Table_2.Field_Z" — Davy C, Apr 10 '16 at 21:10
@davy-c, for example, in Table_2, the library wants to combine [publisher] & [book name] if the book type is college text book, and combine [authur] & [book name] if the book type is novel. Which field is used to combine with [book name] column depends on the user input value (txtValue) in Table_1 for each book type. — Will, Apr 11 '16 at 01:30
Please update your post with actual field names (even list table field names) as you just commented above rather than the generic Y, Z. Also, you cannot simply declare a VBA variable to a table field with `txtValue = Table_1![Field_A]` but must run a DLookUp. — Parfait, Apr 11 '16 at 02:58
If you are going to use `ws` and `db` as variables you must declare them. You also have a blank declaration in your code. — LiamH, Apr 11 '16 at 06:39
[How to debug dynamic SQL in VBA](http://stackoverflow.com/a/1099570/3820271) — Andre, Apr 11 '16 at 08:39
@Parfait I have updated the question with actual field name. And added screenshots of the two tables. — Will, Apr 11 '16 at 19:18

Adam Silenko · Answer 1 · 2016-04-11T23:06:25.693

1

try use DLookup Function and concatenate result with your Update string to get expected command.

edit:
you can also open recordset, build your update dynamically and execute it in loop. For example:

Set rst = db.OpenRecordset("Select distinct SelectCombinetField FROM Table_1", dbOpenDynaset)
with rst 
  do while not .eof
     strSQL = "UPDATE Table_2 SET Table_2.[" & !SelectCombinetField & "] = (select txtValue from Table_1 where SelectCombinetField = '" & SelectCombinetField & "' and id = Table_2.id Where somting....)"
     db.Execute strSQL, dbFailOnError
     .MoveNext
  Loop
end with

If is only example, because all your description is unclear for me.

edited Apr 11 '16 at 23:06

answered Apr 11 '16 at 22:37

Adam Silenko

3,025
1
14
30

Thanks @adam-silenko, I am able to fix the "Too few parameters error" by DLookup. It fixed the t fixed the too few parameters error, and I get a bit about your idea for loop, but I can't really work it out as i am new to VBA, and I raised the question here: http://stackoverflow.com/questions/36581104/access-vba-concatenate-dynamic-columns-and-execute-in-loop – Will Apr 12 '16 at 18:25

score 0 · Accepted Answer · edited May 23 '17 at 12:16

As advised by @adam-silenko, I used DLookup function and then "Too few parameters error" is fixed. Here is the codes I used. Tough it fixed the error, but it produces another issue, which I raised on another question here.

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

You can use `DLookup` but with where condition. You can use it directly in `Update` — Adam Silenko, Apr 15 '16 at 13:41

score 0 · Answer 3 · answered Apr 15 '16 at 14:11

example with DLookup:

strSQL = "UPDATE Table_2 " _
  & "SET Table_2.CombinedField = DLookup(" _
    & "DLookup(""[SelectCombineField]""" _
      & ", ""Table_1""" _
      & ", ""BookType = """""" & Table_2.BookType & """""""")" _
    & ", ""[Table_2]""" _
    & ", ""Table_2_ID = "" & Table_2.Table_2_ID)" _ 'you must replace Table_2_ID with proper value
    & " & "" - "" & [Table_2]![BookName]" 
db.Execute strSQL, dbFailOnError

But this is slower solution. Solution with Recordset is better.

Acess VBA - Update Variable Column (Too few parameters error)

3 Answers3