Find and Replace in MS Access

[ctaylor]

I have been using notepad for quite a while to write/edit UNION and other queries.

The primary reason for using notepad is to take advantage of the FIND/REPLACE utility.

Have any of you ever written a module that would be enable the FIND/REPLACE utility within the SQL VIEW of a query?

[Creosote]

I've never heard of it being able to be done. But I guess it depends on the version of Access. Anyways, you can make a form to examine your queries. You can do a find/replace on your form, if you change your find criteria to "Match Any Part of Field." I made a little form to do that. You would want to add error checking/etc if you wanted to use it. You have to reference DAO in the VBA as well. I'll attach the code and pic.

Option Compare Database
Dim db As Database
Dim qd As QueryDef
Private Sub Form_Load()
    Set db = CurrentDb
    LoadQueries
    Command1_Click
End Sub
Private Sub Form_Unload(Cancel As Integer)
    Set db = Nothing
    Set qd = Nothing
End Sub
Private Sub Combo1_Change()
    If Combo1.SelText = "*NEW*" Then
        Set qd = db.CreateQueryDef(InputBox("Enter a Name"))
    Else
        ShowSQL
    End If
End Sub
Private Sub Command1_Click()
    On Error GoTo cmd1_err
    Text1.SetFocus
    If qd Is Nothing = False Then qd.SQL = Text1.Text
    LoadQueries
cmd1_err:
    If Err.Description <> "" Then MsgBox Err.Description
End Sub
Sub ShowSQL()
    Set qd = Nothing
    Set qd = db.QueryDefs(Combo1.SelText)
    Text1.SetFocus
    Text1.Text = qd.SQL
End Sub
Sub LoadQueries()
    Combo1.SetFocus
    Dim i As Integer
    If Combo1.ListCount > 1 Then
        For i = 0 To Combo1.ListCount - 1
            Combo1.RemoveItem (0)
        Next i
    End If
    Set qd = Nothing
    Combo1.AddItem "*NEW*"
    For Each qd In db.QueryDefs
        Combo1.AddItem qd.Name
    Next
End Sub

[ctaylor]

Thanks for the code.

Unfortunately it does not working with MS Access 2000 - the AddItem and RemoveItem properties are only available in MS Access 2002 according to one forum post I found, but I'm not sure if this is true...

Perhaps this can be resoplved with another reference? I did find on the MSDN site an Office 2000 article that used this property in a MS Word 2000 mail merge code example though. (http://msdn.microsoft.com/library/de...MergePartI.asp)


Also, I am unable to open the picture you posted. I gather from the code that there is a combo box named combo1, text box called text1, and a command button called command1. Is this all I should need for the form?

[Creosote]

Well that sucks about the pic. Here is the link if you want to copy and paste it into your browser:

http://www.end-of-file.com/misc/accessfr3.jpg.

I seriously doubt it is true about the add/remove item. If it is, then it can probably be resolved by reference an Active X control. Do you have VB or Visual Studio installed? You can use some controls from those in Access.
But if that is not possible, there would have to be a way to add items to a combo box programattically in Access 2K IMO.

[ctaylor]

Thanks for the reply.

I did some more research and found that "In othe rapplications, these controls have AddItem and RemoveItem methods to add and remove items. These methods are not supported for Access list and combo box controls. Instead you use combinations of RowSource and RowSourceType properties to specify the data that appears in a list box or combo box control." Microsoft Office 2000/Visual Basic Programmer's Guide, P. 136.

When looking at examples, I see that a combo box should have its properties set as follows with MS Access 2000

With Me!cboEmployees
.RowSourceType = "Table/Query" 'is a valid line of code
.RowSource = "SELECT EmployeeID, FirstName LastName
FROM Employees
ORDER BY LastName"
.BoundColumn = 1
.ColumnCount = 3
.ColumnWidths = "0in; .5in; .5in"
.ColumnHeads = False
.ListRows = 5
End with

I understand this example well as I have used this kind of language before. Unfortunately I have limited experience with VBA access to DAO. This being the case, would I be correct in thinking that the QueryDefs object is a COLLECTION that contains query names? If so, I think I understand what the LoadQueries procedure is doing, Though I am not fully sure how to re-write the RowSource property in the above example. Perhaps a new recordset object should be created in the LoadQueries procedure that could be assigned to the RowSource Property? What do you think?

[ctaylor]

In answer to your earlier question, I do not have Visual Studio installed. I work in the "wrong department" to have access to those kinds of development tools. (I'm an analyst, no longer an IT guy.) Given that limitation, VBA is all that is available to me.

[Creosote]

That's why it doesn't work. You have to change .RowSourceType to "Value List". At least in Access 2002, you have to change it, and .AddItem works.

[ctaylor]

I am able to set the RowSource property of the combo box equal to the following code

.RowSource = "select t.Name from MSysObjects t " & _
"where t.Type = 5 and t.Flags = 0;"

The t.Type = 5 argument selects only queries.

Now the problem is the non-support of the .SQL property in Access 2000. This is an odd problem because both Access 97 and Access 2002 support the property.

This is an issue in the ShowSQL procedure.

I'm very pleased with how close we have gotten to making this work.

[Creosote]

Cool. Make sure you are referencing DAO 3.6, or the newest DAO you have. The .SQL property is of the QueryDef object. So what I'm saying is that in case there are other libraries that have QueryDef object, you need to make sure you are using a DAO Query Def object.