Return data with a RunSQL cmd in VB

[dimbodoyle]

Hi FOlks
I am trying to get access to return data to a data sheet when I run an sql select statement through vb. For some reason it wont work and says:
"A RunSQL action requires and argument consiting ofan SQL statement."

I have included the piece below
SELECT VALID_ALTS_AND_REPLS.PART_NUMBER, VALID_ALTS_AND_REPLS.OPTION, PARTS_WK_27.[PART DESCRIPTION], PARTS_WK_27.[FUNCTIONAL DESCRIPTION], PARTS_WK_27.[RETURN TO], PARTS_WK_27.[FBSC COST], TSLs.DEPOT, TSLs.MIN, TSLs.MAX
FROM TSLs INNER JOIN (VALID_ALTS_AND_REPLS INNER JOIN PARTS_WK_27 ON VALID_ALTS_AND_REPLS.OPTION=PARTS_WK_27.[PART NUMBER]) ON TSLs.PART_NUMBER=VALID_ALTS_AND_REPLS.OPTION
WHERE (((TSLs.DEPOT)=91101) AND ((TSLs.MIN)>0))
ORDER BY PARTS_WK_27.[FBSC COST];
The code is Dim frag1 As String
Dim frag2 As String
Dim SQL_cmd2 As String
Dim depot As String
depot = InputBox("Enter the Depot Code you wish to query", "Alternative-Replacement Search")

frag1 = "SELECT VALID_ALTS_AND_REPLS.PART_NUMBER, VALID_ALTS_AND_REPLS.OPTION, PARTS_WK_27.[PART DESCRIPTION], PARTS_WK_27.[FUNCTIONAL DESCRIPTION], PARTS_WK_27.[RETURN TO], PARTS_WK_27.[FBSC COST], TSLs.DEPOT, TSLs.MIN, TSLs.MAX FROM TSLs INNER JOIN (VALID_ALTS_AND_REPLS INNER JOIN PARTS_WK_27 ON VALID_ALTS_AND_REPLS.OPTION = PARTS_WK_27.[PART NUMBER]) ON TSLs.PART_NUMBER = VALID_ALTS_AND_REPLS.OPTION WHERE (((TSLs.DEPOT)="
frag2 = ") AND ((TSLs.MIN)>0)) ORDER BY PARTS_WK_27.[FBSC COST];"
SQL_cmd2 = frag1 & depot & frag2
MsgBox (SQL_cmd2)
DoCmd.RunSQL SQL_cmd2


Anyone any ideas? Am i using the wrong method or can runSQL not return data to a sheet as in a select statement?
regards

 

[pbaldy]

Not sure where to start. The main point is that RunSQL is for running action queries; those that append, update or delete data. If you're trying to populate a datasheet, perhaps you want to set the source of a form or subform to that string?

You declare frag2 but never set it, then try to use it building frag1. I can't imagine that this line could produce valid SQL:

SQL_cmd2 = frag1 & depot & frag2

since frag1 contains the semi-colon that ends an SQL string plus the ORDER BY clause, which wouldn't expect anything after it. Since it looks like the only dynamic part of the SQL is the criteria, why not just create a saved query that either looks to a form control or requests ([Enter..]) the criteria?

 

[dimbodoyle]

Hey Thanks for that
I want to populate a data sheet alrite but i thought i had it properly formed. This query operates on a table that is created earlier in the query and I would like to keep it all in that function so ideally i am looking to create a query object with the statement and then do open query or else run a command that will create a datasheet from the statement.

 

[pbaldy]

Well, I'd populate a form. My users never see anything but forms and reports. The sample db on this FAQ from another site creates SQL and assigns it to a subform for display. Maybe it will give you a head start. The FAQ itself would also help you learn how to build SQL in VBA.

http://www.mdbmakers.com/forums/showthread.php?t=4895

But again, since there's nothing dynamic in that SQL, it makes no sense to build it in VBA. It would run more efficiently as a saved query.