**RecordSource** (1 Viewer)

[MS_Access_Amature]

I am trying to set a record source to view only one record. For example: When a user logs in I want the form to only show that users record.

I have the following on the log in form which is called UserAccess.

DoCmd.Close acForm, "UserAccess", acSaveNo
DoCmd.OpenForm "EmployeesInfo"

Forms!EmployeesInfo.RecordSource = "I DONT KNOW WHAT TO PUT HERE"

I believe is something like SELECT [First Name] FROM EmployeesTable WHERE ????

Thats where i get stuck

 

[jdraw]

What are the fields in your Employees Table?

 

[boblarson]

Is this going to be the record source for each employee and not change? If so, just create a query and in the criteria you can use either the

Environ("username")

or

fOSUsername

from here if your username in the table matches with their domain login name.

 

[MS_Access_Amature]

No, this record source is only going to be for the employee who signed in. I was switching it up until it worked. It's working but when the EmployeeInfo loads it gives me like a little message box with the title "Enter Parameter Value" Then the message is UserAccess.Username then it give me a textbox to enter the value.

My code is

Forms!EmployeesInfo.RecordSource = "SELECT * FROM Employees WHERE Employees.[FirstName] = UserAccess.UserName"

So when that enter parameter value pops up i put the username of the person who signed in and it works. How do i get rid of this pop up box??

 

[boblarson]

No, this record source is only going to be for the employee who signed in.

Yes, and that is fine. But there is no need to change the recordsource through code. Just set the recordsource to the SQL string.

My code is

Forms!EmployeesInfo.RecordSource = "SELECT * FROM Employees WHERE Employees.[FirstName] = UserAccess.UserName"

So when that enter parameter value pops up i put the username of the person who signed in and it works. How do i get rid of this pop up box??

Your query should be:

"SELECT * FROM Employees WHERE Employees.[FirstName] = " & Chr(34) & [Forms]![UserAccess]![UserName] & Chr(34)

 

[MS_Access_Amature]

I didn't understand how to do it that way. I tried this but it gives me a few errors like undefined funciton Employees.AccessOption.Column in expression...Too few parameters. Expected 1. .... and object variable or with block variable not set...Can someone let me know whats wrong please.....What i'm doing is this. On the switchboard the user click his name and hits the view my info button and it takes him to a sign in form (UserAccess) that varifies his username and password. When he is allowed access the EmployeeInfo form opens. If he is an employee then he can only view his info so i set the recordsource to view just him. If he is an administrator he is allow to view everybody. In my Employees table i have a field named AccessOption which is a combobox which is a value list and it lets you either choose employee or administrator. Can someone please give me a hand. I appreciate it and thanks in advance.

Private Sub Form_Open(Cancel As Integer)
On Error GoTo MyErrorControl

'If the user that signed in has employee access do this

If DLookup("[AccessOption]", "Employees", "Employees.AccessOption.Column(1)") Then

Dim UserDB As Database, UserRS As Recordset
Set UserDB = CurrentDb
Set UserRS = UserDB.OpenRecordset("SELECT * FROM Employees WHERE Employees.[First Name] = Forms!UserAccess.UserName")
Me.ButtonFirstRecord.Enabled = False
Me.ButtonPreviousRecord.Enabled = False
Me.ButtonNextRecord.Enabled = False
Me.ButtonLastRecord.Enabled = False

'If the user that signed in has administrator access do this

ElseIf DLookup("[AccessOption]", "Employees", "Employees.AccessOption.Column(2)") Then
Dim DB As Database, RS As Recordset
Set DB = CurrentDb
Set RS = DB.OpenRecordset("SELECT * FROM Employees")
RecordCount.Value = 0
RS.MoveLast
RS.MoveFirst
RecordCount.Value = RS.RecordCount
RecordNumber.Value = 1
ButtonFirstRecord.Enabled = False
ButtonPreviousRecord.Enabled = False

If RS.RecordCount < 2 Then
ButtonLastRecord = False
ButtonNextRecord = False
End If

End If

RS.Close
Set RS = Nothing
Set DB = Nothing
Exit Sub
MyErrorControl:
Select Case Err.Number
Case 0
Resume Next
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Form_BeforeUpdate ERROR"
Resume Next
End Select
End Sub

 

[DCrake]

For a start get rid of table level lookups there more trouble than they are worth. Create a table with the lookup values in it along with a primary key. Store this PK in the employee table as a foreign key (FK). Then use the dlookup and you will find it works.

 

[MS_Access_Amature]

so i have to make a new table with what? All the employees and their AccessOption?? But what happens when u create a new employee .. how do i add it to both tables? wouldn't that just be adding the same info in two tables??

 

[DCrake]

No You have your Employee table that holds all the things about the employee that usually remain static, such as their name, address, start date, etc.

you also will have a field to denote their status Employee or Administrator. In this field you would store say 1 for employee and 2 for Admin

Then you have a employee status table that holds the status types

TblEmpStatus
StatusId
StatusDesc

You add your fisrt 2 records

1
Employee
2
Admin

You then create a relationship between the StatusId in the TblEmpTable and the StatusId in the TblEmployees.
Then you TblEmpStatus becomes the source for the lookup table to determine the statusId

 

[MS_Access_Amature]

Ok. I made the table and the relationship. But now is giving me this error message saying Too Few Parameters. Expected 1...????

THis is the code:


Private Sub Form_Open(Cancel As Integer)
On Error GoTo MyErrorControl

'If the user that signed in has employee access do this

If DLookup("[StatusID]", "Employees") = 1 Then

Dim UserDB As Database, UserRS As Recordset
Set UserDB = CurrentDb
Set UserRS = UserDB.OpenRecordset("SELECT * FROM Employees WHERE Employees.[First Name] = Forms!UserAccess.UserName")
Me.ButtonFirstRecord.Enabled = False
Me.ButtonPreviousRecord.Enabled = False
Me.ButtonNextRecord.Enabled = False
Me.ButtonLastRecord.Enabled = False

'If the user that signed in has administrator access do this

ElseIf DLookup("[StatusID]", "Employees") = 2 Then

Dim DB As Database, RS As Recordset
Set DB = CurrentDb
Set RS = DB.OpenRecordset("SELECT * FROM Employees")
RecordCount.Value = 0
RS.MoveLast
RS.MoveFirst
RecordCount.Value = RS.RecordCount
RecordNumber.Value = 1
ButtonFirstRecord.Enabled = False
ButtonPreviousRecord.Enabled = False

If RS.RecordCount < 2 Then
ButtonLastRecord = False
ButtonNextRecord = False
End If

End If
RS.Close
Set RS = Nothing
Set DB = Nothing
Exit Sub
MyErrorControl:
Select Case Err.Number
Case 0
Resume Next
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Form_Open ERROR"
Resume Next
End Select
End Sub

 

[DCrake]

This needs to be changed to be

Set UserRS = UserDB.OpenRecordset("SELECT * FROM Employees WHERE First Name] = '" & Me.UserName & "'")

However what happens if you have 2 John's You should really be using the users primary key for the look up.

 

[boblarson]

This needs to be changed to be

Set UserRS = UserDB.OpenRecordset("SELECT * FROM Employees WHERE First Name] = '" & Me.UserName & "'")

Seems to be missing 1 square bracket

Set UserRS = UserDB.OpenRecordset("SELECT * FROM Employees WHERE [First Name] = '" & Me.UserName & "'")

However what happens if you have 2 John's You should really be using the users primary key for the look up.

AMEN to that!

 

[DCrake]

Typo Bob. Dyslexia Rules KO

 

[boblarson]

Typo Bob.

I knew that, but I thought I had better point it out so the OP doesn't wonder why it is returning an error.