Getting a userform in a different module to cooperate.

[ClearlyAnIdiot]

I made a userform in another module and got it into a subroutine for a form, but it doesn't seem to affect what goes on in the subroutine, The code was

 Private Sub UpdateI_Click()

Dim Popup As Role, Sub4 As String, PerName As String, Sub3 As String, Sub2 As String, Sub1 As String
Set Popup = New Role
        Popup.Show
        Select Case Popup.Tag
          Case 1
            'Do Sub1
        
          Case 2
            'Do Sub2
          Case 3
            'Do Sub3
          Case 4
          'Do Sub4
    End Select
    
    
    PerName = InputBox("What is their name?")

    Sub4 = "UPDATE Individuals SET [ShareholderOf] = " & Me.CompanyNo & " WHERE [Name] = '" & PerName & "';"
    DoCmd.RunSQL Sub4
    
    Sub3 = "UPDATE Individuals SET [ResDirOf] = " & Me.CompanyNo & " WHERE [Name] = '" & PerName & "';"
    DoCmd.RunSQL Sub3
    
    Sub2 = "UPDATE Individuals SET [DirOf] = " & Me.CompanyNo & " WHERE [Name] = '" & PerName & "';"
    DoCmd.RunSQL Sub2
    
    Sub1 = "UPDATE Individuals SET [AltDirOf] = " & Me.CompanyNo & " WHERE [Name] = '" & PerName & "';"
    DoCmd.RunSQL Sub1
    
    Set Popup = Nothing
End Sub

Basically, I want it so that when I click on one of the cases in the userform, it will perform that subroutine and that subroutine exclusively. For example, I want it so that when I click on case 3, it will only update the field "ResDirOf", and so on. At the moment, it indiscriminately performs all 4 "subs", regardless of what I click.
PS: "Role" is the name of the userform.

 

[madefemere]

Hello,

And like this ?

 Private Sub UpdateI_Click()  
Dim Popup As Role, strsub As String, PerName As String

PerName = InputBox("What is their name?")
Set Popup = New Role    
Popup.Show    
Select Case Popup.Tag           
Case 1      
strsub = "UPDATE Individuals SET [AltDirOf] = " & Me.CompanyNo &  " WHERE [Name] = '" & PerName & "';"              
Case 2      
strsub = "UPDATE Individuals SET [DirOf] = " & Me.CompanyNo & " WHERE [Name] = '" & PerName & "';"           
Case 3      
strsub = "UPDATE Individuals SET [ResDirOf] = " & Me.CompanyNo & " WHERE [Name] = '" & PerName & "';"           
Case 4      
strsub = "UPDATE Individuals SET [ShareholderOf] = " & Me.CompanyNo & " WHERE [Name] = '" & PerName & "';"     
End Select    
    DoCmd.RunSQL strsub        
Set Popup = Nothing 
End Sub

 

[ClearlyAnIdiot]

Heh, to think I was making a mountain out of a molehill with my problem .