Insert SQL generated by Access Fails when i try to run it in VBA

[brucemc777]

I obtained the following SQL from an Append query in Access:

INSERT INTO tblOrderEquipment ( IDOrderEquipment, IDEquipment, IDOrders, UnitCount )
SELECT tblOrderEquipment.IDOrderEquipment, tblOrderEquipment.IDEquipment, tblOrderEquipment.IDOrders, tblOrderEquipment.UnitCount
FROM tblOrderEquipment
WHERE (((tblOrderEquipment.IDEquipment)=Forms!frmEquipOrder!frmOrderAddEquip.Form!cbxEquipSelect.Column(0)) AND ((tblOrderEquipment.IDOrders)="IDOrders") AND ((tblOrderEquipment.UnitCount)="tbCount"));

and i'm attempting to run it from a command button as follows:

Private Sub cbAdd_Click()
    Dim SQL As String

    SQL = "INSERT INTO tblOrderEquipment ( IDOrderEquipment, IDEquipment, IDOrders, UnitCount )" _
    & "SELECT tblOrderEquipment.IDOrderEquipment, tblOrderEquipment.IDEquipment," _
    & "tblOrderEquipment.IDOrders, tblOrderEquipment.UnitCount FROM tblOrderEquipment" _
    & "WHERE (((tblOrderEquipment.IDEquipment)=Forms!frmEquipOrder!frmOrderAddEquip.Form!cbxEquipSelect.Column(0))" _
    & "AND ((tblOrderEquipment.IDOrders)=""IDOrders"") AND ((tblOrderEquipment.UnitCount)=""tbCount""));"
    
    DoCmd.RunSQL SQL

End Sub

But i get an error:

I also noted when Forms!frmEquipOrder!frmOrderAddEquip.Form!cbxEquipSelect.Column(0) was run in the immediate window i got back the result i am looking for. Apparently there is more to converting an SQL statement from an Append Query than cut & paste-

By looking at the above can anyone tell me what needs to be modified and perhaps how?

Thank you very much for helping me learn and dealing with my confusion!

-Bruce

 

[GaP42]

Private Sub cbAdd_Click()
Dim strSQL As String

strSQL = "INSERT INTO tblOrderEquipment ( IDOrderEquipment, IDEquipment, IDOrders, UnitCount )" _ &
    "SELECT tblOrderEquipment.IDOrderEquipment, tblOrderEquipment.IDEquipment," _ &
        "tblOrderEquipment.IDOrders, tblOrderEquipment.UnitCount FROM tblOrderEquipment" _ &
    "WHERE (((tblOrderEquipment.IDEquipment)=Forms!frmEquipOrder!frmOrderAddEquip.Form!cbxEquipSelect.Column(0))" _ &
    "AND ((tblOrderEquipment.IDOrders)=""IDOrders"") AND ((tblOrderEquipment.UnitCount)=""tbCount""));"

Debug.print strSQL
DoCmd.RunSQL strSQL

Step 1: use debug.print in your code so you can see what the SQL return, comment out this statement after debugging.

 

[arnelgp]

use proper Concatenation on your SQL string:

Private Sub cbAdd_Click()
    Dim SQL As String

    SQL = "INSERT INTO tblOrderEquipment ( IDOrderEquipment, IDEquipment, IDOrders, UnitCount )" _
    & "SELECT tblOrderEquipment.IDOrderEquipment, tblOrderEquipment.IDEquipment," _
    & "tblOrderEquipment.IDOrders, tblOrderEquipment.UnitCount FROM tblOrderEquipment" _
    & "WHERE (((tblOrderEquipment.IDEquipment)=" & Forms!frmEquipOrder!frmOrderAddEquip.Form!cbxEquipSelect.Column(0))  _
    & " AND ((tblOrderEquipment.IDOrders)='IDOrders') AND ((tblOrderEquipment.UnitCount)='tbCount'));"
    
    DoCmd.RunSQL SQL

End Sub

 

[theDBguy]

Hi Bruce. One problem I see is you may be missing a space between the table name and the Where clause.

 

[The_Doc_Man]

Darn, too slow, because theDBguy beat me to that one.

 

[MarkK]

I would never rely on such a fragile chain of references as this: Forms!frmEquipOrder!frmOrderAddEquip.Form!cbxEquipSelect.Column(0). If a method consumes data from an object, put the method in that object.

 

[plog]

...ND ((tblOrderEquipment.IDOrders)="IDOrders") AND ((tblOrderEquipment.UnitCount)="tbCount"));

Did you actually run that query from a query object or just build it there? It just doesn't smell right.

1. A value in the field [IDOrders] can have the literal text value of 'IDOrders'? Or are you trying to reference a variable?

2. A field called [UnitCount] is not a numeric field but a text field where the literal text of 'tbCount' is a possible value? Again, are you trying to reference some variable or form field instead?

Also, the spacing issue you have with your FROM that others have pointed out isn't unique to the FROM. Youve made that same spacing error on every new line of your SQL that you put into VBA.

 

[isladogs]

This is what I get using my SQL <==> VBA converter (originally based on an old utility from Allen Browne):

SQL = "INSERT INTO tblOrderEquipment ( IDOrderEquipment, IDEquipment, IDOrders, UnitCount )" & _
" SELECT tblOrderEquipment.IDOrderEquipment, tblOrderEquipment.IDEquipment, tblOrderEquipment.IDOrders, tblOrderEquipment.UnitCount" & _
" FROM tblOrderEquipment" & _
" WHERE (((tblOrderEquipment.IDEquipment)=Forms!frmEquipOrder!frmOrderAddEquip.Form!cbxEquipSelect.Column(0))" & _
" AND ((tblOrderEquipment.IDOrders)=""IDOrders"") AND ((tblOrderEquipment.UnitCount)=""tbCount""));"

However, as already stated in posts #6 & #7, the where filter items all need attention

SQL to/from VBA Converter Add-In

This Access add-in is an updated version of my SQL to VBA And Back Again utility which makes it easy to convert query SQL to a VBA string (or vice versa). Additonal functionality includes creating example code from the VBA string and temp queries to test the SQL / VBA output.

www.isladogs.co.uk

I've just updated this utility to v3.27 today

 

[GPGeorge]

The AUG website includes a SQL Converter that can format your SQL for VBA. Give that a try as well.

 

[Gasman]

I obtained the following SQL from an Append query in Access:

INSERT INTO tblOrderEquipment ( IDOrderEquipment, IDEquipment, IDOrders, UnitCount )
SELECT tblOrderEquipment.IDOrderEquipment, tblOrderEquipment.IDEquipment, tblOrderEquipment.IDOrders, tblOrderEquipment.UnitCount
FROM tblOrderEquipment
WHERE (((tblOrderEquipment.IDEquipment)=Forms!frmEquipOrder!frmOrderAddEquip.Form!cbxEquipSelect.Column(0)) AND ((tblOrderEquipment.IDOrders)="IDOrders") AND ((tblOrderEquipment.UnitCount)="tbCount"));

and i'm attempting to run it from a command button as follows:

Private Sub cbAdd_Click()
    Dim SQL As String

    SQL = "INSERT INTO tblOrderEquipment ( IDOrderEquipment, IDEquipment, IDOrders, UnitCount )" _
    & "SELECT tblOrderEquipment.IDOrderEquipment, tblOrderEquipment.IDEquipment," _
    & "tblOrderEquipment.IDOrders, tblOrderEquipment.UnitCount FROM tblOrderEquipment" _
    & "WHERE (((tblOrderEquipment.IDEquipment)=Forms!frmEquipOrder!frmOrderAddEquip.Form!cbxEquipSelect.Column(0))" _
    & "AND ((tblOrderEquipment.IDOrders)=""IDOrders"") AND ((tblOrderEquipment.UnitCount)=""tbCount""));"
  
    DoCmd.RunSQL SQL

End Sub

But i get an error:

View attachment 114630

I also noted when Forms!frmEquipOrder!frmOrderAddEquip.Form!cbxEquipSelect.Column(0) was run in the immediate window i got back the result i am looking for. Apparently there is more to converting an SQL statement from an Append Query than cut & paste-

By looking at the above can anyone tell me what needs to be modified and perhaps how?

Thank you very much for helping me learn and dealing with my confusion!

-Bruce

Tip: Put a space in front of each new line after the " . Much easier to ensure a space is there, rather than scrolling all the way over to thr right all the time.
Test the Select code before adding the Insert code.
Also add a Debug.Print SQL before ever trying to use it. Then comment out/remove when it is correct.