Compile error Sub or Function Not Defined

[Dave14867]

Hello all,



I am getting the error "Compile error Sub or Function Not Defined"

I have the following code defining the Sub in a module

Public Sub GetuUserName()
Dim User As String
Dim Username As String

End Sub

And on the Form Load Event I have the following code to call it

Private Sub Form_Load()
Call GetUserName
'Public User As String
Dim User As String


txtUser.SetFocus
txtUser.Text = User

I get the error on the Form_Load() event

I am trying to set the Text in the txtUser field as the person who logged into the database

I am not real good at VBA so don't make assumptions about what I know and where it should be placed.

Any help is greatly appreciated

[Dave14867]

Found stupid typo with the Public sub Name, however txtUser is not being set in the form load event as expected.

[Micron]

This

Public Sub GetuUserName()
    Dim User As String
    Dim Username As String
    
End Sub

1) Basically that does nothing. 1st 2 lines declare string variables. Their scope/visibility is limited to that sub so when it is done, they are 'destroyed'. Next line ends it - all done. So there must be more that you have that you didn't post? If not and you copied that from somewhere, you must have missed part of it.
2) A sub cannot return a value to the procedure that called it. For that you need a function. While it might be possible for a function to return a value to whatever called it without declaring its return data type, I'm going to say that the function must end with the part that defines what it is returning; e.g. string, date, some kind of number, etc. By that I mean along the lines of

Public Function myFunction() As String

Really no need to worry about your form load code until you come up with the missing link.

[June7]

Common topic.

If you want to retrieve the user Windows network login USERNAME, consider:

Code in a general module

Option Compare Database
Option Explicit

Public Function GetUserName() As String
GetUserName = Environ("USERNAME")
End Function

That function can be called from query, VBA, or textbox.

Expression in textbox: =GetUserName()

Those variables aren't even needed unless you don't want to make repeated calls to the function.

[Dave14867]

Micron &June7,
I want to get the UserName from the txtUsername textbox that the user enters in the login screen of the database and be able to use it to populate a field called txtUser

Here is the code I am using now in the module

Public Function GetUserName() As String Dim User As String
Dim Username As String
Dim Name As String
Name = Username


End Function

Then, in the btnLogin_Click event is the following code (much of this has been commented out currently)

Private Sub btnLogin_Click()


' Look into adding new tblDevelopers and add new if statement If rs.NoMatch to look at tblDevelopers to see if a match is found.




Me.txtUserName.SetFocus


Dim rs As Recordset
Dim db As Database


Set db = CurrentDb
Set rs = db.OpenRecordset("tblUsers", dbOpenSnapshot, dbReadOnly)

rs.FindFirst "UserName='" & Me.txtUserName & "'"

If rs.NoMatch = True Then
Me.lblWrongUser.Visible = True
Me.txtUserName.SetFocus
Exit Sub
End If
Me.lblWrongUser.Visible = False

If rs!UserPassword <> Nz(Me.txtPassword) Then
Me.lblWrongPassword.Visible = True
Me.txtPassword.SetFocus
Exit Sub
End If
Me.lblWrongPassword.Visible = False

If rs!UserType = 4 Then 'Checks to what user Priviledges User Has Developer Priviledges
Dim prop As Property
Call GetUserName
User = txtUserName
On Error GoTo SetProperty
Set prop = CurrentDb.CreateProperty("AllowByPassKey", dbBoolean, False)

CurrentDb.Properties.Append prop

SetProperty:
If MsgBox("Would you like to turn on the bypass key?", vbYesNo, "Allow Bypass") = vbYes Then
CurrentDb.Properties("AllowBypassKey") = True
Else
CurrentDb.Properties("AllowBypassKey") = False

End If


End If
DoCmd.OpenForm "frmNorthPointMain"
DoCmd.Close acForm, "frmLoginScreen"


If rs!UserType = 1 Then 'Checks to what user Priviledges User Has, opens frmNavigation
DoCmd.Close acForm, "frmNorthPointMain"
User = txtUserName
' DoCmd.OpenForm "frmAdmin" 'Commented out 9/11/22
DoCmd.Close acForm, "frmLoginScreen"
End If

If rs!UserType = 2 Then 'Checks to what user Priviledges User Has, opens frmNavDataEntry
CurrentDb.Properties("AllowBypassKey") = False
DoCmd.OpenForm "frmNorthPointMain"
User = txtUserName
' DoCmd.OpenForm "Lot Number Entry"
' DoCmd.OpenForm "Inventory Transactions" 'Commented out 9/11/22
' DoCmd.OpenForm "Purchase Orders" 'Commented out 9/11/22
' DoCmd.OpenForm "DHREntryNewMethod" 'Commented out 9/11/22
DoCmd.Close acForm, "frmLoginScreen"
End If

If rs!UserType = 3 Then 'Checks to what user Priviledges User Has, opens frmNavusers
CurrentDb.Properties("AllowBypassKey") = False
DoCmd.Close acForm, "frmNorthPointMain"
User = txtUserName
' DoCmd.OpenForm "frmNavUsers" 'Commented out 9/11/22
DoCmd.Close acForm, "frmLoginScreen"
End If

If rs!UserType = 5 Then 'Checks to what user Priviledges User Has, opens frmLabels-Users
CurrentDb.Properties("AllowBypassKey") = False
DoCmd.Close acForm, "frmNorthPointMain"
User = txtUserName
' DoCmd.OpenForm "frmLabels-Users" 'Commented out 9/11/22
DoCmd.Close acForm, "frmLoginScreen"
End If

If rs!UserType = 7 Then 'Checks to what user Priviledges User Has, opens frmLabels-Users
CurrentDb.Properties("AllowBypassKey") = True
' CurrentDb.Properties("AllowBypassKey") = False Commented out 9/15/22
DoCmd.OpenForm "frmNorthPointMain"
DoCmd.OpenForm "frmAssetTruck"
DoCmd.OpenForm "frmEmployees"
User = txtUserName
' DoCmd.Close acForm, "frmNorthPointMain" 'Commented out 9/15/22
' DoCmd.OpenForm "frmLabels-Users" 'Commented out 9/11/22
DoCmd.Close acForm, "frmLoginScreen"
' frmSubJobDescription.Height = 6.5
' frmSubDrivers.Visible = no
' frmSubPTO.Visible = no
' frmsubemppaperwork.Visible = no
' frmsubemployeehired.Visible = no

End If



End Sub



The txtUsername in the above code returns the User Name as I want it to.
So I guess txtUserName is being destroyed and not preserved as I want because when I open the frmEmployees the txtUsername is ""

Now I get the name of the form that is loaded which is frmEmployees

I am obviusly missing something but I don't know or understand what it is.

[June7]

Need to declare those user variables in a general module header in order for them to available to multiple modules and procedures. If you only want them available to the form procedures then declare them in form module header. I think this was explained in another of your threads.

Option Compare Database
Option Explicit

Public Username As String
Public Name As String

Name is a reserved word and advise not to use reserved words as names for anything. If Username is a field name, advise not to use it as a variable name.

Why do you refuse to post code between CODE tags in spite of having been previously asked to do so?

[Micron]

Why do you refuse to post code between CODE tags in spite of having been previously asked to do so?

I had checked a couple of hours ago but could find no posts by this OP with keywords "code tags" so here it is cleaned up and posted with tags this one time but not proof read by me. NOTE: code tags are added via # button on posting toolbar.

The fact that the prior sub is now a function hasn't changed anything - it still does the same as before, which is nothing. I think OP is copying code from other sources with pretty much zero idea of what it does. It's OK; we all had to start somewhere but maybe some research into subs and functions would be a good idea first. Kind of like learning to crawl before trying to run a marathon.

Option Compare Database
Option Explicit

Public Function GetUserName() As String
Dim User As String, Username As String, Name As String

Name = Username

End Function

'Then, in the btnLogin_Click event is the following code (much of this has been commented out currently)

Private Sub btnLogin_Click()
' Look into adding new tblDevelopers and add new if statement If rs.NoMatch to look at tblDevelopers to see if a match is found.
Dim rs As Recordset
Dim db As Database

Me.txtUserName.SetFocus

Set db = CurrentDb
Set rs = db.OpenRecordset("tblUsers", dbOpenSnapshot, dbReadOnly)
rs.FindFirst "UserName='" & Me.txtUserName & "'"

If rs.NoMatch = True Then
     Me.lblWrongUser.Visible = True
     Me.txtUserName.SetFocus
     Exit Sub
End If

Me.lblWrongUser.Visible = False
If rs!UserPassword <> Nz(Me.txtPassword) Then
     Me.lblWrongPassword.Visible = True
     Me.txtPassword.SetFocus
     Exit Sub
End If
Me.lblWrongPassword.Visible = False

If rs!UserType = 4 Then 'Checks to what user Priviledges User Has Developer Priviledges
     Dim prop As Property
     Call GetUserName
     User = txtUserName
     On Error GoTo SetProperty
     Set prop = CurrentDb.CreateProperty("AllowByPassKey", dbBoolean, False)
     CurrentDb.Properties.Append prop
     
SetProperty:
     If MsgBox("Would you like to turn on the bypass key?", vbYesNo, "Allow Bypass") = vbYes Then
          CurrentDb.Properties("AllowBypassKey") = True
          Else
          CurrentDb.Properties("AllowBypassKey") = False
     End If
End If

DoCmd.OpenForm "frmNorthPointMain"
DoCmd.Close acForm, "frmLoginScreen"

If rs!UserType = 1 Then 'Checks to what user Priviledges User Has, opens frmNavigation
     DoCmd.Close acForm, "frmNorthPointMain"
     User = txtUserName
     DoCmd.Close acForm, "frmLoginScreen"
End If

If rs!UserType = 2 Then 'Checks to what user Priviledges User Has, opens frmNavDataEntry
     CurrentDb.Properties("AllowBypassKey") = False
     DoCmd.OpenForm "frmNorthPointMain"
     User = txtUserName
     DoCmd.Close acForm, "frmLoginScreen"
End If

If rs!UserType = 3 Then 'Checks to what user Priviledges User Has, opens frmNavusers
CurrentDb.Properties("AllowBypassKey") = False
DoCmd.Close acForm, "frmNorthPointMain"
User = txtUserName
DoCmd.Close acForm, "frmLoginScreen"
End If

If rs!UserType = 5 Then 'Checks to what user Priviledges User Has, opens frmLabels-Users
     CurrentDb.Properties("AllowBypassKey") = False
     DoCmd.Close acForm, "frmNorthPointMain"
     User = txtUserName
     DoCmd.Close acForm, "frmLoginScreen"
End If

If rs!UserType = 7 Then 'Checks to what user Priviledges User Has, opens frmLabels-Users
     CurrentDb.Properties("AllowBypassKey") = True
     DoCmd.OpenForm "frmNorthPointMain"
     DoCmd.OpenForm "frmAssetTruck"
     DoCmd.OpenForm "frmEmployees"
     User = txtUserName
     DoCmd.Close acForm, "frmLoginScreen"
End If

End Sub

[June7]

Midway of post #2 in Case statement not working as Expected (accessforums.net) is at least one instance.

[Micron]

I only went back a year, figuring anyone could forget if no posts in that time. However, I guess that if the point isn't driven home by now it never will be. At a certain point I just don't read it.

[Welshgasman]

I only went back a year, figuring anyone could forget if no posts in that time. However, I guess that if the point isn't driven home by now it never will be. At a certain point I just don't read it.

With the signature

Dave

Learn Something new everyday. A day in which you learn nothing is a wasted day.

you'd think it would stick?

[Dave14867]

Ok I have attached a simple sample database. I want to populate the field on the frmTestForm with the user name entered on the login form. I obviously am missing something when trying to preserve for global use in the Public Functions Module the User Name.

Can someone please update the code or explain to me what I am doing incorrectly. I want to use this method throughout the database and once I see what I am doing wrong I will get it and be able to use the same method elsewhere.

Thanks in advance and I apologize for net getting it yet.

Passing Data Issue.zip

[June7]

Here's one way to do it:

Code in PublicFunctions module:

Option Compare Database
Option Explicit


Public UserType As Integer
Public User As String

Code behind frmLoginScreen:

Option Compare Database
Option Explicit


Private Sub btnLogin_Click()


' Look into adding new tblDevelopers and add new if statement If rs.NoMatch to look at tblDevelopers to see if a match is found.


'Public Function LoginName()
'Dim LoginUserName As String
'LoginUserName = txtUserName
'End Function


    Me.txtUserName.SetFocus


    Dim rs As Recordset
    Dim db As Database
    Dim LoginUserName As String
'    Public LoginUserName As String 'Commented out 9/19/22
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblUsers", dbOpenSnapshot, dbReadOnly)
    
    rs.FindFirst "UserName='" & Me.txtUserName & "'"
'    Call GetUserName 'function as constructed does nothing useful


    If rs.NoMatch = True Then
        Me.lblWrongUser.Visible = True
        Me.txtUserName.SetFocus
        Exit Sub
    End If
    Me.lblWrongUser.Visible = False
    
    If rs!UserPassword <> Nz(Me.txtPassword) Then
        Me.lblWrongPassword.Visible = True
        Me.txtPassword.SetFocus
        Exit Sub
    End If
    Me.lblWrongPassword.Visible = False


    If rs!UserType = 1 Then     'Checks to what user Priviledges User Has, opens frmNavigation
        User = Me.txtUserName
        DoCmd.OpenForm "frmTestForm"
        DoCmd.Close acForm, "frmLoginScreen"
    End If


End Sub

Code behind frmTestForm

Option Compare Database
Option Explicit


Private Sub Form_Open(Cancel As Integer)
Me.txtUserName = User
End Sub

[Welshgasman]

Ok I have attached a simple sample database. I want to populate the field on the frmTestForm with the user name entered on the login form. I obviously am missing something when trying to preserve for global use in the Public Functions Module the User Name.

Can someone please update the code or explain to me what I am doing incorrectly. I want to use this method throughout the database and once I see what I am doing wrong I will get it and be able to use the same method elsewhere.

Thanks in advance and I apologize for net getting it yet.

Passing Data Issue.zip

When I wanted to do the same thing, I used Tempvars.

Set them on Login and used them throughout the system.

[Dave14867]

June7,

I copied and pasted the code you suggested and I am still not getting the results I am looking for. What I want is the when frmTestForm opens, the txtUserName displayed is "User1". I made the default value of txtUserName=[User] and it doesn't grab it. I put stops in the code to try to debug it and it seems that when frmLoginScreen closes "User" is lost or cleared from memory.

[June7]

Don't use the brackets - [ ] - you will see I have not in my code. VBA variables are never enclosed in brackets.

Yes, VBA variables lose their value when unhandled run-time errors are thrown.