VBA Form login credentials to module-CodePudding

I wanted to know if its possible to pass a variable from a Form to a module after the form is closed.

I had created this simple login when opening excel:

That uses the following code:

Option Explicit


Private Sub cmdLogin_Click()

    Dim Cn As ADODB.Connection
    Dim Rc As ADODB.Recordset
    Set Cn = New ADODB.Connection
    Dim user As String
    Dim password As String
    Dim sConnect As String
    
      
    user = Me.txtUserID.Value
    password = Me.txtPassword.Value
    
    
    
    sConnect = "Driver={Teradata};DBCname=xxxxx;DatabaseName=xxxxx;Uid=" & user & ";Pwd=" & password & "; Authentication=LDAP;"
    
    
    Cn.Open sConnect
    
  
    
    
    If Cn.State = 1 Then
    
        Unload Me
        Application.Visible = True
    Else
        If LoginInstance < 3 Then
                
                MsgBox "Invalid login credentials. Please Try again.", vbOKOnly   vbCritical, "Invalid Login Details"
                LoginInstance = LoginInstance   1
                
        Else
            MsgBox "You have exceeded the maximum number of login attempts.", vbOKOnly   vbCritical, "Invalid Credentials"
            Unload Me
            ThisWorkbook.Close savechanges:=False
            Application.Visible = True
            LoginInstance = 0
    
    
        End If
    
    End If
    
    
    

    


End Sub

Private Sub cmdSkip_Click()

        Unload Me
        Application.Visible = True

End Sub

Private Sub UserForm_Initialize()

    Me.txtUserID.Value = ""
    Me.txtPassword.Value = ""
    
    Me.txtUserID.SetFocus
    
End Sub

Its still abit of rough code but essentially with the login credentials im able to see if the user login connects to the server.

I have a module where in which I want to run SQL query from that server:

sConnect = "Driver={Teradata};DBCname=xxxxx;DatabaseName=xxxxx;Uid=" & user & ";Pwd=" & password & "; Authentication=LDAP;"
        
        
Cn.Open sConnect

Basically I want to store the username and password written During the login stage to then use it in the module.

Is this possible? because the form closes after logging in.

This is what the module should look like:

Public LoginInstance As Integer
Public user As String
Public password As String

Sub Extract_SUMMARY()

Dim Cn As ADODB.Connection
Dim Rc As ADODB.Recordset
Set Cn = New ADODB.Connection






table_query = "sql query"

summary_query = "sql query"

sConnect = "Driver={Teradata};DBCname=xxxx;DatabaseName=xxxxx;Uid=" & user & ";Pwd=" & password & "; Authentication=LDAP;"


Cn.Open sConnect


Set table_set = New ADODB.Recordset

    table_set.Open table_query, Cn
    Worksheets("Higher Level Analysis").Cells(2, 4).CopyFromRecordset table_set
    table_set.Close



Set summary_set = New ADODB.Recordset

    summary_set.Open summary_query, Cn
    Worksheets("Higher Level Analysis").Cells(5, 1).CopyFromRecordset summary_set
    summary_set.Close

Cn.Close




Set recset = Nothing



End Sub

As you can see I tried to create a public variable but I dont think it saves after the form is closed...

Can someone help?

CodePudding user response：

You should use Private declarations for the respective variables, but use Public variables in a standard module:

Public userM As String 
Public passwordM As String

Then give them a value (in two ways):

Private Sub cmdSkip_Click()
    userM = user: passwordM = password
    Unload Me
    Application.Visible = True
End Sub

or use Terminate event:

Private Sub UserForm_Terminate()
   userM = user: passwordM = password
End Sub

You must understand that these variables are volatile, I mean, they are valid (staying in memory) until an error stopping the code appear... So, you can also save their value in a hidden sheet...

Edited:

In fact you can avoid all the above scenario, moving the existing variables in a standard module. They will be used from all project, form included... But still remains the volatility problem.