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 Address") 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.