MS Access Form where user name is selected from li

问题:

I am going to rephrase a previous question that I had posted:

I have built a form in access that allows users to enter in their hours worked by activity (i.e. transactional processing, project time, vacation etc.). The fields on the form are:

1) user name 2) start date 3) end date 4) activity 5) hours spent

Is there a way to have this form in such a way that when it is opened up by a user, a prompt automatically appears that forces you to select the user name from a list of users? Than once the user name is selected, all records entered through this form have the user name field already populated (unless the form is closed and reopened again)?

Based on the user name selection in the prompt, the toggling of records using the "Previous Record" and "Next Record" arrows should strictly pertain to the selected user name selected.

** this is my current form**

steps used to produce the solution mentioned below

1) delete the combo box called "User Name"
2) add an unbound hidden text box and call it txUN
3) add the VBA code loaded below but changed the txtusername reference to user_full_name as this is the name of the column in the table that should store these records
4) created the form below with a combo box for user names and called it cbousername
5) change the frm_sample reference to [Specialist - Timesheet Entry]

回答1:

OPTION, Open User Name form first:

Create a form like the below with a hidden textbox named txtUN.:

With the following code behind:

Private Sub Form_Current()
  If VBA.Strings.Len(txtUN & "") = 0 Then DoCmd.OpenForm "frm_UserName", acNormal, , , , acDialog
  If VBA.Strings.Len(txtUsername & "") = 0 Then txtUsername = txtUN
End Sub 

Then create a form like below. Name the drop down cboUserName.

With the following code behind:

Private Sub cboUserName_AfterUpdate()
   Forms!frm_Sample.txtUN = cboUserName
End Sub
Private Sub Form_Unload(Cancel As Integer)
  If (VBA.Strings.Len(cboUserName & "") = 0) Then
     MsgBox "You must supply a user name before proceeding.", , "ERROR: Missing Info."
     Cancel = True
  End If
End Sub

When you open frm_Sample it will check to see if txtUN has a value. If not it will open frm_UserName as a dialog. The user must select a user name and close the form. When navigating to another record if txtUsername is empty then it is assigned whatever txtUN contains.

回答2:

You can automatically pass the actual windows username to your form and with a table lookup gain your application related username.

Probably you already have a table (lets say table tblUsers with a field that contains your username, for example fldUsername) in your database that stores all valid usernames. Just add a column to this table (named fldDomainUser) that contains the domain AND/OR the username of the logged-in user.

Environ$("userdomain") + "\" + Environ$("username")

will return a string with your domain, a '\' and your actual username.

The function

dlookup ("[fldUsername]","[tblUsers]","[fldDomainUser]='"+cstr(Environ$("userdomain") + "\" + Environ$("username"))+"'")

will return the username.

Set your actual username in the form's before_insert event

Private Sub Form_BeforeInsert(Cancel As Integer)
   [ctrlUsername] = Environ$("userdomain") + "\" + Environ$("username")
End Sub

so that all new created records automatically contain your username.

Do not forget to filter your form or its Recordsource accordingly .