DELETING REDUNDANT OBJECTS

[Glint]

Hi All,

Following a tip I got from the forum yesterday, I want to find a way to make
it easy to find redundant queries in my database, prior to deleting them.
Because the application is large, I may not find it easy to check each area
one at a time to see it it works after a query has been renamed temporarily.

I was thinking that it should be possible to find out if a query has been
used as a form's recordsouce; that done, I could find out if the said query
featured at all in any of the form's combo or list boxes row sources.

However, I have been stuck at the first stage: finding out if a query has
been the recordsource of any form. Here is what I tried, using the AllForms
collection:

If Not IsNull(T1) Then
strResults = ""
numFound = 0
Dim obj As AccessObject, dbs As Object, frm As Form
Set dbs = Application.CurrentProject
For Each obj In dbs.AllForms
If TypeOf obj Is Form Then
Set frm = obj
If frm.RecordSource = T1 Then
numFound = numFound + 1
strResults = strResults & vbNewLine & " " & numFound
& ") " & frm.Name
End If
End If
Next obj
Text3 = strResults
Exit Sub
End If

T1 and T 3 are textboxes; the query name goes into T1, while T3 should
display the outcome of the search. strResults is String, while numFound is
Long. The sub is in the event procedure of a command button.

Unfortunately, T3 fires blank even when T1 contains a query that I know has
been used as a recordsource for a form. What should I do?

 

[Klatuu]

Here is a link to a really good Find and Replace utility:

http://www.rickworld.com/download.html

You can get an evaluation version free. If you buy it, it is only $39.00

I couldn't live without it.

What you do is enter the name of the query and it can show you everywhere in
your application the query's name is used. If the query is not used, it will
not find it and you know it is safe to delete it.

 

[Glint]

Thanks J_Goddard.
It does not appear that that error was the source of the problem. I
corrected it and changed the code to:

If Not IsNull(T1) Then
strResults = ""
numFound = 0
Dim obj As AccessObject, dbs As Object, frm As Form
Set dbs = Application.CurrentProject
For Each obj In dbs.AllForms
If obj.Type = acForm Then
Set frm = obj
If frm.RecordSource = T1 Then
numFound = numFound + 1
strResults = strResults & vbNewLine & " " & numFound
& ") " & frm.Name
End If
End If
Next obj
T3 = strResults
Exit Sub
End If

The main change in the code, apart from changing Text3 to T3, was to replace

"If TypeOf obj IS Form Then" with

"If obj.Type = acForm Then".

This still did not work.

It appears the line:

Set frm = obj

produced "Type mismatch" error.

Do you know how I can still reference the recordsources of all forms in the
database?

 

[Klatuu]

The allforms collection contains only forms, so it is not necessary to make
that. Also, you have to have a form open before you can see its recordsource
property

check:
If Not IsNull(T1) Then
strResults = ""
numFound = 0
Dim frm As Form

For Each frm In CurrentProject.AllForms
If frm.RecordSource = T1 Then
docmd.OpenForm frm.Name,acDesign,,,,acHidden
numFound = numFound + 1
strResults = strResults & vbNewLine & " " & numFound
& ") " & frm.Name
Docmd.Close acForm, frm.Name, acSaveNo
End If
End If
Next frm
T3 = strResults
Exit Sub
End If

 

[Glint]

Thanks again, Klatuu.
However, I got a "Type mismatch" at the level of

For Each frm In CurrentProject.AllForms

Is that because I am using Access 2003?

 

[Klatuu]

Sorry, Glint, What was I thinking. Here is a corrected version:

check:
If Not IsNull(T1) Then
strResults = ""
numFound = 0
Dim objForms As AllForms
Dim lngX As Long
Dim strFormName as String

For lngX = 0 To objForms.Count - 1
strFormName = objForms(lngX).Name
DoCmd.OpenForm strFormName, acDesign, , , , acHidden

If Forms(strFormName).RecordSource = T1 Then
numFound = numFound + 1
strResults = strResults & vbNewLine & " " & numFound
& ") " & frm.Name
Docmd.Close acForm, strFormName, acSaveNo
End If
End If
Next lngX
T3 = strResults
Exit Sub
End If

 

[Glint]

Thanks once more, Klatuu.

This time the error is

"Object variable or With block variable not set"

This is at the level

strFormName = objForms(lngX).Name

 

[Glint]

And when I tried inserting:

Set objforms = CurrentProject.AllForms,

before the offending line, I got "Object required" error message.

 

[Glint]

Klatuu,
I have great confidence that you will help me out in this one, as usual. But
I have to go right now. "See" you later.

 

[Klatuu]

don't know why you are getting those errors. I tested it before I posted it
this time and it worked for me.

 

[Glint]

Klatuu,
Really grateful for your help so far. After tinkering with the code you
posted, I finally stumbled on something that worked thus:

If Not IsNull(T1) Then
strResults = ""
numFound = 0
Dim accObj As AccessObject
Dim frm As Form
Dim strName As String
Dim i As Integer
Set db = CurrentDb
Dim rSource As String

For Each accObj In CurrentProject.AllForms
strName = accObj.Name
If strName <> "UsedTables" Then
DoCmd.OpenForm strName, acDesign, , , , acHidden
If Forms(strName).RecordSource = T1 Then
numFound = numFound + 1
strResults = strResults & vbNewLine & " " &
numFound & ") Form " & strName & " - Record Source"
End If
For Each Tx In Forms(strName).Controls
If TypeOf Control Is ComboBox Then
rSource = Tx.RowSource
If InStr(1, rSource, T1) > 0 Then
numFound = numFound + 1
strResults = strResults & vbNewLine & "
" & numFound & ") Combo " & qdf.Name & " - Row Source"
End If
End If
Next
DoCmd.Close acForm, strName, acSaveYes
End If
Next
strSearch1 = T1
For Each qdf In db.QueryDefs
strSQL = qdf.SQL
If InStr(1, strSQL, strSearch1) > 0 Then
numFound = numFound + 1
strResults = strResults & vbNewLine & " " & numFound &
") Query " & qdf.Name
End If
Next
T3 = strResults
End If

I included a query portion. As it is the form and the query portions work:
any form that has a recordsource based on the query is shown, and any query
that uses the query also shows up. And I am very grateful for your help so
far.

The problem now is two-folds. A typical report looks like this:

1) Form TB31TB - Record Source
2) Query ~sq_cTB~sq_cTB31TB
3) Query ~sq_cTBReport~sq_cTB31
4) Query ~sq_drptTBReport~sq_dTB31
5) Query ~sq_dTBReport~sq_dTB31
6) Query bTBUnion

From the above report, I know I used the query in question for the form
shown as number 1, and that I also used it to build query number 6. But I
have no idea where and how numbers 2 to 5 came about. Do you?

The second question is about going through combo boxes in a form to see if
the query in question featured in their rowsource property. But that section
of the code is not working; the reports have been blank for them. The portion
of the code reads:

For Each Tx In Forms(strName).Controls
If TypeOf Control Is ComboBox Then
rSource = Tx.RowSource
If InStr(1, rSource, T1) > 0 Then
numFound = numFound + 1
strResults = strResults & vbNewLine & "
" & numFound & ") Combo " & qdf.Name & " - Row Source"
End If
End If
Next

It is inside the part that opens a form in design and hidden view. Do you
know why it is not working?

 

[Klatuu]

Looks good, but I don't understand why my version didn't work for you.

Seems to me you are going to a lot of work when the download I suggested not
only does that, but allows for changing object names as well.