users have own switchboard that opens with username and password- make forms filter

[Jgk]

I have a database that users log into using a username and password , then opens own switchboard form.

ElseIf username = "Joe Smith" And password = "JS11" Then

DoCmd.Close
DoCmd.OpenForm "JoeSmith"

My question is I have a command button of their switchboard that opens a form to add project time , is there a way to put code in to add new record and have their name already selected as that record.
I have been able to get a form to open with their name selected but it has record selectors enabled at bottom to view or enter time for other users.



Attachment 15879


I do not want them to be able to use record selectors at bottom to go to next employee but cannot figure out how to hide,disable after opening form , also If I hide the tab page General, the command to filter by name will not work. Any help would be appreciated. I have all my access 2007 books out and have been trying to find solution. Thanks,

[pbaldy]

For starters, I don't like having the user names and passwords hard-coded like that; makes the db very "un-dynamic". In any case, you should be able to leave the login form open but hidden so you can refer to it, or save the logged in user somehow. Then you can filter forms on that value so they only show the records for the logged in user (and you'd only need one form, not one per user). You can also refer to that hidden login form in the default value of a user textbox, which will put that value in for new records.

[Jgk]

Thanks for your reply, I have one login form and it unfortunately opens to each users own switchboard to limit what they can do and see. I do not like either but this got them up and running until I figure out exactly what you want to happen is what I want to happen, was not sure how to do. You are four steps ahead of me, that is what I wanted to do. However, If I could first figure out one than I could implement across the board. I have their own switchboard for now and it filters forms based on their name, but the form I pasted I cannot get the record selector to be disabled. Is there a quick fix to hide the record selector on form. I will be going to your method since I have created a new table with usernames and passwords in the hope of passing information from that table to one form to make it more dynamic.

[pbaldy]

Showing the record selectors is a property of the form. You can set it to No. Actually the record selector is the gray bar on the left; you want the navigation buttons property. I normally turn them both off.

[Jgk]

Got it thanks to you, I have navigation set to yes on form, by the way I have corrected also to eliminate individual switchboards and set up one form for users with permissions as you suggested and I wanted, just working thru code now. thanks again

[pbaldy]

Happy to help! I'm lazy; I'd rather maintain 1 form than many any day.

[Jgk]

As you stated about dynamic, I am trying to get this query to be used as a form filtered by username at login:

Private Sub Form_Load()
Dim iEdit As Integer

'populate the text boxes
Me.txtName = Environ("username")
Me.txtDate = Date
'set edit permissions
iEdit = Forms!frmmenu!txtLevel.Value
Select Case iEdit
Case Is < 2
Me.AllowEdits = False
Case Else
Me.AllowEdits = True
End Select
End Sub
Query based on table project hours

SELECT [Project Hours].ID, [Project Hours].Project, [Project Hours].Contractor, [Project Hours].DateWorked, [Project Hours].WorkDescription, [Project Hours].BillableHours, [Project Hours].ProjectType, [Project Hours].QBCode
FROM [Project Hours];
the field contractors is a lookup field in a table called contractors
I am trying to create query that limits this query based on txtname in frmmenu on load event.
Any help would be appreciated. Thanks

[pbaldy]

Along the lines of:

SELECT [Project Hours].ID, [Project Hours].Project, [Project Hours].Contractor, [Project Hours].DateWorked, [Project Hours].WorkDescription, [Project Hours].BillableHours, [Project Hours].ProjectType, [Project Hours].QBCode
FROM [Project Hours]
WHERE FieldName = Environ("username")

If the name is in a different table, you'd need to join that table into the SQL.

[pbaldy]

I got your db. You want the query to open to a new record? Or a form? Typically I'd expect a form to be used. Your contractor field is a lookup field, but I assume you want to find the user name and get the associated number?

[Jgk]

I was trying to create a form based on project hours opening to new record and use the username but was way over my head to make it more dynamic.

[Jgk]

I could not get to work without contractor being a lookup field on the form Contractor Details Orders, I wanted it to input user name in field contractor. Yes you are correct in what I was trying to do before I got in over my head.

[pbaldy]

Late afternoon here so I may be having brain cramps, but I'm confused (and part of it is that lookup fields drive me crazy...okay, crazier). Does adding this line to the frmMenu load event do what you want?

Me.txtContractor = DLookup("Contractor", "tblStaff", "Login='" & Forms!frmmenu!txtUser & "'")

That populates the contractor field on that form, which you could then use in the default value property of the textbox on your form.

[Jgk]

Yes that is what I want but I tried that in frmForm3 and frmMenu got run- time error 3075 syntax error missing operator in query expression 'login = Jay K".

[Jgk]

Private Sub Form_Load()
Me.txtName = Environ("username")
Me.txtDate = Date
Me.txtContractor = DLookup("Contractor", "tblstaff", "Login=" & Forms!frmForm3!txtName & "'")
End Sub


I tried the above for frmForm3 got run time error

frmMenu

I put code below and did not get error but did not return any results in frmMenu

Private Function GetPermission(sUser As String)
If (IsNothing(DLookup("Permissions", "tblStaff", "Login='" & Forms!frmMenu!txtUser & "'"))) Then
GetPermission = "ReadOnly"
Me.txtContractor = DLookup("Contractor", "tblstaff", "Login='" & Forms!frmMenu!txtUser & "'")

Else
GetPermission = DLookup("Permissions", "tblStaff", "Login='" & Forms!frmMenu!txtUser & "'")
End If
End Function

[Jgk]

I got it to work by changing tblstaff field contractor from a lookup field to text box and putting this code in frmMenu
Private Sub Form_Load()
Dim sPermit As String
Dim iAccess As Integer
Dim Ctl As Access.Control
Me.txtUser = Environ("username")
If IsNothing(Me.txtUser) Then
sPermit = "ReadOnly"
Else
sPermit = GetPermission(Me.txtUser)
End If
Select Case sPermit
Case "Edit"
iAccess = 2
Case "Admin"
iAccess = 3
Case Else
iAccess = 1
End Select
Me.txtLevel = iAccess
Me.lstMenu.Requery
Me.txtContractor = DLookup("Contractor", "tblstaff", "Login='" & Forms!frmMenu!txtUser & "'")
End Sub


Thanks, I will send what I did