Help with username

stubby100

New Member
Joined
Dec 1, 2005
Messages
5
So I am ttempting to set up a database to manage budget forecasting and analysis for a large group of project managers. I have been able to use much of the information and examples on this forum to get to the point I am at, but I have encountered a stumbling block.

I have utilized a username and password managment utility that was posted on this site at an earlier date. Th problem I am having now is using that username/userid to drive values returned on forms for inputting budget forecasts.

The relevant Tables I have are:

tblUserDetails (includes: UserId, UserName, Password)
tblProjectDetails (includes: UserID, ProjectId, ProjectName)

How do I return a list of Projects that the current User is responsible for?

Thanks for the help
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
If you want to browse through the different users, the best way is to use a couple of forms. Do this:
1. Go to the Forms tab of your database. Create a new Autoform based on tblProjectDetails. Call the from fsubProjectDetails. Save it, then go to Design view. In the Properties for the form, set the Default View to Datasheet. Close and save the form.

2. Now create a new form called frmUserDetails, also by creating an Autoform. Save, go to Design view, and drag to enlarge the form. Now for the cool bit.

3. Restore the form so that you can see the database container. Press F11 to bring the container to the front. Locate the icon for fsubProjectDetails, drag it onto frmUserDetails, and let go. Immediately go to the subform's Properties. You should see 2 rows about Link Child Field and Link Master Field. Ensure that both rows have UserID.

4. Save, go to datasheet view and browse around. This setup should give you 2 main features -- When you browse a user, you see only their projects, and when you add a new ProjectID to the subform, that project is immediately attached to the user.

Denis
 

stubby100

New Member
Joined
Dec 1, 2005
Messages
5
Syd G-

Thanks for the response. One of the things that I was hoping to acomplish was to include a script that would run at the opening of the form to set the UserID in the frmUserDetails to the current user. This would then automatically return the list of projects that the User was responsible for in the sub form frmProjectDetails. I could then hide the frmUserDetails fields which would provide the benfit of protecting the project information from viewing and editing by other Users.

Any thoughts how to automatically set the UserId Field in the frmUserID to the current user.

Reminder-I am not using the work group user managment tools that are built in to Access/Microsoft Office.

Thanks again for your input.

Cheers,
MAtt
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
One way to automatically get the user ID assumes that you are using the Windows login as UserID.

Do this:

1. Create an UNBOUND textbox on the form. Call it txtUserID.
2. In the form's Current event, insert this code:
Code:
Me.[txtUserID] = Environ("username")
When the form loads, the Windows login will be placed in this textbox.
If you want to prevent modification, set Enabled and Locked properties to No

Note: Setting up the Current event.
1. In the form's Properties, click the Events tab then double-click the blank row for Current. You will see Event Procedure.
2. At the right of this row is the Builder (...) button. Click it to go to the code module.
3. In the blank row between Private Sub... and End Sub, place the above code

Denis
 

Watch MrExcel Video

Forum statistics

Threads
1,118,811
Messages
5,574,451
Members
412,595
Latest member
slim313
Top