By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,836 Members | 2,059 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,836 IT Pros & Developers. It's quick & easy.

searching Access modules

P: 7
In VBA an Access module has a find method - works perfectly to find a string inside a module. i'm working in A97 (legacy) systems (large ones) and want to write code that searches all modules so that I can check on the possible effects of a change. But the 97 modules collection is open modules only.

Access 97 doesn't have an allmodules collection - 2000+ does - so I can move the app up for this purpose. But now I find that a module in allmodules is an 'access object' and doesn't respond to the module.find method.

Any ideas? thanks

Peter Hall
Jun 9 '07 #1
Share this Question
Share on Google+
3 Replies


JConsulting
Expert 100+
P: 603
In VBA an Access module has a find method - works perfectly to find a string inside a module. i'm working in A97 (legacy) systems (large ones) and want to write code that searches all modules so that I can check on the possible effects of a change. But the 97 modules collection is open modules only.

Access 97 doesn't have an allmodules collection - 2000+ does - so I can move the app up for this purpose. But now I find that a module in allmodules is an 'access object' and doesn't respond to the module.find method.

Any ideas? thanks

Peter Hall
Hi Pete,
I have two...that may or may not work with 97. I apologise if not, as I have no way to test them.

The first is a Joe Kendal module. The second is home grown for a specific purpose, but shows a loop through the Modules Container object.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  4. 'Function to Search for a String in a Code Module. It will return True if it is found and
  5. 'False if it is not. It has an optional parameter (NewString) that will allow you to
  6. 'replace the found text with the NewString. If NewString is not included in the call
  7. 'to the function, the function will only find the string not replace it.
  8. '
  9. 'Created by Joe Kendall 02/07/2003
  10. ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  11.  
  12. Public Function SearchOrReplace(ByVal ModuleName As String, ByVal StringToFind As String, _
  13.         Optional ByVal NewString, Optional ByVal FindWholeWord = False, _
  14.         Optional ByVal MatchCase = False, Optional ByVal PatternSearch = False) As Boolean
  15.  
  16.     Dim mdl As Module
  17.     Dim lSLine As Long
  18.     Dim lELine As Long
  19.     Dim lSCol As Long
  20.     Dim lECol As Long
  21.     Dim sLine As String
  22.     Dim lLineLen As Long
  23.     Dim lBefore As Long
  24.     Dim lAfter As Long
  25.     Dim sLeft As String
  26.     Dim sRight As String
  27.     Dim sNewLine As String
  28.  
  29.     Set mdl = Modules(ModuleName)
  30.  
  31.     If mdl.Find(StringToFind, lSLine, lSCol, lELine, lECol, FindWholeWord, _
  32.             MatchCase, PatternSearch) = True Then
  33.         If IsMissing(NewString) = False Then
  34.             ' Store text of line containing string.
  35.             sLine = mdl.Lines(lSLine, Abs(lELine - lSLine) + 1)
  36.             ' Determine length of line.
  37.             lLineLen = Len(sLine)
  38.             ' Determine number of characters preceding search text.
  39.             lBefore = lSCol - 1
  40.             ' Determine number of characters following search text.
  41.             lAfter = lLineLen - CInt(lECol - 1)
  42.             ' Store characters to left of search text.
  43.             sLeft = Left$(sLine, lBefore)
  44.             ' Store characters to right of search text.
  45.             sRight = Right$(sLine, lAfter)
  46.             ' Construct string with replacement text.
  47.             sNewLine = sLeft & NewString & sRight
  48.             ' Replace original line.
  49.             mdl.ReplaceLine lSLine, sNewLine
  50.         End If
  51.         SearchOrReplace = True
  52.     Else
  53.         SearchOrReplace = False
  54.     End If
  55.  
  56.     Set mdl = Nothing
  57. End Function
  58.  
  59. Public Sub tryme()
  60.     Dim DB As DAO.DataBase
  61.     Dim ctr As Container
  62.     Dim doc As Document
  63.     Dim amod As Module
  64.  
  65.     On Error Resume Next
  66.  
  67.     Set DB = CurrentDb
  68.     ' Set Container object variable.
  69.     Set ctr = DB.Containers("Modules")
  70.     For Each modu In ctr
  71. '        doc.Properties.Refresh
  72. '        If doc.Name <> "MSys*" And doc.Name <> "~*" Then
  73. '            DoCmd.OpenForm doc.Name, acDesign
  74. '            If Forms(doc.Name).HasModule = True Then
  75. '                DoCmd.OpenModule "Form_" & doc.Name
  76. '                Debug.Print SearchOrReplace("Mod_" & modu.Name, "testing")
  77. '            End If
  78. '            DoCmd.Close acForm, doc.Name
  79. '        End If
  80.     Next
  81.  
  82.     Set ctr = Nothing
  83.     DB.Close
  84.     Set DB = Nothing
  85.     Set doc = Nothing
  86. End Sub
  87.  
  88.  
I'll keep looking around to see if I hve another.
J
Jun 10 '07 #2

P: 7
Hi, thanks for this, the second part of the code trying the container is interesting and I'll try it asap today and tell youi how it goes, thanks again for taking the time to look for this

Peter

Hi Pete,
I have two...that may or may not work with 97. I apologise if not, as I have no way to test them.

The first is a Joe Kendal module. The second is home grown for a specific purpose, but shows a loop through the Modules Container object.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  4. 'Function to Search for a String in a Code Module. It will return True if it is found and
  5. 'False if it is not. It has an optional parameter (NewString) that will allow you to
  6. 'replace the found text with the NewString. If NewString is not included in the call
  7. 'to the function, the function will only find the string not replace it.
  8. '
  9. 'Created by Joe Kendall 02/07/2003
  10. ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  11.  
  12. Public Function SearchOrReplace(ByVal ModuleName As String, ByVal StringToFind As String, _
  13.         Optional ByVal NewString, Optional ByVal FindWholeWord = False, _
  14.         Optional ByVal MatchCase = False, Optional ByVal PatternSearch = False) As Boolean
  15.  
  16.     Dim mdl As Module
  17.     Dim lSLine As Long
  18.     Dim lELine As Long
  19.     Dim lSCol As Long
  20.     Dim lECol As Long
  21.     Dim sLine As String
  22.     Dim lLineLen As Long
  23.     Dim lBefore As Long
  24.     Dim lAfter As Long
  25.     Dim sLeft As String
  26.     Dim sRight As String
  27.     Dim sNewLine As String
  28.  
  29.     Set mdl = Modules(ModuleName)
  30.  
  31.     If mdl.Find(StringToFind, lSLine, lSCol, lELine, lECol, FindWholeWord, _
  32.             MatchCase, PatternSearch) = True Then
  33.         If IsMissing(NewString) = False Then
  34.             ' Store text of line containing string.
  35.             sLine = mdl.Lines(lSLine, Abs(lELine - lSLine) + 1)
  36.             ' Determine length of line.
  37.             lLineLen = Len(sLine)
  38.             ' Determine number of characters preceding search text.
  39.             lBefore = lSCol - 1
  40.             ' Determine number of characters following search text.
  41.             lAfter = lLineLen - CInt(lECol - 1)
  42.             ' Store characters to left of search text.
  43.             sLeft = Left$(sLine, lBefore)
  44.             ' Store characters to right of search text.
  45.             sRight = Right$(sLine, lAfter)
  46.             ' Construct string with replacement text.
  47.             sNewLine = sLeft & NewString & sRight
  48.             ' Replace original line.
  49.             mdl.ReplaceLine lSLine, sNewLine
  50.         End If
  51.         SearchOrReplace = True
  52.     Else
  53.         SearchOrReplace = False
  54.     End If
  55.  
  56.     Set mdl = Nothing
  57. End Function
  58.  
  59. Public Sub tryme()
  60.     Dim DB As DAO.DataBase
  61.     Dim ctr As Container
  62.     Dim doc As Document
  63.     Dim amod As Module
  64.  
  65.     On Error Resume Next
  66.  
  67.     Set DB = CurrentDb
  68.     ' Set Container object variable.
  69.     Set ctr = DB.Containers("Modules")
  70.     For Each modu In ctr
  71. '        doc.Properties.Refresh
  72. '        If doc.Name <> "MSys*" And doc.Name <> "~*" Then
  73. '            DoCmd.OpenForm doc.Name, acDesign
  74. '            If Forms(doc.Name).HasModule = True Then
  75. '                DoCmd.OpenModule "Form_" & doc.Name
  76. '                Debug.Print SearchOrReplace("Mod_" & modu.Name, "testing")
  77. '            End If
  78. '            DoCmd.Close acForm, doc.Name
  79. '        End If
  80.     Next
  81.  
  82.     Set ctr = Nothing
  83.     DB.Close
  84.     Set DB = Nothing
  85.     Set doc = Nothing
  86. End Sub
  87.  
  88.  
I'll keep looking around to see if I hve another.
J
Jun 10 '07 #3

P: 7
Hi J, thanks for this, the second part of the code trying the container is interesting and I'll try it asap today and tell youi how it goes, thanks again for taking the time to look for this

Peter
Jun 10 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.