MS Access Tips

I have been creating a timesheet and employee locater for our office and had need for few access tips and have listed them here that I found helpful for my quick reference.

Field Level Permissions: There are several types field level permissions that you can set on MS Access. One good example that I found recently was on Allen Browne's site. As the creater states, it is not a true-user level permissions, but applies to the current form. It records who inputted and who edited last. The script is in the Modules "ajbFieldLevel".

Adding in Form: This adds five text boxes (Day1Status1Time - Day1Status5Time): Add the following in the "Control Source" of the Total Text Box: =Nz([DAY1STATUS1Time])+Nz([DAY1STATUS2Time])+Nz([DAY1STATUS3Time])+Nz([DAY1STATUS4Time])+Nz([DAY1STATUS5Time])

Dlookup Function This function is great to look up a function based on what you select in a combo box. This should be added in the after update event. This one particularly looks up based on the EMPLOYEEID.

Option Compare Database

Private Sub EMPLOYEEID_AfterUpdate()

On Error GoTo Err_EMPLOYEEID_AfterUpdate

Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "EMPLOYEEID=" & Me!EmployeeID

' Look up product's unit price and assign it to UnitPrice control.
Me!LOCATIONDay1 = DLookup("LOCATION", "tbl_EmployeeList", strFilter)
Me!PHONEDay1 = DLookup("PHONE", "tbl_EmployeeList", strFilter)
Me!EXTDay1 = DLookup("EXT", "tbl_EmployeeList", strFilter)
Me!DAY1STATUS1 = DLookup("STATUS", "tbl_EmployeeList", strFilter)
Me!TIMEINDay1 = DLookup("TIMEIN", "tbl_EmployeeList", strFilter)
Me!TIMEOUTDay1 = DLookup("TIMEOUT", "tbl_EmployeeList", strFilter)

Exit_EMPLOYEEID_AfterUpdate:
Exit Sub

Err_EMPLOYEEID_AfterUpdate:
MsgBox Err.Description
Resume Exit_EMPLOYEEID_AfterUpdate

End Sub