This is an example of passing information to a sub routine and receiving it back using the CALL command:


When this form is executed, it comes up and asks the user to key in an address. Then the address is displayed in parts in the sub routine and put back together and passed to the calling routine for display.

The code behind the command button calls a routine and sends the input to the routine. The routine than splits up the name and displays it. When the routine is done, control returns to the command button routine that issued the call and the information is displayed in the field labeled SplitStreet.

NOTE: the wkadr = InputBox(...) is all coding on one line. On this copy it occupies 3 lines.

  Option Compare Database  
  Option Explicit  

  Private Sub cmdSplit_Click()  
     Dim wkadr As String  
        wkadr = InputBox("Street Number (no embedded blanks) space Street    
                Name space Street Type (only 2 chars)", "Enter Street   
        Call SplitName(wkadr)  
        txtStAdr = wkadr  
  End Sub  

  Private Sub SplitName(subwkadr As String)  
     Dim subwksploc As Integer, subwklen As Integer  
     subwksploc = InStr(subwkadr, " ")  
     Rem This is a debugging tool shows subwksploc  
     MsgBox (subwksploc)  
     subwklen = Len(subwkadr) - (subwksploc + 3)  
     Rem This is a debugging tool shows subwklen  
     MsgBox (subwklen)  
     txtStNum = Left(subwkadr, subwksploc - 1)  
     txtStNam = Mid(subwkadr, subwksploc + 1, subwklen)  
     txtStType = Right(subwkadr, 2)  
     subwkadr = txtStNum & " " & txtStNam & " " & txtStType  
  End Sub  

In this example, when the user clicks on the command button named cmdSplit and input box is displayed whichs asks the user to key in the address - a very specific format is provided. When the InputBox has received the answer the sub routine SplitName is called and the wkadr that just came in from the InputBox is passed to the subroutine. In the subroutine, the passed information is given the name subwkadr. Note: I could have used the same name. The address is split using the InStr, Left, Right, Mid and Len functions. The parts are shown on the screen. Then the address is concatenated back into subwkadr. When the subroutine is complete, control returns to the routine that contained the call and the next command is executed. This command puts the address on the screen in txtStAdr.