I have a Supervisors table linked to an Employees table in a one-to-many relationship.
Supervisor and employee records each have a field called WinLogon that holds what their Windows username should be.

When a user opens the db, I need some code that will read the user's username (Environ("username")) and then match that to their record in either the Supervisors table or the Employees table and then open the correct form depending on which table they were found in.

How can I do that?

You could use DCount to test for the existence of the name in the Supervisor table. If found open form A, else open form B.

Ahh, excellent.
I was thinking about it the wrong way.

Thanks!

Well, I always seem to have problems with DCount.
I added my username into the Supervisors table, but it keeps telling me that I'm just an employee.
Can anybody see what I'm doing wrong?


Public Function UserAutoExec()
Dim UserName As String
UserName = Environ("username")
If DCount("[strWinLogon]", "tblSupervisors", "[strWinLogon]" = "UserName") = 1 Then
If MsgBox("You're a supervisor", vbOKOnly) = vbOK Then
End If
Else
If MsgBox("You're just an employee", vbOKOnly) = vbOK Then
End If
End If
End Function


I've also tried:
... "[strWinLogon] = UserName")

And

... "[strWinLogon]" = UserName)

And


Public Function UserAutoExec()
Dim UserName As String
Dim IntX As Integer
UserName = Environ("username")
If (IntX = (DCount("[strWinLogon]", "tblSupervisors", "[strWinLogon]" = "UserName"))) = 1 Then
If MsgBox("You're a supervisor", vbOKOnly) = vbOK Then
End If
Else
If MsgBox("You're an employee", vbOKOnly) = vbOK Then
End If
End If
End Function

Try

If DCount("[strWinLogon]", "tblSupervisors", "[strWinLogon] = '" & UserName & "'") = 1 Then

Works like a charm!

Thanks!

No problem. I should have included this handy reference:

http://www.mvps.org/access/general/gen0018.htm

Added it to my bookmarks.
That website is quite a wealth of information!

Alright, One more thing. I have taken the code I have and added to it so that if I open the database or my supervisor does, It takes me to the management form.

If I leave the code as:
If UserName = "MyUsername" Then
it works fine.

But during startup, I keep getting an error that says Runtime error '13'; Type Mismatch.

Public Function UserAutoExec()
Dim UserName As String
UserName = Environ("username")
If UserName = "MyUsername" Or "MySupervisorsUserName" Then
DoCmd.OpenForm "frmManage"
Else
If DCount("[strWinLogon]", "tblSupervisors", "[strWinLogon] = '" & UserName & "'") = 1 Then
DoCmd.OpenForm "frmCurrentLeadSup"
Else
DoCmd.OpenForm "frmCurrentUser"
End If
End If
End Function

You have to repeat the value:

If UserName = "MyUsername" Or UserName = "SomeoneElsesUserName" Then

Dang you're fast.

So simple! That was kind-of a dumb question huh?

I wouldn't call it dumb. There are probably languages where your original syntax is perfectly valid.