Access 2016 VBA unable to set button click on form to write multi-value fields to second table

Question

I am building an application to track time amongst project, employees, etc. The company I work for has significant security red tape, so I have to use MS Access 2016, I am barred from youtube to search solutions, and I cannot download any files from the internet. Luckily, I can still access StackOverflow.

I have an entry form ("Task_List_Entry_Form") that has a button. On click, the button sends values to a table ("tbl_Task_List"). In the form VBA I've included that the table ("tbl_Task_List") refreshes, and all the values go to another table that acts as a corrections log ("tbl_Task_List_Corrections"). But the multi-value fields (I know, they are the worst, if it wasn't something that was seemingly necessary, I wouldn't include them) can't go. So I included a 2nd query in the VBA to take the value of the multi-valued fields and input into the fields, but this has been unsuccessful so far.

The first query option looked like this:
strSQL2 = "INSERT INTO tbl_Task_List_Corrections " & Tags.Value & " FROM tbl_Task_List " & "WHERE tbl_Task_List.ID IN (SELECT MAX(tbl_Task_List.ID) FROM tbl_Task_List);" 'DoCmd.RunSQL strSQL2
Result: Error thrown "Run-Time error '3134': Syntax error in INSERT INTO statement", with the error on line "DoCmd.RunSQL strSQL2", The non-multi-value fields transferred correctly, but the value was not pulled, the tbl_Task_List_Corrections.Tags field remains NULL.

Then I tried something more like this:
CurrentDb.Execute "INSERT INTO tbl_Task_List_Corrections (Tags) VALUES ('" & Me.[Tags].Value & "');"
Result: No error was thrown, but the value was not pulled, and there is an extra row that was inserted into the "tbl_Task_List_Corrections" table

There is potentially a 3rd solution I could try, that I found at this source, however it is based on an example access database that due to security restrictions, I am unable to download and look at, and I could not figure it out from the code alone. Here is that source: https://www.utteraccess.com/forum/index.php?s=66e048c0880b46f8bd25a0541c30df63&showtopic=2018212&st=0&p=2463687&#entry2463687

This is not all the code, but the code that has to do with moving values after the form is saved.

Me.Requery                'Refresh so the table is complete

Dim strSQL1 As String     'Declaring the Non-Multivalued fields
Dim strSQL2 As String     'attempting the multi-value fields again

strSQL1 = "INSERT INTO tbl_Task_List_Corrections (Task_ID, Task_Title,  
Task_Start_Time, Task_End_Time, Break_Length_Minutes, Priority,  
Blockage_Reason, Requirements, Notes, Task_Complete) SELECT 
tbl_Task_List.ID, tbl_Task_List.Task_Title, tbl_Task_List.Task_Start_Time, 
tbl_Task_List.Task_End_Time, tbl_Task_List.Break_Length_Minutes, 
tbl_Task_List.Priority, tbl_Task_List.Blockage_Reason, 
tbl_Task_List.Requirements, tbl_Task_List.Notes, 
tbl_Task_List.Task_Complete FROM tbl_Task_List WHERE tbl_Task_List.ID IN 
(SELECT MAX(tbl_Task_List.ID) FROM tbl_Task_List);"
DoCmd.RunSQL strSQL1

'strSQL2 = "INSERT INTO tbl_Task_List_Corrections " & Tags.Value & " FROM 
tbl_Task_List " & "WHERE tbl_Task_List.ID IN (SELECT MAX(tbl_Task_List.ID) 
FROM tbl_Task_List);"
'DoCmd.RunSQL strSQL2          'THIS IS ATTEMPT 1

'CurrentDb.Execute "INSERT INTO tbl_Task_List_Corrections (Tags) VALUES ('" 
& Me.[Tags].Value & "');"      'THIS IS ATTEMPT 2


    Dim db As Database                               'Attempt4
    Dim rs As Recordset                              'Attempt4
    Dim childRS As Recordset                         'Attempt4
                                                'Attempt4
    Set db = CurrentDb()                             'Attempt4
                                                'Attempt4
    ' Open a Recordset for the Tasks table.                    'Attempt4
    Set rs = db.OpenRecordset("tbl_Task_List")                 'Attempt4
    rs.MoveFirst                                               'Attempt4
                                                               'Attempt4
    Do Until rs.EOF                                            'Attempt4
       ' Print the name of the task to the Immediate window.   'Attempt4
       Debug.Print rs!Task_Title.Value                         'Attempt4
                                                               'Attempt4
       ' Open a Recordset for the multivalued field.           'Attempt4
       Set childRS = rs!Tags.Value                             'Attempt4
                                                               'Attempt4
          ' Exit the loop if the multivalued field contains no records 'Attempt4
         Do Until childRS.EOF                                      'Attempt4
              childRS.MoveFirst                                     'Attempt4
                                                               'Attempt4
             ' Loop through the records in the child recordset.    'Attempt4
              Do Until childRS.EOF                                  'Attempt4
                  ' Print the owner(s) of the task to the Immediate 'Attempt4
                  ' window.                                         'Attempt4
                  Debug.Print Chr(0), childRS!Value.Value           'Attempt4
                  childRS.MoveNext                                  'Attempt4
              Loop                                                  'Attempt4
          Loop                                                      'Attempt4
      rs.MoveNext                                                  'Attempt4
    Loop                                                            'Attempt4

MsgBox "You have successfully added this Task"

DoCmd.Close

End Sub
 Below is attempt 5

 Dim db As Database                               'Attempt5
    Dim rs As Recordset                              'Attempt5
    Dim rs2 As Recordset                             'Defining the tbl_Task_List_Corrections
    Dim childRS As Recordset                         'Attempt5
                                                'Attempt5
    Set db = CurrentDb()                             'Attempt5
                                                'Attempt5
    ' Open a Recordset for the Tasks table.                              'Attempt5
    Set rs = db.OpenRecordset("tbl_Task_List")                           'Attempt5
    Set rs2 = db.OpenRecordset("tbl_Task_List_Corrections")              'Setting the value of tbl_Task_List_Corrections
    rs.MoveLast                                                         'Attempt5
                                                                    'Attempt5

'Do Until rs.EOF 'Attempt5

  ' Print the name of the task to the Immediate window.             'Attempt5
  Debug.Print rs!ID.Value                                           'Attempt5
  Debug.Print rs!Task_Title.Value                                   'Attempt5
  Debug.Print rs!Priority.Value                                     'Attempt5
  Debug.Print rs!Blockage_Reason.Value                              'Attempt5
  Debug.Print rs!Requirements.Value                                 'Attempt5
  Debug.Print rs!Notes.Value                                        'Attempt5

  ' Open a Recordset for the multivalued field.                     'Attempt5
  Set childRS1 = rs!Tags.Value                                      'Attempt5
  Set childRS2 = rs!Assigned_To.Value
                                                                    'Attempt5
     ' Exit the loop if the multivalued field contains no records.  'Attempt5
     Do Until childRS1.EOF                                          'Attempt5
         childRS1.MoveFirst                                         'Attempt5
                                                                    'Attempt5
         ' Loop through the records in the child recordset.         'Attempt5
         Do Until childRS1.EOF                                      'Attempt5
             ' Print the owner(s) of the task to the Immediate      'Attempt5
             ' window.                                              'Attempt5
             Debug.Print Chr(0), childRS1!Value.Value               'Attempt5

             childRS1.MoveNext                                       'Attempt5
         Loop                                                        'Attempt5
    Loop                                                            'End of loop that checks if Tags multi-value field is NULL

    ' Exit the loop if the multivalued field contains no records.  'Attempt5
     Do Until childRS2.EOF                                           'Attempt5
         childRS2.MoveFirst                                          'Attempt5

         ' Loop through the records in the child recordset.         'Attempt5
         Do Until childRS2.EOF                                       'Attempt5
             ' Print the owner(s) of the task to the Immediate      'Attempt5
             ' window.                                              'Attempt5
             Debug.Print Chr(0), childRS2!Value.Value                'Attempt5

             childRS2.MoveNext                                       'Attempt5
         Loop                                                       'Attempt5

     Loop                                                           'End of loop that checks if Assigned_To multi-value field is NULL

    rs2.AddNew                                                          'Attempt5
    rs2!Task_ID = rs!ID.Value                                           'Attempt5
    rs2!Task_Title = rs!Task_Title                                      'Attempt5
    rs2!Tags = childRS1!Value.Value                                     'Attempt5
    rs2!Priority = rs!Priority                                          'Attempt5
    rs2!Assigned_To = childRS2!Value.Value                              'Attempt5
    rs2!Blockage_Reason = rs!Blockage_Reason                            'Attempt5
    rs2!Requirements = rs!Requirements                                  'Attempt5
    rs2!Notes = rs!Notes                                                'Attempt5
    rs2.Update                                                          'Attempt5

Below is Attempt 7, referring to the Answer on 4/13 (edited again 4/23)

Me.Requery                                                          'Refresh the table before running the query

Dim strSQL1 As String                                               'Declaring the Non-Multivalued fields to move to Corrections Log

strSQL1 = "INSERT INTO tbl_Task_List_Corrections (Task_ID, Task_Title, " & _
        "Task_Start_Time, Task_End_Time, Break_Length_Minutes, Priority, " & _
        "Blockage_Reason, Requirements, Notes, Task_Complete) " & _
        "SELECT ID, Task_Title, Task_Start_Time, Task_End_Time, " & _
        "Break_Length_Minutes, Priority, Blockage_Reason, Requirements, " & _
        "Notes, Task_Complete FROM tbl_Task_List WHERE ID IN " & _
        "(SELECT MAX(ID) FROM tbl_Task_List);"
DoCmd.RunSQL strSQL1

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT ID FROM tbl_Task_List") 'error 3061
Set rs = CurrentDb.OpenRecordset("SELECT MAX(ID) FROM tbl_Task_List") '3265
    Do Until rs.EOF
    CurrentDb.Execute "INSERT INTO " & _ 
    " tbl_Task_List_Corrections(Tags.Value) SELECT Tags.Value FROM " & _  
    " (SELECT Tags.Value FROM tbl_Task_List WHERE ID = " & rs!ID & ") " & _ 
    " AS T1 WHERE tbl_Task_List_Corrections.Task_ID = " & rs!ID
    rs.MoveNext
Loop

MsgBox "You have successfully added this Task"

'DoCmd.SetWarnings False                                             'Turning the "You are about to Update 1 Row" warning back on

DoCmd.Close

End Sub

With CurrentDb.Execute don't have to bother with SetWarnings. Get the INSERT working with a query object before trying with VBA. https://support.office.com/en-us/article/using-lookup-and-multivalued-fields-in-queries-6f64f92d-659f-411c-9503-b6624e1e323a?ui=en-US&rs=en-US&ad=US#bm7 — June7, Apr 05 '19 at 21:18
The "SetWarnings" portion is in reference to the popup "You are about to Insert...". Which I don't want to have to deal with. I've tried a number of different approaches in the last week - but any SELECT or INSERT with a query object has yet to pull the value I'm looking for - it returns "1", sometimes "1" and "2" depending on how I structure it (if there are two values in the multi-value field; makes two columns with values "1" and "2"). Is there a way to access the string if I can isolate the position of that string in the multi-value field? I've edited my post with new code doing that. — CircularReference, Apr 11 '19 at 21:01
A multi-value field does not have CSV string data. It has links to records in hidden dependent table. Execute will not trigger warnings so SetWarnings is not needed. So use Execute instead of RunSQL. — June7, Apr 11 '19 at 21:28
If I comment out everything except the CurrentDB.Execute line - it doesn't give me an errors, but there is no value that is parsed in. It's NULL. Can you tell me where I'm going wrong on that line? — CircularReference, Apr 11 '19 at 21:42
I've tried a couple different attempts at using recordsets. Which seems to work OK, except that the value I'm getting in the Locals window is "1", when I expect it to be "TEST". When there are two values "TEST", and "TEST2", similarly it's '1' and '2'. So it seems to find the position value - but not the string The code that is doing this is now labelled as "Attempt4" in the code — CircularReference, Apr 11 '19 at 22:14
Is the field also using a lookup with alias? If so, the actual value saved is 1 or 2 (record ID from the source lookup table), not the alias text that is displayed. The actual saved value is what will be transferred into other record. — June7, Apr 11 '19 at 22:16
I believe I'm very close. I've added my latest code (under the previous to keep it separate and retain the edit history). It's labelled mostly as 'Attempt5. All of the recordset fields from the 'tbl_Task_List' are being added in a new record to table 'tbl_Task_List_Corrections', except for the multi-value fields. It is successful if I comment those lines out of the rs2.AddNew portion, but when not, it returns error "Run-time error 3021, no current record". Debug shows error at line "rs2!Tags = childRS1!Value.Value. Any advice on diagnosing why or working around is appreciated — CircularReference, Apr 12 '19 at 20:41
You need to select the rs ID from tbl_Task_List_Corrections. It would probably run faster if limited to only the new records just created in the first INSERT. The MVF is not really required in the rs recordset, really just need the ID. The MVF INSERT is wrong. The innermost SELECT does not select from rs, select from table. Look at my example again (also did an edit on the rs). Space and ; at end not needed — June7, Apr 15 '19 at 20:57
Actually, can pull the ID from either table but really should limit the recordset, especially if pulling from the original table. — June7, Apr 15 '19 at 21:18
Ok, I tried that earlier but got a different error - I've updated the code to reflect it. returns error: "Run-time error '3061': Too few parameters. Expected 1". I tried referencing it as a number by removing the " & ... & ", from the middle part of currentDb.Execute - that returns "Run-time error '3061': Too few parameters. Expected 2". So there's some reference in there I have wrong maybe? — CircularReference, Apr 15 '19 at 21:24
Not seeing any filter for the recordset. The MVF insert looks fine but might try an alias name for the inner query as shown in my example. — June7, Apr 16 '19 at 01:55
Sorry for the delay, my work added a fun new security protocol which disabled all macros for all office suite for 9 days. I've updated my 'attempt 7' to what the code is now. Includes an alias name. When it runs it returns run-time error 3061. I thought if I drilled down on setting rs to the specific ID it might help. That returns runtime error 3265 — CircularReference, Apr 23 '19 at 14:54
You should provide the exact error message, not just the number. The query using Max() does not have the field ID, it has a calculated field. Need an alias name for the calculated field then reference that field name in the subsequent MVF INSERT.`Set rs = CurrentDb.OpenRecordset("SELECT MAX(ID) AS NewID FROM tbl_Task_List")`. Or instead of opening recordset set a variable with DMax() domain aggregate. — June7, Apr 23 '19 at 17:25
The version 'Set rs = CurrentDb.OpenRecordset("SELECT ID FROM [tbl_Task_List]")' returns 'Run-time error "3061": Too few parameters. Expected 1.' The version 'Set rs = CurrentDb.OpenRecordset("SELECT MAX(ID) FROM [tbl_Task_List]")' returns 'Run-time error "3265": Item not found in this collection.' The latest suggestion 'Set rs = CurrentDb.OpenRecordset("SELECT DMax(ID) AS NewID FROM [tbl_Task_List]")' returns 'Run-time error "3920": unable to execute query. Invalid operation or syntax using multi-value field' — CircularReference, Apr 23 '19 at 18:26
And did you modify the MVF INSERT to use `NewID` field? All I can say is my code works perfectly for me. Maybe set focus to that latest record on form and reference the field/control on form instead of trying to pull via recordset. — June7, Apr 23 '19 at 18:39

June7 · Answer 1 · 2019-04-15T21:16:31.230

0

Turned out to be much simpler than I thought. Looks like original attempt was on right track.

Once new record with non-MVF data is created, run another INSERT SELECT action to populate the MVF. Example shows populating second table MVF in a loop of copied records. Tested and worked. Should be simple to execute the SQL action for a single record specified by record identifier on form instead of looping recordset.

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT RateID FROM CopyOfRates")
Do Until rs.EOF
    CurrentDb.Execute "INSERT INTO CopyOfRates(MVTest.Value) SELECT MVTest.Value FROM " & _
        "(SELECT MVTest.Value FROM Rates WHERE RateID = " & rs!RateID & ") AS Q1 " & _
        "WHERE CopyOfRates.RateID = " & rs!RateID
    rs.MoveNext
Loop

edited Apr 15 '19 at 21:16

answered Apr 12 '19 at 23:16

June7

19,874
8
24
34

It feels REALLY close. I am getting 'Compile error: Syntax error', which seems to suggest I have something named incorrectly. I've tried a bunch of different solutions but still returning the error. To clarify: the table I'm pulling it from "tbl_Task_List", the table I'm trying to write it to: "tbl_Task_List_Corrections". The MV field (in both tables" is named "Tags". The autonumber ID field in "tbl_Task_List" is named "ID", and non-auto-number field that it corresponds to is "Task_ID". Any updated syntax is greatly appreciated, but I'll keep trying! – CircularReference Apr 15 '19 at 19:52
Code provided works perfectly for me. You will have to provide your attempt for analysis. Edit question. – June7 Apr 15 '19 at 19:56
Added my edits, sorry I was unable to properly format it so you didn't have to scroll... – CircularReference Apr 15 '19 at 20:13
Use line continuation character and concatenation in your code to make it more readable without scrolling. – June7 Apr 15 '19 at 21:00
Dang, 2.5 years later and I am trying to replicate my own code and it won't work. – June7 Oct 10 '21 at 13:08

Access 2016 VBA unable to set button click on form to write multi-value fields to second table

1 Answers1