Hmm. One thought is to build a form that mimics the AddTable dialog,
and just modify what's in the SQL pane based on what the user does
there, not worrying about joins. People on the team are concerned about
the testing burdenhere, so this may well not make it into this release,
but for the future, and because I like this code, can y'all think of
states in which the query design window might be that would cause me
problems? What about sql statements that might cause this to fail?
Thanks much.
Jeremy
----
Function fnAddTableToQuery()
On Error GoTo Error
Dim bolShowSql As Boolean
Dim bolShowDiagram As Boolean
Dim bolShowGrid As Boolean
Dim strSql As String
Dim intLoc As Integer
Dim cbrAdvancedQuery As CommandBar
Dim strDelimiter As String
Dim strAddedTable As String
'In working code this would be a parameter of the function, but for the
test, I'll just hardcode it.
strAddedTable = "tblPayments"
'turn off the diagram and grid panes and turn on the sql pane
Set cbrAdvancedQuery = CommandBars("MetrixAdvancedQueryToolbar")
'Record the state of the views
bolShowSql = cbrAdvancedQuery.Controls("&SQL View").State
bolShowDiagram = cbrAdvancedQuery.Controls("&Diagram").State
bolShowGrid = cbrAdvancedQuery.Controls("&Grid").State
'turn off the diagram and grid panes and turn on the sql pane
Set cbrAdvancedQuery = CommandBars("MetrixAdvancedQueryToolbar")
If bolShowSql = 0 Then
Call DoCmd.RunCommand(acCmdViewShowPaneSQL)
End If
If bolShowDiagram = -1 Then
Call DoCmd.RunCommand(acCmdViewShowPaneDiagram)
End If
If bolShowGrid = -1 Then
Call DoCmd.RunCommand(acCmdViewShowPaneGrid)
End If
'Copy the sql of the query to the clipboard
Call DoCmd.RunCommand(acCmdSelectAll)
Call DoCmd.RunCommand(acCmdCopy)
strSql = ClipBoard_GetText 'This is a function in our library, I think
from the KB, though I'm not sure
'Modify the sql statement, adding the table
intLoc = InStr(1, strSql, " FROM ")
If intLoc = 0 Then
intLoc = InStr(1, strSql, Chr(10) & "FROM ")
End If
If Len(Trim(strSql)) - 5 > intLoc Then
strDelimiter = ","
End If
If intLoc < 19 Then
strSql = "SELECT " & strAddedTable & ".* FROM " & strAddedTable &
strDelimiter & " " & Mid(strSql, intLoc + 7)
Else
strSql = left(strSql, intLoc + 6) & strAddedTable & strDelimiter &
" " & Mid(strSql, intLoc + 7)
End If
Call ClipBoard_SetText(strSql) 'This is a function in our library, I
think from the KB, though I'm not sure
Call DoCmd.RunCommand(acCmdPaste)
'Make sure the diagram is showing and turn of the sql pane
If cbrAdvancedQuery.Controls("&Grid").State = 0 Then
Call DoCmd.RunCommand(acCmdViewShowPaneGrid)
End If
Call DoCmd.RunCommand(acCmdViewShowPaneSQL)
'Return each of the panes to their original states
If Not bolShowSql = cbrAdvancedQuery.Controls("&SQL View").State Then
Call DoCmd.RunCommand(acCmdViewShowPaneSQL)
End If
If Not bolShowDiagram = cbrAdvancedQuery.Controls("&Diagram").State
Then
Call DoCmd.RunCommand(acCmdViewShowPaneDiagram)
End If
If Not bolShowGrid = cbrAdvancedQuery.Controls("&Grid").State Then
Call DoCmd.RunCommand(acCmdViewShowPaneGrid)
End If
ExitPoint:
On Error Resume Next
Exit Function
Error:
Select Case Err.Number
Case Else
Call ErrorTrap(Err.Number, Err.Description, "fnAddTableToQuery")
End Select
GoTo ExitPoint
End Function
--
Jeremy Wallace
Fund for the City of New York
http://metrix.fcny.org