Which is the best approach to identify the current user in MS Access? (1 Viewer)

Alhakeem1977

Registered User.
Local time
Today, 15:40
Joined
Jun 24, 2017
Messages
308
Hi All,
I am using the below code to identify the current user in the main form [NavigationForm] but I realized that the users are facing a little delay while launching the system.

I am using in my code the DLookup function on Open event to achieve that, I heard about the Recordset and TempVars, I do not know how to use them if they are faster than DLookup.

I would be grateful if someone can guide me to use the best approach to achieve that.

Here is my code:

Code:
Private Sub Form_Open(Cancel As Integer)
On Error Resume Next
DoCmd.OpenForm "frmLogo", acNormal
DoCmd.Maximize
DoCmd.OpenForm "LoginForm", acNormal
DoCmd.ShowToolbar "ribbon", acToolbarNo
'****************************************
Me.txtUserID = Environ("Username")
Me.txtDeptID = DLookup("ID", "qryUserLog_Dept", "[UserLogin] = '" & Environ("UserName") & "'")
Me.txtUserName = DLookup("UserName", "tblUser", "[UserLogin]='" & Environ("UserName") & "'")
Me.txtID = DLookup("UserID", "tblUser", "[UserLogin]='" & Environ("UserName") & "'") 'User ID Number
Me.txtLastName = DLookup("LastName", "tblUser", "[UserLogin]='" & Environ("UserName") & "'")
Me.txtDeptShN = DLookup("DeptShName", "qryUserLog_Dept", "[UserLogin] = '" & Environ("UserName") & "'")
End Sub

Thanks in advance!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:40
Joined
Oct 29, 2018
Messages
21,357
Hi. Since you're trying to retrieve multiple information for the same record in a table, perhaps you can just use a query or a recordset to do it (depending on what you intend to do with the information).
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:40
Joined
Aug 30, 2003
Messages
36,118
I agree, and since you said you were unfamiliar, here's an example. This demonstrating something different but includes the recordset code you'd use:

 

onur_can

Active member
Local time
Today, 05:40
Joined
Oct 4, 2015
Messages
180
You have 3 alternatives for this situation. The first is the code you have applied, The second is to write a DLookup command directly to the control source of the Text boxes, and the third is to open a set of records and check the records. Whichever of these is fast, VBA code you have applied.
 

Alhakeem1977

Registered User.
Local time
Today, 15:40
Joined
Jun 24, 2017
Messages
308
Thanks to all of you for the prompt responses.
Is it fine like this? Or need to be redisgned to make it faster by the record set approach (I read the DAO is faster than DLookup)? But could you please help me to retrieve data from two tables with one record set statement? Because I tried to do that it was all garbage. 😷

Thank you in advance!
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:40
Joined
Aug 30, 2003
Messages
36,118
To do it with one recordset you'd use SQL that pulled from both tables. Hard to be more specific without knowing your structure or seeing a sample db.
 

Alhakeem1977

Registered User.
Local time
Today, 15:40
Joined
Jun 24, 2017
Messages
308
To do it with one recordset you'd use SQL that pulled from both tables. Hard to be more specific without knowing your structure or seeing a sample db.

Please find attached my db, when you open it for the first time you must type your machine's User Name in the UserLogin field at Active Users form.

You will find the code at the NavigationForm in the on Open event.

Thanks in advance!
 

Attachments

  • test db.accdb
    2.6 MB · Views: 369

Minty

AWF VIP
Local time
Today, 12:40
Joined
Jul 26, 2013
Messages
10,354
I haven't looked at your db, but I'd be very surprised if 5 DLookups were causing slowness unless the query you are using as a source for a couple of them is complicated , have you tried just substituting a hard coded value to see if there is a noticeable improvement in the loading time?
 

Alhakeem1977

Registered User.
Local time
Today, 15:40
Joined
Jun 24, 2017
Messages
308
I haven't looked at your db, but I'd be very surprised if 5 DLookups were causing slowness unless the query you are using as a source for a couple of them is complicated , have you tried just substituting a hard coded value to see if there is a noticeable improvement in the loading time?
Maybe there is something else that's why I uploaded my db as per pbaldy recommend to convert the code to SQL. Thank you!
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:40
Joined
Aug 30, 2003
Messages
36,118
For me it loads so fast I'm not sure testing other methods will be meaningful. How long does it take for you? I've got a conference call shortly so I may not have time to look at it until this afternoon (10am here now).
 

Alhakeem1977

Registered User.
Local time
Today, 15:40
Joined
Jun 24, 2017
Messages
308
For me it loads so fast I'm not sure testing other methods will be meaningful. How long does it take for you? I've got a conference call shortly so I may not have time to look at it until this afternoon (10am here now).
I see, thank you for that, for me it not delys, but as I stated above it delays with some users which they have the same new machines like me, I am wondering why it's happening with some users I thought maybe because of the DLookup so that I can use another method.
May it could be due to another reason other than the DLookup?

You all are expert on Access and you know all the circumstances, so far as you confident and your
recommendation I will keep on using the DLookup. Thank you again!
 

Users who are viewing this thread

Top Bottom