Default Value Table

  • Thread starter Thread starter Lee T.
  • Start date Start date
L

Lee T.

I wish to make a table that the users of the database will
fill out selected criteria. For example they can set
their department and the specific address they want used
as a return address and so on. By having a table that
each department can "customize" and referring to this
table in the code for forms and reports, I would hope that
my code would not need to be changed each time a new
department uses the system.

However, I am not sure how to call the data from a
different table for input into my form or report. Any
help pointing me in the proper direction would be greatly
appreciated.


tia

LT
 
Lee,

To begin with, you wil need two tables: one associating users with
departments, and one holding the defaults by department. So, for each user
you can look up their department, and then look up the defaults for the
department. The benefit of the double table design is (a) you don't have to
store the same defaults as many times as the users in a department, and (b)
for a new user you only need to add their user name and department in the
first table, not all the defaults.
If you have implemented Access security, then you can identify users by
means of Application.CurrentUser. If not, a very convenient way is to use
their Windows logon name, returned by Environ("UserName"). In either case,
the rest is the same.

In order to get default values on a form, you would use the form's Current
event, with a bit of code like:

If Me.NewRecord = False Then Exit Sub
vUser = Environ("UserName") 'or Application.CurrentUser
vDept = DLookup("[fldDept]","tblUsers","[fldUser]='" & vUser & "'")
Me.txtSomething = DLookup("[fldSomehting]","tblDepartments","[fldDept]='" &
vDept & "'")
etc.

In a report, you would set a control's ControlSource property to:
= DLookup("[fldSomehting]","tblDepartments","[fldDept]='" &
DLookup("[fldDept]","tblUsers","[fldUser]='" & Environ("UserName") & "'") &
"'")
(or you can use Application.CurrentUser instead of Environ("UserName") in
the case of Access security)
Or, you could use a function in code, like:

Function get_somefield()
vUser = Environ("UserName") 'or Application.CurrentUser
vDept = DLookup("[fldDept]","tblUsers","[fldUser]='" & vUser & "'")
get_somefield = DLookup("[fldSomehting]","tblDepartments","[fldDept]='" &
vDept & "'")
End Function

and then set the ControlSource property of the control in the report to:
=get_somefield()

HTH,
Nikos
 
Nikos,

Thank you again for providing valuable insight to my
issue. I don't have time to try it right now but I will
try it over the weekend.

Thanks again!

Lee T.
-----Original Message-----
Lee,

To begin with, you wil need two tables: one associating users with
departments, and one holding the defaults by department. So, for each user
you can look up their department, and then look up the defaults for the
department. The benefit of the double table design is (a) you don't have to
store the same defaults as many times as the users in a department, and (b)
for a new user you only need to add their user name and department in the
first table, not all the defaults.
If you have implemented Access security, then you can identify users by
means of Application.CurrentUser. If not, a very convenient way is to use
their Windows logon name, returned by Environ ("UserName"). In either case,
the rest is the same.

In order to get default values on a form, you would use the form's Current
event, with a bit of code like:

If Me.NewRecord = False Then Exit Sub
vUser = Environ("UserName") 'or Application.CurrentUser
vDept = DLookup("[fldDept]","tblUsers","[fldUser]='" & vUser & "'")
Me.txtSomething = DLookup
Click to expand...
("[fldSomehting]","tblDepartments","[fldDept]='" &
vDept & "'")
etc.

In a report, you would set a control's ControlSource property to:
= DLookup("[fldSomehting]","tblDepartments","[fldDept]='" &
DLookup("[fldDept]","tblUsers","[fldUser]='" & Environ ("UserName") & "'") &
"'")
(or you can use Application.CurrentUser instead of Environ ("UserName") in
the case of Access security)
Or, you could use a function in code, like:

Function get_somefield()
vUser = Environ("UserName") 'or Application.CurrentUser
vDept = DLookup("[fldDept]","tblUsers","[fldUser]='" & vUser & "'")
get_somefield = DLookup
Click to expand...
("[fldSomehting]","tblDepartments","[fldDept]='" &
 
Back
Top