Insert SQL Problems

[Gary_Mc_Mahon]

Hi,

Can anyone help with this one. This is attached to a button on a form if that helps at all.

When I get the line about opening the query, I get a run time error.

Private Sub Command1_Click()

Dim qdf As DAO.QueryDef
Dim db As DAO.Database
Dim strSQL As String
Dim CP As String
Dim From As String

Set db = CurrentDb

CP = InputBox("Please Enter the charge period to update, YYYYMM")
From = "From qry_UNION_" + CP



strSQL = "INSERT INTO tbl_RR19_Charge_Bucket_200211_2 ( [Billing Sys Code], [Invoice Number], [Opco Code], [Product Group 1 Desc], [Product Group 2 Desc], [Product Group 3 Desc], [Product Group 4 Desc], [Product Group 1 ID], [Product Group 4 ID], [Charge Period], [Reporting Period], [Invoice Date], [SAP Account ID], [Customer Name], [Segment ID], [Sub Segment ID], [Total Rev Post Disc & Adj (USD)] )" & _
"SELECT [qry_UNION_" + CP + "].[Billing Sys Code], [qry_UNION_" + CP + "].[Invoice Number], [qry_UNION_" + CP + "].[Opco Code], [qry_UNION_" + CP + "].[Product Group 1 Desc], [qry_UNION_" + CP + "].[Product Group 2 Desc], [qry_UNION_" + CP + "].[Product Group 3 Desc], [qry_UNION_" + CP + "].[Product Group 4 Desc], [qry_UNION_" + CP + "].[Product Group 1 ID], [qry_UNION_" + CP + "].[Product Group 4 ID], [qry_UNION_" + CP + "].[Charge Period], [qry_UNION_" + CP + "].[Reporting Period], [qry_UNION_" + CP + "].[Invoice Date], [qry_UNION_" + CP + "].[SAP Account ID], [qry_UNION_" + CP + "].[Customer Name], [qry_UNION_" + CP + "].[Segment ID], [qry_UNION_" + CP + "].[Sub Segment ID], [qry_UNION_" + CP + "].[Total Rev Post Disc & Adj (USD)]" & _
From + ";"




Set qdf = db.CreateQueryDef("qry_ZZTESTSQL1", strSQL)
DoCmd.OpenQuery "qry_ZZTESTSQL1"


db.QueryDefs.Delete ("qry_ZZTESTSQL1")

Set qdf = Nothing
Set db = Nothing
End Sub

Cheers!!
Gary

 

[mdmilner]

Assuming the SQL is correct, for an action query.
Remove all references to querydefs and add this line after you assign the value to strSQL. Other thing you might want to do is throw in some error handling as part of the inputbox where you're building the parameter.

DoCmd.RunSQL strSQL

This is how you execute action queries.

Mike

 

[Gary_Mc_Mahon]

Hi Mike, I've tried that and I still get an invalid arguement error. Any other ideas why this doesnt work?

 

[mdmilner]

I see missing spaces in the SQL syntax.
Following the INSERT INTO tbl (field, field2, field3) there is a space missing
Same with before the FROM statement.

My suggestion, or at least, what I do is, I step thru the VBA module and stop right after the strSQL value is set. I then either use...

MsgBox strSQL

Within the code or go to the Immediate window and type...

?strSQL

And look at it - looking for problems.

strSQL = "INSERT INTO tbl_RR19_Charge_Bucket_200211_2 ( [Billing Sys Code], [Invoice Number], [Opco Code], [Product Group 1 Desc], [Product Group 2 Desc], [Product Group 3 Desc], [Product Group 4 Desc], [Product Group 1 ID], [Product Group 4 ID], [Charge Period], [Reporting Period], [Invoice Date], [SAP Account ID], [Customer Name], [Segment ID], [Sub Segment ID], [Total Rev Post Disc & Adj (USD)] )" & _ 
"SELECT [qry_UNION_" + CP + "].[Billing Sys Code], [qry_UNION_" + CP + "].[Invoice Number], [qry_UNION_" + CP + "].[Opco Code], [qry_UNION_" + CP + "].[Product Group 1 Desc], [qry_UNION_" + CP + "].[Product Group 2 Desc], [qry_UNION_" + CP + "].[Product Group 3 Desc], [qry_UNION_" + CP + "].[Product Group 4 Desc], [qry_UNION_" + CP + "].[Product Group 1 ID], [qry_UNION_" + CP + "].[Product Group 4 ID], [qry_UNION_" + CP + "].[Charge Period], [qry_UNION_" + CP + "].[Reporting Period], [qry_UNION_" + CP + "].[Invoice Date], [qry_UNION_" + CP + "].[SAP Account ID], [qry_UNION_" + CP + "].[Customer Name], [qry_UNION_" + CP + "].[Segment ID], [qry_UNION_" + CP + "].[Sub Segment ID], [qry_UNION_" + CP + "].[Total Rev Post Disc & Adj (USD)]" & _ 
From + ";"

 

[Gary_Mc_Mahon]

I got it to work, not sure what the prolem was......

Thanks
Gary