Combining two functions 2

[nancier]

These two functions have alot of repeated code. Anyone know if I could combine these somehow? Thanks


Option Compare Database
Option Explicit
Dim MyModule As Module

Function Command5_Click()
Dim StartLine As Long, StartColumn As Long
Dim EndLine As Long, EndColumn As Long
Dim strLine As String, strNewLine As String
Dim intChr As Integer, intBefore As Integer, intAfter As Integer
Dim strLeft As String, strRight As String
Dim strSearchText As String, strNewText

' The string you are searching for is:
strSearchText = "tblStupload.[NUM-1]"

' The replacement string is:
strNewText = "tblAmount.[" & Forms!Form1!Text2 & "] AS [NUM-1]"

' Open the Module you want to modify.
DoCmd.OpenModule "mdlDoItAll"

' Set the Created Code Modules as the Object.
Set MyModule = Application.Modules("mdlDoItAll")

' Search for string.
If MyModule.Find(strSearchText, StartLine, StartColumn, EndLine, _
EndColumn) Then

' Store text of line containing string.
strLine = MyModule.Lines(StartLine, Abs(EndLine - StartLine) + 1)

' Determine length of line.
intChr = Len(strLine)

' Determine number of characters preceding search text.
intBefore = StartColumn - 1

' Determine number of characters following search text.
intAfter = intChr - CInt(EndColumn - 1)

' Store characters to left of search text.
strLeft = Left$(strLine, intBefore)

' Store characters to right of search text.
strRight = Right$(strLine, intAfter)

' Construct string with replacement text.
strNewLine = strLeft & strNewText & strRight

' Replace the original line.
MyModule.ReplaceLine StartLine, strNewLine

Else
MsgBox "Text not found."
End If



End Function


Function Command9_Click()
Dim StartLine As Long, StartColumn As Long
Dim EndLine As Long, EndColumn As Long
Dim strLine As String, strNewLine As String
Dim intChr As Integer, intBefore As Integer, intAfter As Integer
Dim strLeft As String, strRight As String
Dim strSearchText As String, strNewText

' The string you are searching for is:
strSearchText = "tblStupload.[NUM-2]"

' The replacement string is:
strNewText = "tblAmount.[" & Forms!Form1!Text3 & "] AS [NUM-2]"

' Open the Module you want to modify.
DoCmd.OpenModule "mdlDoItAll"

' Set the Created Code Modules as the Object.
Set MyModule = Application.Modules("mdlDoItAll")

' Search for string.
If MyModule.Find(strSearchText, StartLine, StartColumn, EndLine, _
EndColumn) Then

' Store text of line containing string.
strLine = MyModule.Lines(StartLine, Abs(EndLine - StartLine) + 1)

' Determine length of line.
intChr = Len(strLine)

' Determine number of characters preceding search text.
intBefore = StartColumn - 1

' Determine number of characters following search text.
intAfter = intChr - CInt(EndColumn - 1)

' Store characters to left of search text.
strLeft = Left$(strLine, intBefore)

' Store characters to right of search text.
strRight = Right$(strLine, intAfter)

' Construct string with replacement text.
strNewLine = strLeft & strNewText & strRight

' Replace the original line.
MyModule.ReplaceLine StartLine, strNewLine

Else
MsgBox "Text not found."
End If

' Save and close the module.
DoCmd.Save acModule, MyModule
DoCmd.Close acModule, MyModule, acSaveYes

End Function

 

[PHV]

Option Compare Database
Option Explicit
Dim MyModule As Module
Sub ModifyModule(strSearchText As String, strNewText As String)
Dim StartLine As Long, StartColumn As Long
Dim EndLine As Long, EndColumn As Long
Dim strLine As String, strNewLine As String
Dim intChr As Integer, intBefore As Integer, intAfter As Integer
Dim strLeft As String, strRight As String
' Open the Module you want to modify.
DoCmd.OpenModule "mdlDoItAll"
' Set the Created Code Modules as the Object.
Set MyModule = Application.Modules("mdlDoItAll")
' Search for string.
If MyModule.Find(strSearchText, StartLine, StartColumn, EndLine, _
EndColumn) Then
' Store text of line containing string.
strLine = MyModule.Lines(StartLine, Abs(EndLine - StartLine) + 1)
' Determine length of line.
intChr = Len(strLine)
' Determine number of characters preceding search text.
intBefore = StartColumn - 1
' Determine number of characters following search text.
intAfter = intChr - CInt(EndColumn - 1)
' Store characters to left of search text.
strLeft = Left$(strLine, intBefore)
' Store characters to right of search text.
strRight = Right$(strLine, intAfter)
' Construct string with replacement text.
strNewLine = strLeft & strNewText & strRight
' Replace the original line.
MyModule.ReplaceLine StartLine, strNewLine
Else
MsgBox "Text not found."
End If
' Save and close the module.
DoCmd.Save acModule, MyModule
DoCmd.Close acModule, MyModule, acSaveYes
End Sub

Function Command5_Click()
ModifyModule "tblStupload.[NUM-1]", "tblAmount.[" & Forms!Form1!Text2 & "] AS [NUM-1]"
End Function

Function Command9_Click()
ModifyModule "tblStupload.[NUM-2]", "tblAmount.[" & Forms!Form1!Text3 & "] AS [NUM-2]"
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

or:

[blue]Public Sub CustomizeMod(Flg As Boolean)
   [green]'Flg = True = Execute 1st Functrion
   'Flg = False = Ececute 2nd Function[/green]
   
   Dim StartLine As Long, StartColumn As Long
   Dim EndLine As Long, EndColumn As Long
   Dim strLine As String, strNewLine As String
   Dim intChr As Integer, intBefore As Integer, intAfter As Integer
   Dim strLeft As String, strRight As String
   Dim strSearchText As String, strNewText
   Dim [purple][b]Idx1[/b][/purple] As String, [purple][b]Idx2[/b][/purple] As String
   
   If Flg Then
      [purple][b]Idx1[/b][/purple] = "1"
      [purple][b]Idx2[/b][/purple] = "2"
   Else
      [purple][b]Idx1[/b][/purple] = "2"
      [purple][b]Idx2[/b][/purple] = "3"
   End If
   
   ' The string you are searching for is:
   strSearchText = "tblStupload.[NUM-" & [purple][b]Idx1[/b][/purple] & "]"
   
   ' The replacement string is:
   strNewText = "tblAmount.[" & Forms!Form1!("Text" & [purple][b]Idx2[/b][/purple]) & "] AS [NUM-" & [purple][b]Idx1[/b][/purple] & "]"

   [green]' Open the Module you want to modify.
   '
   ' Main Code Body
   '
   ' Save and close the module.[/green]
    If Not Flg Then
      DoCmd.Save acModule, MyModule
      DoCmd.Close acModule, MyModule, acSaveYes
   End If

End Sub

Function Command5_Click()
   Call CustomizeMod(True)
End Function

Function Command9_Click()
   Call CustomizeMod(False)
End Function[/blue]

See Ya! . . . . . .

 

[nancier]

Thanks to both of you, terrific job!!!!!!