Re: Filtering records in a form
- From: cmiller <cmiller@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 31 Dec 2007 11:24:03 -0800
Again, please forgive me for being uneducated in ACCESS; I have attempted
what I believe you instructed 3 times starting with a fresh copy of my
current database and failed all three attempts. Maybe I am missing
something.
I have one table,TblMainData, with all the data.
I have another table, TblUserName, with all the usernames with their display
names.
I have one query, QrySelMainData, pulling data from TblMainData. I added
the field named: "Expr1: [ChangedBy]" and in the criteria of this field
entered: "[Forms]![FrmMyMenu]![HiddenName]"
I have a switchboard form that has one command button to open the data entry
form. In this form I have created a Label named "HiddenName". This field is
set to "Visible: No" and in the Open Event I have:
Me.HiddenName = Nz(DLookup("DisplayName","TblUserName","UserName =" &
Environ("UserName")),"ERROR")
If Me.HiddenName = "ERROR" Then MsgBox "UserName not found."
End If
I also have the form, FrmMainData, pulling fields from QrySelMainData. In
the BeforeUpdate event, I have:
Me.ChangedBy = Environ("UserName")
OKAY. That is me. Could someone please tell me what I have done wrong? To
add insult, I am being told by gurus who know ACCESS that this is a simple
task, but they don't have time for me.
"Pat Hartman" wrote:
Most applications open to a "menu" type form that lists options for users to.
choose from. That is the form I was referring to. you can use the built in
Switchboard Manager to create a switchboard type menu for you. The wizard
will guide you though the process.
In the StartUp options, you can select the name of your switchboard/menu
form as the form to open when the database opens. The autoexec macro is
another alternative but one which I never use any more.
"cmiller" <cmiller@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2E6F9804-7C86-4260-A62F-90DF2FB17266@xxxxxxxxxxxxxxxx
OKAY, Now that I have had time to review your response, I have a few
questions to clarify.
If I go with single form method, will I run into the error when two users
are simultaneously trying to enter a record at the same time?
Also, in your instructions "1. Add a field to your opening menu.", what
is
referred to as the opening menu? Is that like a message box that opens
and
asks for the user login?
Sorry for sounding so... unlearned in ACCESS, but I admit that just might
be the case.
"Pat Hartman" wrote:
You need to get rid of all your person specific forms and go with a
single
form. There are several ways to capture the ID of the person who opened
the
database. One of them is:
Me.ChangedBy = Environ("UserName")
Which would be placed in the form's BeforeUpdate event.
Since you probably don't really want the "UserName" but would rather have
a
nice human readable name, you would need to create a table that lists all
the "UserName" values and pairs them with the name you want to display.
This will allow you to create a query that selects records for a specific
user and use that query as the RecordSource for the single form.
1. Add a field to your opening menu.
2. Set its visible property to No.
3. In the Open event of the menu form place the user name in the hidden
field:
Me.HiddenName = Nz(DLookup("DisplayName","YourUserListTable", "UserName =
"
& Environ("UserName")),"ERROR")
If Me.HiddenName = "ERROR" Then
MsgBox "User name not found. Please update user table",vbokonly
'you
will have to reopen the menu after the user table has been updated with
the
new user or populate the HiddenName field from a different procedure
End If
In the query, add criteria to the ChangedBy column:
Where ChangedBy = Forms!frmMyMenu!HiddenName
"cmiller" <cmiller@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5EB99EE4-2C9B-48E7-8576-F7A6199C531C@xxxxxxxxxxxxxxxx
Greetings all,
I work at a company with an IT department, but they are so busy, they
do
not
have time for my trivial questions. I resort to the mass knowledge
available
to me by you wonderful people.
I have a database of Issues entered by my agents. All of my agents
have
their own data entry form ex. FrmJohnDoe, FrmJaneDoe, FrmJohnSmith.
Each
of
these forms automatically assign the agent name and date when the new
record
is created.
All of these forms were created from the same table, TblMainData, that
records each issue with a customer. To normalize the table, a look-up
reference has consolodated the agents onto its own table TblSpecialist.
What I would like to do is When JohnDoe opens his form, I would like
for
him
to see only those issues he had entered into the log, i.e. Where
fieldname
"Specialist"="John Doe".
I am using Access 2003. I have read online about using a query,
creating
a
macro, and using subforms. Since I am wanting this to be a constant,
everytime the form is opened, I also read the query method is not the
best
method. Please advise how this task might be accomplished best?
- References:
- Re: Filtering records in a form
- From: Pat Hartman
- Re: Filtering records in a form
- From: cmiller
- Re: Filtering records in a form
- From: Pat Hartman
- Re: Filtering records in a form
- Prev by Date: Re: Access 2007 Form "Not Responding"
- Next by Date: Re: Combo box and data
- Previous by thread: Re: Filtering records in a form
- Next by thread: RE: Filtering records in a form
- Index(es):
Relevant Pages
|
