Treat query in VBA like a recordset??

[Frenstall]

I've searched the internet far and wide and can't seem to find an answer for this.

I need to run a query 1/2 through vba code in my form(because I import a file first). I then need to add new records to a table using fields from that form I'm in and from the query. I would like to do it similar to db.openRecordset("myTable") but it doesn't want to load queries in Access 2000.

I also tried runSQL but the statement copied from the query refuses to run(its kinda long):

DoCmd.RunSQL "SELECT T_JEV_Vendors.NAME, Sum(JEVImport!F17*T_JEV_Vendors!AmtResponsible) AS PPH FROM JEVImport INNER JOIN T_JEV_Vendors ON JEVImport.F3 = T_JEV_Vendors.JEV_Question GROUP BY T_JEV_Vendors.NAME ORDER BY Sum(JEVImport!F17*T_JEV_Vendors!AmtResponsible) DESC"

Does anyone know how to open a query like a recordset in VBA or have a better way of doing this??? MUCH APPRECIATED!!!

 

[TheAceMan1]

How are ya Frenstall . . . . .

Are [blue]JEVImport!F17[/blue] and [blue]T_JEV_Vendors!AmtResponsible[/blue] [purple]actual field names[/purple] or are they [purple]controls on a form[/purple] as they appear to be?

If I'm not mistaken the exclamations ([red]![/red]) in your SQL should be periods ([red].[/red]) (in [red]red[/red]) below:

[blue]SELECT T_JEV_Vendors.NAME, Sum(JEVImport[red][b]![/b][/red]F17*T_JEV_Vendors[red][b]![/b][/red]AmtResponsible) AS PPH FROM JEVImport INNER JOIN T_JEV_Vendors ON JEVImport.F3 = T_JEV_Vendors.JEV_Question GROUP BY T_JEV_Vendors.NAME ORDER BY Sum(JEVImport[red][b]![/b][/red]F17*T_JEV_Vendors[red][b]![/b][/red]AmtResponsible) DESC[/blue]

See Ya! . . . . . .

 

[PHV]

The DoCmd.RunSQL method is for action queries only.
You may try something like this:
Dim rs As DAO.Recordset
Set rs = CurrentDV.OpenRecordset("SELECT ....

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244