Ms Access Default value on form load

below is a SQL which shows the logged on users.. i need a the value for the  UserID to be plced in a field on a form when form is loaded and only if there is a value if null then default value is blank

SELECT tblLogTimes.UserID, tblLogTimes.UserLoggedOn
FROM tblLogTimes
WHERE (((tblLogTimes.UserLoggedOn)=True));
Blueice13085Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Blueice13085Author Commented:
i tried to do this
Private Sub Form_Load()
Me.txtDispatcherLoggedIn = [qryLoggedOnUsers]![UserID]
End Sub

just get the #Name
0
danishaniCommented:
You might try this in an unbound Text Field on your Form, the controlsource will be:

= Nz(DLookup("[UserID]", "tblLogTimes", "[UserLoggedOn] = True", Null))

Hope this helps,
Daniel
0
peter57rCommented:
I find it hard to believe that this is what you want l but this will give you what you have asked for in the question.
Set the controlsource for txtDispatcherLoggedIn to

= nz(Dlookup("UserID", "tblLoggedTimes", "UserLoggedOn=True"), "")
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

danishaniCommented:
Oops, wrong closing bracket, try this:
=Nz(DLookUp("[UserID]","tblLogTimes","[UserLoggedOn] = True"),Null)
0
Eric ShermanAccountant/DeveloperCommented:
Will there be multiple users logged on in the table???


ET
0
Blueice13085Author Commented:
Thank you both...

Final code looks as below

Private Sub Form_Load()
Me.txtDispatcherLoggedIn = Nz(DLookup("[UserName]", "tblLogTimes", "[UserLoggedOn] = True"), Null)
If IsNull(Me.txtDispatcherLoggedIn) Then
Me.lstDailyCallLog.RowSource = "SELECT DriverID, DispatchDate, Dispatcher, DriverName, TruckNo FROM tblCallInputMain WHERE ((([DispatchDate]) Between [Forms]![Report]![txtStart] And [Forms]![Report]![txtEnd]));"
Else
Me.txtDispatcherLoggedIn = Nz(DLookup("[UserName]", "tblLogTimes", "[UserLoggedOn] = True"), Null)
Me.lstDailyCallLog.RowSource = "SELECT DriverID, DispatchDate, Dispatcher, DriverName, TruckNo FROM tblCallInputMain WHERE ((([DispatchDate]) Between [Forms]![Report]![txtStart] And [Forms]![Report]![txtEnd]) AND (([Dispatcher])=[Forms]![Report]![txtDispatcherLoggedIn]));"
End If
End Sub
0
Blueice13085Author Commented:
yes there could be, good idea... hmmm
0
Blueice13085Author Commented:
etsherman: Yes you are right, there could be more then one logged on at a time.....
0
Blueice13085Author Commented:
guess you would need to see the logon code for it to work all the way one sec
0
Blueice13085Author Commented:
Option Compare Database
Option Explicit

Public Type UserInfo
    UserID As Long
    UserName As String
    Password As String
    EncryptedPassword As String
    UserLevel As Long
    Active As Boolean
End Type

Public User As UserInfo

Public Function PerformEncryption(ByRef strPassword As String, ByVal boo As Boolean) As String
' boo = True then Encrypt, boo = False then Decrypt
Dim strCode As String, intCounter As Integer

    For intCounter = 1 To Len(strPassword)
        If intCounter Mod 2 = 0 Then
            If boo = True Then
                strCode = strCode + Chr(Asc(Mid(UCase(strPassword), intCounter, 1)) + 1)
            Else
                strCode = strCode + Chr(Asc(Mid(UCase(strPassword), intCounter, 1)) - 1)
            End If
        Else
            If boo = True Then
                strCode = strCode + Chr(Asc(Mid(UCase(strPassword), intCounter, 1)) + 2)
            Else
                strCode = strCode + Chr(Asc(Mid(UCase(strPassword), intCounter, 1)) - 2)
            End If
        End If
    Next intCounter

    PerformEncryption = strCode

End Function

Function fLogUser(ldet As Integer)
On Error GoTo Err_LogUser

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblLogTimes", dbOpenDynaset)

Select Case ldet

Case 0 'Log Off

    With rst
        .FindLast "UserID =  " & User.UserID
        .Edit
        !LoggedOff = Now()
        !UserLoggedOn = False
        .Update
        .Close
    End With
   
Case 1
   
    With rst
        .AddNew
        ![UserID] = User.UserID
        ![UserName] = User.UserName
        ![UserLoggedOn] = True
        .Update
        .Close
    End With

End Select

Set rst = Nothing

Exit_LogUser:
    Exit Function

Err_LogUser:
    Resume Exit_LogUser

End Function
0
Blueice13085Author Commented:
Option Compare Database
Option Explicit

Public LogAttempts As Long

Const Message1 = "  Please enter User Name and Password.     "
Const Message2 = "LogOn Details Incorrect....  User Not Found.           "
Const Title1 = " LogOn Error"

Private Sub cmdCancel_Click()
On Error GoTo Err_cmdCancel_Click

    'DoCmd.Quit
    DoCmd.Close acForm, Me.Name
   
Exit_cmdCancel_Click:
    Exit Sub

Err_cmdCancel_Click:
    MsgBox Err.Description, , " LogOn Error"
    Resume Exit_cmdCancel_Click
   
End Sub

Private Sub cmdChangePassword_Click()
On Error GoTo Err_cmdChangePassword_Click

    DoCmd.OpenForm "frmUpdatePassword", , , "[UserID] = " & Me.txtUserName.Column(1), , acDialog

Exit_cmdChangePassword_Click:
    Exit Sub

Err_cmdChangePassword_Click:
    MsgBox Err.Description, , " Edit User Error"
    Resume Exit_cmdChangePassword_Click
End Sub

Private Sub cmdLogOn_Click()
On Error GoTo Err_cmdLogOn_Click
       
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
       
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("tblUserDetails", dbOpenDynaset)
   
If IsNull(Me.txtUserName) Then
   
    PopUpMsgBox Message1, 2, Title1, vbCritical
    Me.txtUserName.SetFocus
    Call CheckLogAttempts
   
ElseIf IsNull(Me.txtPassword) Then

    PopUpMsgBox Message1, 2, Title1, vbCritical
    Me.txtPassword.SetFocus
    Call CheckLogAttempts
   
ElseIf Me.txtPassword = "Wizard" Then   'Admin Password - If you want to change this password, you need to change it here, in the frmAddUser form and in the frmUpdatePassword form.
    DoCmd.OpenForm "frmUserDetails"
    User.UserLevel = 1
    DoCmd.OpenForm "Report"
    DoCmd.Close acForm, Me.Name
   
   
ElseIf Me.txtPassword = "0122" Then  'Emergency Password - If you want to change this password, you need to change it here, in the frmAddUser form and in the frmUpdatePassword form.
    DoCmd.OpenForm "frmUserDetails"
    DoCmd.Close acForm, Me.Name

Else
    User.Password = PerformEncryption(Me.txtPassword, True)
    rst.FindFirst "Password = '" & User.Password & "'" & " And UserName = '" & Me.txtUserName & "'"
   
    If rst.NoMatch Then
        Call ErrorMessage
    Else
        If PerformEncryption(Me.txtPassword, True) = User.Password Then
            With User
                .UserName = Me.txtUserName
                .UserLevel = rst.Fields("UserLevelID")
                .UserID = rst.Fields("UserID")
                .Active = rst.Fields("Active")
            End With
           
            rst.Close
        If User.Active = False Then
            MsgBox " Your User account has been suspended. " & Chr(13) & "Please contact your System Administrator               " & Chr(13) & "          to have your account reset.", vbExclamation, " Log On Error"
            Me.txtPassword = ""
            Me.txtUserName = ""
            Me.txtUserName.SetFocus
            User.Password = ""
        Else
            DoCmd.OpenForm "frmHidden", , , , , acHidden
            Call fLogUser(1)
            DoCmd.OpenForm "Report"
            DoCmd.Close acForm, Me.Name
        End If
       
        Else
           
            Call ErrorMessage
       
        End If
    End If
End If

Exit_cmdLogOn_Click:
    Exit Sub

Err_cmdLogOn_Click:
    Select Case Err.Number
        Case 94
            Call ErrorMessage
            Resume Exit_cmdLogOn_Click
        Case Else
            MsgBox Err.Description, vbCritical, " Log On Error"
            Resume Exit_cmdLogOn_Click
        End Select

End Sub



Private Sub Form_Load()
On Error GoTo Err_Form_Load
   
    Me.txtUserName = fOSUserName
    LogAttempts = 0
   
Exit_Form_Load:
    Exit Sub

Err_Form_Load:
    MsgBox Err.Description, , " LogOn Error"
    Resume Exit_Form_Load

End Sub

Function ErrorMessage()
On Error Resume Next

    PopUpMsgBox Message2, 2, Title1, vbCritical
    Me.txtPassword = ""
    Me.txtUserName = ""
    Me.txtUserName.SetFocus
    User.Password = ""
    Call CheckLogAttempts
   
End Function

Private Sub CheckLogAttempts()

If LogAttempts >= 2 Then
    MsgBox "     It appears you are having trouble logging on.    " & Chr(13) & "Please contact your System Administrator for assistance.       ", vbCritical, Title1
Else
    LogAttempts = LogAttempts + 1
End If

End Sub





Private Sub LogOnImage_DblClick(Cancel As Integer)
Dim strPasswd

   strPasswd = UCase(InputBox("Please Enter Admin Password", "Re-Link Tables is Restricted"))

   'Check to see if there is any entry made to input box, or if
   'cancel button is pressed. If no entry made then exit sub.

   If strPasswd = "" Or strPasswd = Empty Then
       MsgBox "No Password Provided", vbInformation, "Password Required"
       Exit Sub
   End If

   'If correct password is entered unlock Re-Link Tables
   'If incorrect password entered give message and exit sub

   If strPasswd = "JAX" Then
       DoCmd.OpenForm "frmRelinkTables"
   Else
       MsgBox "Sorry, you do not have access to Re-Link tables", vbOKOnly, "Password Incorrect!"
       Exit Sub
   End If
End Sub

Private Sub txtUserName_GotFocus()
Me.Requery
End Sub
0
Eric ShermanAccountant/DeveloperCommented:
<<<<Me.txtDispatcherLoggedIn = Nz(DLookup("[UserName]", "tblLogTimes", "[UserLoggedOn] = True"), Null)>>>>>

What happens if more than one user is logged on???

Seems like you would need this additional criteria ....

Me.txtDispatcherLoggedIn = Nz(DLookup("[UserName]", "tblLogTimes", "[UserName]='?????'" & " [UserLoggedOn] = True"), Null)

ET

0
Blueice13085Author Commented:
well, what i could do is.... make my database split, and not spilt the tblLogTimes, so this would really only show one logged in at a time, other then that i am lost on how to make it work other wise!
0
Blueice13085Author Commented:
unless there is a way to change my logone code?, but other then that i have no idea!
0
Blueice13085Author Commented:
well how can i show it otherwise? like username= user that sigend on from that computer ?
0
Eric ShermanAccountant/DeveloperCommented:
You could do one of the following ....

1.) Store the User Name to a global variable and add it to the DLookup critera as shown below.

Me.txtDispatcherLoggedIn = Nz(DLookup("[UserName]", "tblLogTimes", "[UserName]='" & strGloabalVariable & "' AND [UserLoggedOn] = True"), Null)

2.) Hold the user login form open (minimize and make it invisible) and get the user name from this form to use in the criteria.  Then close the login form once the main form loads.

("[UserName]", "tblLogTimes", "[UserName]='" & Forms!YourFormName!UserNameControl & "' AND [UserLoggedOn] = True"), Null)

ET




0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Blueice13085Author Commented:
well, i do have a form that opens in hidden mode!, i can make a blank txt box? make that called txtLoggedInAs, let me try that see if that works!
0
Blueice13085Author Commented:
when you talk about this
1.) Store the User Name to a global variable and add it to the DLookup critera as shown below.

Me.txtDispatcherLoggedIn = Nz(DLookup("[UserName]", "tblLogTimes", "[UserName]='" & strGloabalVariable & "' AND [UserLoggedOn] = True"), Null)

what do you mean>?
0
Eric ShermanAccountant/DeveloperCommented:
<<< ....what do you mean>?  >>>>

When the user enters their name on your form, you can store the value in the name textbox to a global variable for use in your DLookup function.

1.) To declare a Global variable ....  In a VBA Module in the Declarations section at the top you would enter ...

Global strUserName As String

2.) When the user logs in you would populate strUserName ...

strUserName = Forms!YourFormName!YourUserNameTextControl

3.) In your Dlookup you would use this variable .... Now you know which user is logging in and which one to lookup in your table.

Me.txtDispatcherLoggedIn = Nz(DLookup("[UserName]", "tblLogTimes", "[UserName]='" & strUserName & "' AND [UserLoggedOn] = True"), Null)


Or, the other option is to just pull the user name field from your hidden login form.

Either way should work.

ET


0
Blueice13085Author Commented:
Alright so i make a new VBA Mod call is basGlobals.. put
Global strUserName As String
 then save...
 i then dont understand where
strUserName = Forms!YourFormName!YourUserNameTextControl
needs to go
0
Eric ShermanAccountant/DeveloperCommented:
<<<<< .... i then dont understand where
strUserName = Forms!YourFormName!YourUserNameTextControl
needs to go >>>>>

I you have a login form that means the user must enter his/her user name and password then click a button to logon, correct???

Use the logon button's OnClick event to populate your Global Variable.

Private Sub UserLogonContinueButton_Click()
     strUserName = Forms!YourFormName!YourUserNameTextControl
End Sub

ET
 
0
Blueice13085Author Commented:
Mine with the form name and txt control would look like this

strUserName = Forms!frmLogOn!tstUserName
0
Blueice13085Author Commented:
so on click it would be strUserName = Me.txtUserName??
0
Eric ShermanAccountant/DeveloperCommented:
Yes, either one will work.

ET
0
Blueice13085Author Commented:
problem if i have

Private Sub Form_Load()
Me.txtDispatcherLoggedIn = StrUserName
End Sub

i get compile error on StrUserName
0
Blueice13085Author Commented:
Thank you all for the help on this, really worked out great!
0
Eric ShermanAccountant/DeveloperCommented:
Can you upload a sample of your db???  I'm about to shut down for the Holiday.

It's really not that complicated if the steps previously discussed are followed.

When you pouplate the Global variable strUserName, you want to use that variable in your DLookup function as criteria.

I need to see exactly how you have thins set up.

ET
0
Eric ShermanAccountant/DeveloperCommented:
Ok, seems like you got it working ... Glad I could help.

ET
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.