Editing a Query

 < Day Day Up > 



The query values are stored in the ComboBox control on the Excel2k3 VBA Query command bar. Each query has its own entry, which can be selected by choosing the appropriate entry from the drop-down list. Pressing the Edit Query button on the command bar loads the DBQuery UserForm (shown in Figure 24-5) with the currently selected query from the ComboBox control on the command bar.

click to expand
Figure 24-5: The DBQuery form is essentially a large textbox that allows the user to enter his query.

The following code is executed when the UserForm is loaded. After locating the proper command bar, the FindControl method is used to get an object pointer to the Command barComboBox control by searching for the proper Tag property value. Then, assuming that the control exists, the Text property of the combo box control is copied to the large, multi-line textbox control on the UserForm.

Private Sub UserForm_Initialize()

Dim c As CommandBar
Dim cc As CommandBarComboBox

On Error Resume Next
Set c = Application.CommandBars("Excel2k3 VBA Query")
Set cc = c.FindControl(, , "Excel2k3 VBA Query Statement")
If Not cc Is Nothing Then
Query.Text = cc.Text

End If

End Sub

Once the user has made the desired changes to the query, pressing the Close button closes the user form.

Private Sub CommandButton1_Click()

Unload Me

End Sub

When the user form is closed, the SaveData routine is called from the UserForm_Terminate event to insure that the query is always saved when the form is closed. By placing the call to SaveData here, the query is saved no matter how the form is closed.

Private Sub UserForm_Terminate()

SaveData

End Sub

The SaveData routine copies the query statement to the Text property of the CommandBarComboBox control using the same basic technique that was used to load the query.

Sub SaveData()

Dim c As CommandBar
Dim cc As CommandBarComboBox

On Error Resume Next
Set c = Application.CommandBars("Excel2k3 VBA Query")

Set cc = c.FindControl(, , "Excel2k3 VBA Query Statement")
If Not cc Is Nothing Then
cc.Text = Query.Text

End If

End Sub

Notice that saving the query in this fashion triggers the OnAction event associated with this control, which is the EnterDatabaseQuery routine located in the ThisWorkbook module, which is shown here. This routine scans the list of queries stored in the control and appends the new query to the end if it isn't found, letting users rerun a particular query quickly.

Sub EnterDatabaseQuery()

Dim c As CommandBar
Dim cc As CommandBarComboBox
Dim q As String
Dim i As Long

On Error Resume Next
Set c = Application.CommandBars("Excel2k3 VBA Query")
Set cc = c.FindControl(, , "Excel2k3 VBA Query Statement")
If Not cc Is Nothing Then
q = cc.Text
i = 1
Do While i <= cc.ListCount
If q = cc.List(i) Then
Exit Sub

End If

i = i + 1

Loop

cc.AddItem cc.Text

End If

This routine begins by locating the CommandBarComboBox control, and then saves the value from the Text property into a temporary variable named q. Next the routine uses a Do…While loop to scan through the list of queries stored in the drop-down list. If a match is found, the Exit Sub statement ends the routine without action. If no match is found, the AddItem method is used to add the query to the end of the list of drop-down items.

Note 

One limitation of this approach is that the queries are lost when the user exits Excel. The program shouldn't take up a lot of space in the Windows registry just to store the history of queries, and, while this version of the program doesn't use an external file that would need to be loaded and saved each time Excel starts, it would be straightforward to add a Save/Load set of dialog boxes to this form that would let you save an individual query to disk.



 < Day Day Up > 



Microsoft Excel 2003 Programming Inside Out
Microsoft Office Excel 2003 Programming Inside Out (Inside Out (Microsoft))
ISBN: 0735619859
EAN: 2147483647
Year: 2006
Pages: 161

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net