Substitute a querry for a table in ALL the forms in a large databa

[Gene]

I built a large data base (when I was young and naive) with most of the form
drop down boxes based on a table. I had to split that table into smaller
tables and construct a querry to provide exactly the same data. The name of
the querry is qryManager and the name of the original table was tblManager.
It was easy to change all the VBA references by the 'search and replace'
edit, and because all the fields were exactly the same, it worked. Is there
a way to "easily" change all the Recordsource and rowsource references in
forms? Do I have to open each form and manually change each reference?
Hoping there is an easy solution!
Gene

 

[tina]

try

Public Sub isUpdate()

Dim frm As AccessObject, ctl As Control

For Each frm In Application.CurrentProject.AllForms
DoCmd.OpenForm frm.Name, acDesign
With Forms(frm.Name)
If .RecordSource = "tblManager" Then
.RecordSource = "qryManager"
End If
For Each ctl In .Controls
If TypeOf ctl Is ComboBox Then
If ctl.RowSource = "tblManager" Then
ctl.RowSource = "qryManager"
End If
End If
Next
DoCmd.Save
DoCmd.Close
End With
Next

End Sub

hth

 

[Rick Brandt]

I built a large data base (when I was young and naive) with most of the form
drop down boxes based on a table. I had to split that table into smaller
tables and construct a querry to provide exactly the same data. The name of
the querry is qryManager and the name of the original table was tblManager.
It was easy to change all the VBA references by the 'search and replace'
edit, and because all the fields were exactly the same, it worked. Is there
a way to "easily" change all the Recordsource and rowsource references in
forms? Do I have to open each form and manually change each reference?
Hoping there is an easy solution!
Gene

This is one big reason why I don't use the prefixes tbl and qry. If I were in
your shoes I would simply rename the table and name the new query with the name
that the table previously had and I would be done.