2

I am currently working within Access 2013.

The problem that I have is that I am trying to duplicate a record from the main form to a subform with a button.

My tables consist of:

tbl40_1                      tbl40_1_changes

ID (PK)                      ID (PK)
System_Name                  40_1_ID (FK)
Configuration_Type           System_Name
Configuration_ID             Configuration_Type
Reference_Document           Configuration_ID 
Approval_Authority           Reference_Document
Approval_Mechanism           Approval_Authority
Item_Location                Approval_Mechanism 
Custodian                    Item_Location  
                             Custodian

The current code that I have for the button consist of:

Private Sub Duplicate_Click()

Dim strInsert
Dim db As DAO.Database

StrInsert = "INSERT INTO tbl40_1_changes_subform (ID, [System_Name], [Configuration_Type], [Configuration_ID], [Reference_Document], [Approval_Authority], [Approval_Mechanism], [Item_Location], [Custodian]" & _
            "SELECT ID, [System_Name], [Configuration_Type], [Configuration_ID], [Reference_Document], [Approval_Authority], [Approval_Mechanism], [Item_Location], [Custodian]" & _
            "FROM frm40_1" & _
            "WHERE [40_1_ID] = " & Me.tbl40_1_changes_subform.Form.txt40_1_ID

Set db = CurrentDb()

db.Execute strInsert, dbFailOnError
Set db = Nothing


End Sub

When I click on the button I am getting a Run-time error for [StrInsert = "INSERT INTO", "Select", "From", "Where"]. I am not for sure what is wrong within this code as it is highlighting this entire piece of code.

Orange56
  • 119
  • 7
  • you forgot to close the first line with `)` – Hynek Bernard Jul 15 '16 at 14:03
  • Include `Debug.Print StrInsert`, run the code, and then inspect the `INSERT` statement in the Immediate window. (Ctrl+g will take you there.) You need to match this pattern: `INSERT INTO TableName () ...` You must include both parentheses around `` --- looks like yours is missing the second. – HansUp Jul 15 '16 at 14:05

1 Answers1

1

You are missing spaces at your line breaks.

Add a Debug.Print StrInsert to see it.

And, as Hynek Bernard wrote, the INSERT clause is missing the closing )

Try

StrInsert = "INSERT INTO tbl40_1_changes (ID, [System_Name], [Configuration_Type], [Configuration_ID], [Reference_Document], [Approval_Authority], [Approval_Mechanism], [Item_Location], [Custodian])" & _
            " SELECT ID, [System_Name], [Configuration_Type], [Configuration_ID], [Reference_Document], [Approval_Authority], [Approval_Mechanism], [Item_Location], [Custodian]" & _
            " FROM frm40_1" & _
            " WHERE [40_1_ID] = " & Me.tbl40_1_changes_subform.Form.txt40_1_ID

Debug.Print StrInsert   ' Ctrl+G shows Debug output

Edit

I think your WHERE clause is very off. You are copying from frm40_1 (on the main form) to tbl40_1_changes on the subform, right?

And if tbl40_1_changes.ID is an Autonumber, you can't set it.

Then you'll need something like

StrInsert = "INSERT INTO tbl40_1_changes (40_1_ID, [System_Name], [Configuration_Type], [Configuration_ID], [Reference_Document], [Approval_Authority], [Approval_Mechanism], [Item_Location], [Custodian])" & _
            " SELECT ID, [System_Name], [Configuration_Type], [Configuration_ID], [Reference_Document], [Approval_Authority], [Approval_Mechanism], [Item_Location], [Custodian]" & _
            " FROM tbl40_1" & _
            " WHERE ID = " & Me.ID
Andre
  • 26,751
  • 7
  • 36
  • 80
  • Hello Andre, thanks for the response. I copied your code and I still getting the same error code (Run-time error `2465`: Application - defined or object - defined error). – Orange56 Jul 15 '16 at 14:15
  • Please double-check that error, @Orange56. The message for Access error #2465 starts with *"Microsoft Access can't find the field ..."* – HansUp Jul 15 '16 at 14:17
  • @Orange56: edited target table name. If it still fails, please post the full Debug output. And read [How to debug dynamic SQL in VBA](http://stackoverflow.com/questions/418960/managing-and-debugging-sql-queries-in-ms-access/1099570#1099570) – Andre Jul 15 '16 at 14:24
  • @HansUp I have check the Access error #2465 and it is displaying ("application-defined or object-defined error), not for sure why. When I search the code in google it is displaying "Microsoft Access can't find the field". – Orange56 Jul 15 '16 at 14:24
  • @Orange56: Also, if `40_1_ID` is a foreign key, you will have to insert it too. I strongly suggest building your query in the query designer, and only when it is finished and working, copy the SQL back to VBA. – Andre Jul 15 '16 at 14:26
  • I don't understand why that happens, @Orange56, but do you still get an error after changing `tbl40_1_changes_subform` to `tbl40_1_changes`? – HansUp Jul 15 '16 at 14:27
  • @HansUp it is still giving me the error message for some reason. – Orange56 Jul 15 '16 at 14:48
  • @Orange56: see edit, I think there was much more wrong with your initial query than I thought at first. – Andre Jul 15 '16 at 14:56
  • @Orange56 I think your best strategy is to follow Andre's earlier advice. Build a valid `INSERT` in the query designer. I would do it as a parameter query, but that's not required. Anyway, once you have it working, adjust your VBA code to produce the same `INSERT` ... and verify it by checking the output from `Debug.Print StrInsert` – HansUp Jul 15 '16 at 15:11
  • @Andre, yes I am copying from frm40_1 to the subform tbl40_1_changes. The data type for (40_1_ID) is a number, data type for tbl40_1_changes (ID) is an autonumber, and the data type for tbl40_1 (ID) is a autonumber. – Orange56 Jul 15 '16 at 15:12
  • @Orange56: Then the last edit should do it, assuming `tbl40_1.ID` is represented by a field `ID` on the main form. – Andre Jul 15 '16 at 16:06
  • @Andre thank you that code is working great.. Now I am getting an error code (Run-Time error #3131 "Syntax error in FROM clause"). When I debug the code it is showing db.Execute strInsert, dbFailOnError is wrong. – Orange56 Jul 15 '16 at 16:32
  • Copy&Paste the Debug.Print of `StrInsert` into the query designer (SQL view). Switch to Design view. This will get more detailed error information. – Andre Jul 15 '16 at 16:36