append query doesn't work in macro

[scorpio1769]

Heello.

I have an append query which will not execute within a macro. There are no conditions set for the execution of the "OpenQuery" command so it should run every time the macro is run. I turned warnings on and the macro indeed says "your about to append x records...", but it doesn't happen. I then switched the command from an "OpenQuery" to a "RunSQL" and still no luck. When the query is run outside of the macro, it appends as expected.

Any ideas.

Thank you much.

 

[scorpio1769]

UPDATE: I also wrote a VB function (below) and called it in the macro via the RunCode command. Still does not work. The function appends as expected when run directly from the vb editor.

Function AppendRSNI()
On Error GoTo AppendRSNI_Err
Dim strSQl As String

DoCmd.SetWarnings False

strSQl = "INSERT INTO [Inv_Shipment Details] ( [CA Number], [Serial Number], [Description], [Ship Date], [Sales Order], [Customer PO], [SC Number], [SourceID] )SELECT [Shipment Details RS&I].[R00 Number], [Shipment Details RS&I].[Serial Number], [Shipment Details RS&I].Item, [Shipment Details RS&I].[Inv Date], [Shipment Details RS&I].[Inv #], Forms![Frm_Shipment Details Prompt]!Combo4, 'S00RSNI', 3 FROM [Shipment Details RS&I];"
DoCmd.RunSQL strSQl

DoCmd.SetWarnings True

AppendRSNI_Exit:
Exit Function

AppendRSNI_Err:
MsgBox Error$
Resume AppendRSNI_Exit

End Function

 

[dhookom]

Not sure if this would cause your issue but you have no space before "SELECT ...". Also, I would resolve the form/control reference. This assumes Combo4 (BTW a horrible name) is a string type.

strSQl = "INSERT INTO [Inv_Shipment Details] ( [CA Number], [Serial Number], [Description], [Ship Date], [Sales Order], [Customer PO], [SC Number], [SourceID] ) SELECT [R00 Number], [Serial Number], Item, [Inv Date], [Inv #],[blue]""" & Forms![Frm_Shipment Details Prompt]!Combo4 & """[/blue], 'S00RSNI', 3 FROM [Shipment Details RS&I]"

[blue]Debug.Print strSQl[/blue]

DoCmd.RunSQL strSQl





Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]