Filter Form using fOSUserName()

[cclark9589]

Time and Billing db using Access 2007

I'm trying to filter the time card form using fosusername() so that time cards for the logged in user are the only one's visible. I'm stuck because I don't know squat.

I created tblUserNames: UserID (pk), UserName, NetName, & AudID. NetName is the network username and AudID is the pk from tblEmployees and the fk on the Time Card form. I created a Login form with one text box with =fOSUserName() as the control source and one button that opens the switchboard. When the button is clicked, Login form is minimized. I got the idea of this from a previous post but can't seem to get it to work. My db opens to Login and it seems to work but after that I am totally lost. In the On Load event I have "Call fOSUserName() in a private sub.

On the Time Card form I have "EmpNameCombo=fOSUserName()" in the Filter property. In form On Load event I have the following code:

Private Sub Form_Load()
'Me.Filter = "[EmpNameCombo]='" & fOSUserName() & "'"
'Me.Filter = "EmpNameCombo = fOSUserName()"
'Me.FilterOn = True
DoCmd.GoToRecord , , acLast
End Sub

The first 3 lines have been remmed out because they haven't worked.

How do I get this to work and where exactly do I place the code?

Not sure if it matters but the Time Card uses 3 sub-forms for entering billable hours, expenses and non-chargeable hours.

Here is the code I have for fOSUserName() in module FindLoginName:

Option Compare Database
Option Explicit
'******************** Code Start **************************
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function
'******************** Code End **************************

All help is appreciated, just need it in plain English due to thick skull.

 

[boblarson]

You are trying to filter on a nonexistent field (it would seem). You need:

Me.Filter = "[COLOR="Red"][NetName][/COLOR]='" & fOSUserName() & "'"
Me.FilterOn = True

 

[cclark9589]

I put that code in the Form_Load() event. When I open the time card I get a popup window to enter a parameter value for NetName. If I enter "Charlie" for the NetName a debug window comes up saying the acLast isn't available now. Click on End and it takes me to the second to last time card entry but all time cards are available as well.

I think what I'm wanting to do is compare the AudID in the tblUserNames associated with the NetName to the AudID fk on the time card that is in the control EmpNameCombo. My thought was that only those time cards where the AudID match would show.

Am I wrong? If not, where might I be messing this up?

 

[boblarson]

I put that code in the Form_Load() event. When I open the time card I get a popup window to enter a parameter value for NetName. If I enter "Charlie" for the NetName a debug window comes up saying the acLast isn't available now. Click on End and it takes me to the second to last time card entry but all time cards are available as well.

I think what I'm wanting to do is compare the AudID in the tblUserNames associated with the NetName to the AudID fk on the time card that is in the control EmpNameCombo. My thought was that only those time cards where the AudID match would show.

Am I wrong? If not, where might I be messing this up?

Since I can't see what fields are in your query underlying the form, I'm not sure. You can filter by any fields, but fOSUsername is the network login, so it needs to be looking at the field that has that exact name in it.

 

[cclark9589]

Not quite sure what you are saying here. Are you saying that the field NetName, which is in tblUserNames, needs to be in JobHrsSQ which is the query underlying the TimeHrsSfrm?

 

[boblarson]

What I'm saying, is whatever field you want to filter by has to be in the form's recordsource. So, if you are going to filter on the network user name using fOSUsername then you need to have that field available in the form's recordsource.

 

[cclark9589]

What I'm trying to filter by is the AudID which is part of the recordsource for the form and subforms. AudID is also a field in the respective tables and associated queries.

There was an earlier post in another thread suggesting to set up a user table that has the network username and AudID and to filter the time card based upon the username obtained with fOSUserName.

I think what I'm failing to see is how to compare the AudID associated with the network username (NetName) stored in the tblUserNames and the AudID in the EmpNameCombo control on the time card form.

I know in the end it will all seem so easy but being one of those users who programs only when absolutely necessary, none of it seems very easy at all. Access isn't my bag. It is a necessary evil I've been trying to learn in order to make life at work a bit easier. Figuring this out will help in that I won't have fix errors caused by someone entering their time on someone else's time card.

 

[boblarson]

Okay, I think this would be the one you want:

Dim lngAudID As Long
lngAudID = Nz(DLookup("AudID", "tblUserNames", "[NetName]=" & Chr(34) & fOSUserName & Chr(34)),0)

If lngAudID <> 0 Then
   Me.Filter = "[AudID]=" & lngAudID 
   Me.FilterOn = True
End If

 

[cclark9589]

That, my good man, appears to have done the trick!

Can you explain to me just what is happening in the code? Dim lngAudID? Chr(34)?

Here's another question; as the administrator for this database is there some method, maybe some sort of elseif statement, that wouldn't turn the filter on?

Would this work?

Dim lngAudID As Long
lngAudID = Nz(DLookup("AudID", "tblUserNames", "[NetName]=" & _
Chr(34) & fOSUserName & Chr(34)), 0)

If lngAudID = 1 Then
Me.FilterOn = False

ElseIf lngAudID <> 0 Then
Me.Filter = "[AudID]=" & lngAudID
Me.FilterOn = True
End If

As I said, now it looks so easy.

 

[boblarson]

I'll explain it. And to turn it off you just use

Me.Filter = ""
Me.FilterOn = False

do not use lngAudID = 1 because there might be an ID of 1. I used the NZ function in the DLookup to make sure we don't get an error if there is a null returned for the name sent. So, if the name comes back we set the filter and if lngAudID = 0 we don't.

The Chr(34) is simply a double quote. I like doing that instead of using the single quote to surround strings because the string just might have a single quote in it. Probably not in this case, but sometimes like if you are looking for a company name like BARNEY'S BOOTS it would throw an error if you surrounded the text with single quotes. You could use three double quotes instead but I find that to be too much.

As for the Dim lngAudID I just create a variable for assigning the AudID to. You don't need to do it but it is habit for me to use a variable. So, to give you a final product it would be:

Dim lngAudID As Long
lngAudID = Nz(DLookup("AudID", "tblUserNames", "[NetName]=" & Chr(34) & fOSUserName & Chr(34)),0)

If lngAudID <> 0 Then
   Me.Filter = "[AudID]=" & lngAudID 
   Me.FilterOn = True
Else
   Me.Filter = ""
   Me.FilterOn = False
End If