I know that i cant attach it in a NewsGroup so here is the code to the form
it anyone is interested.
Option Explicit
Option Compare Database
Private Sub cmdClose_Click()
If Not IsNull(Me![zstxtCalledFrom]) Then
If Me![zstxtCalledFrom] <> "" Then
Forms(Me![zstxtCalledFrom]).Visible = True
End If
End If
DoCmd.Close acForm, Me.Name
End Sub
Private Sub cmdRun_Click()
Const intKEYWORDMAX = 12
Const strLINE = " strSQL = strSQL & "
Const intLINEUP = 12
Const strCRTEXT = " & vbCrLf"
Dim strSQL As String
Dim strIn As String
Dim lngChar As Long
Dim blnkeyWord As Boolean
Dim intKeyWord As Integer
Dim strQ As String
Dim lngEnd As Long
Dim strOut As String
ReDim strKeyWord(intKEYWORDMAX) As String
' Should do this by grabbing one word "Element" at a time delimited by
'spaces or commas. Then determine what the word is and break
' to the start of the line on keywords and long elements.
' What is the new continuation character? make the form allow both
' continuations and/or & concatination
strKeyWord(1) = "INNER JOIN"
strKeyWord(2) = "LEFT JOIN"
strKeyWord(3) = "RIGHT JOIN"
strKeyWord(4) = "WHERE"
strKeyWord(5) = "GROUP BY"
strKeyWord(6) = "ORDER BY"
strKeyWord(7) = "HAVING"
strKeyWord(8) = "ON"
strKeyWord(9) = "FROM"
strKeyWord(10) = ","
strKeyWord(11) = "AND"
strKeyWord(12) = "OR"
strQ = Chr$(34)
strOut = " Dim strSQl As String" & vbCrLf
strOut = strOut & " strSQL = " & strQ
If Not IsNull(Me![txtSQL]) Then
strSQL = Me![txtSQL]
lngChar = 1
Do Until lngChar > Len(strSQL)
blnkeyWord = False
For intKeyWord = 1 To intKEYWORDMAX
If Mid(strSQL, lngChar, Len(strKeyWord(intKeyWord)) + 1) =
(strKeyWord(intKeyWord) & " ") Then
blnkeyWord = True
Exit For
End If
Next intKeyWord
If blnkeyWord Then
strOut = strOut & strQ & strCRTEXT & vbCrLf & strLINE & strQ &
Space$(intLINEUP - Len(strKeyWord(intKeyWord))) & strKeyWord(intKeyWord)
lngChar = lngChar + Len(strKeyWord(intKeyWord))
ElseIf Asc(Mid$(strSQL, lngChar, 1)) = 13 Or Asc(Mid$(strSQL, lngChar,
1)) = 10 Then
lngChar = lngChar + 1
Else
Select Case Asc(Mid$(strSQL, lngChar, 1))
Case 39
lngEnd = InStr(lngChar + 1, strSQL, Mid(strSQL, lngChar, 1))
strOut = strOut & Mid(strSQL, lngChar, lngEnd - lngChar + 1)
lngChar = lngEnd + 1
Case 34
lngEnd = InStr(lngChar + 1, strSQL, Mid(strSQL, lngChar, 1))
strOut = strOut & strQ & Mid(strSQL, lngChar, lngEnd - lngChar +
1) & strQ
lngChar = lngEnd + 1
Case 91
lngEnd = InStr(lngChar + 1, strSQL, "]")
strOut = strOut & Mid(strSQL, lngChar, lngEnd - lngChar + 1)
lngChar = lngEnd + 1
Case Else
lngEnd = lngChar
strOut = strOut & Mid(strSQL, lngChar, lngEnd - lngChar + 1)
lngChar = lngEnd + 1
End Select
End If
Loop
End If
Me![txtVBA] = strOut & strQ
End Sub
Private Sub lstQuery_Click()
Me![cmdRun].Enabled = True
Me![txtDesc] = Me![lstQuery].Column(2)
End Sub
Private Sub lstQuery_DblClick(Cancel As Integer)
Call cmdRun_Click
End Sub
