VB Query problem (MS ACCESS 2003)

[ZOR]

If only there was an easy way to convert a query grid SQL to a VB query.

The following errors on the line - FORMS!MAINFORM1!SUBFORM1.FORM.AthleteList.Column(0 ))); "

strSQL = "DELETE JUNCTION.ID2, JUNCTION.AthleteID "
strSQL = strSQL & "FROM JUNCTION "
strSQL = strSQL & "WHERE (((JUNCTION.ID2)=[FORMS]![MAINFORM1]![SUBFORM1].[FORM].[ID2]) "
strSQL = strSQL & "AND ((JUNCTION.AthleteID)=FORMS!MAINFORM1!SUBFORM1.FOR M.AthleteList.Column(0))); "

Any suggestions, thanks

[HansV]

It depends on how you want to use the SQL. Does this work?
<code>
strSQL = "DELETE ID2, AthleteID " & _
"FROM JUNCTION " & _
"WHERE ID2=" & Forms!MAINFORM1!SUBFORM1.Form.ID2 & _
" AND JUNCTION.AthleteID=" & Forms!MAINFORM1!SUBFORM1.Form.AthleteList.Column(0 )</code>

[ZOR]

Thankyou Hans, it certainly worked. I will now try and see where I was going wrong. I know it's lazy using the grid first in a query, it's quick, but then I spend hours trying to convert it to VB. It would be a dream to find an extra button in the query SQL marked convert to VB. But that would make people like me look too clever. There are many SQL varients ie MySql etc, where does one start. But thanks again for saving my sanity.

[HansV]

The problem is that queries within the Access interface have extra features that are not part of standard SQL, for example the ability to refer to controls on forms. When running a query or SQL in DAO or ADO, it will fail because they don't understand the reference to controls. This would make it difficult to write a converter.

[ZOR]

Thanks again Hans. That answers why my search on google for a converter comes up with nothing. It's a shame MS does not advise on workarounds. Just means I will never become as clever as you, maybe I should cut down on watching TV not that theres much to watch here in the UK. Best regards and thanks again.

[HansV]

> I will never become as clever as you

Just keep on practicing... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

[Support4John]

ZOR

Check out

SQL to VBA Formatter Version: 2000 (9.0)

Posted on 04/28/06 11:28 AM Attachment (291 downloads)
Posted by dallr - Utter Access VIP
Posts: 2399 - Loc: Trinidad & Tobago

Forum: Access Code Archive

http://www.utteraccess.com/forums/showflat...&Number=1165308

If you perfect it, post a copy

HTH, John

[HansV]

Note: you have to register as a member at Utter Access if you want to download attachments.

[ZOR]

Thanks Hans,John. I downloaded the program and threw a pasted Access query grid SQL into the SQKJet window and it did not like it, kept jumping into the form code with errors. Maybe I was being unkind to it, but the user instructions say you can just cut/paste the SQL from the query grid, but as I say was not very happy. Regards