Filtering records in a form

[cmiller]

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?

 

[Pat Hartman]

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

 

[KARL DEWEY]

UNTESTED for opening different forms but works for a single form ----

One way is to create a Logon form that opens when Access opens (marco named
Autoexec). Have a ComboBox with After Update event to open their forms with
DoCmd.OpenForm [Forms]![Logon]![ComboBoxName], acNormal.

Have their forms source query have criteria for fieldname "Specialist" from
Logon form like this ---
[Forms]![Logon]![ComboBoxName]

The source for the ComboBox to have two fields -- form names & agents names.

 

[cmiller]

WOW. Both of those responses are quite a brain-full for me to process... but
most appreciated. I will get someone versed in ACCESS to assist me with
thier implementation. I really do appreciate both responses. Thank you.

UNTESTED for opening different forms but works for a single form ----

One way is to create a Logon form that opens when Access opens (marco named
Autoexec). Have a ComboBox with After Update event to open their forms with
DoCmd.OpenForm [Forms]![Logon]![ComboBoxName], acNormal.

Have their forms source query have criteria for fieldname "Specialist" from
Logon form like this ---
[Forms]![Logon]![ComboBoxName]

The source for the ComboBox to have two fields -- form names & agents names.

--
KARL DEWEY
Build a little - Test a little

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?

 

[cmiller]

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]

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]

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]

Do you have a column named "ChangedBy" in your table?
Do you have a control named "HiddenName" on your form?

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.

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.