Revisiting: Filtering Records in a 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.

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" wrote in message
...
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?

Well I see at least two potential issues here.

First, is there an actual field called "ChangedBy" in the recordsource of
the query? (The fact that it has been aliased as "Expr1" seems to indicate
that there is not).

Second, if the "HiddenName" control you added to the form is a label, that
just won't work, as a label has no value. You need to change the label to a
text box.

BTW: There's a function to retrieve the network user name of the currently
logged in user at the following URL ...

http://www.mvps.org/access/api/api0008.htm

Using that function, and assuming your "tblMainData" actually has a
"ChangedBy" field, a query to retrieve only records changed by the currently
logged in user would look like this ...

SELECT * FROM tblMainData WHERE ChangedBy = '" & fOSUserName() & "'"

--
Brendan Reynolds

"cmiller" wrote in message
...
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.

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" wrote in message
...
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?

Thank you for your response.

Checking my database you were correct that "ChangedBy" was not a field in
the recordsource table. I have added it.

The "HiddenName label" on my switchboard is indeed a textbox and not a label
and it has a text box name of "Hidden Name"

now when I try to open the switchboard form, I get the following error:

"Microsoft Office Access can't find the Macro 'Me.'

The Macro (or its macro group) doesn't exist, or the macro is new but hasn't
been saved."

This is foreign to me and I have no idea what this is saying to me. Could
you please elaborate.

"Brendan Reynolds" wrote:


Well I see at least two potential issues here.

First, is there an actual field called "ChangedBy" in the recordsource of
the query? (The fact that it has been aliased as "Expr1" seems to indicate
that there is not).

Second, if the "HiddenName" control you added to the form is a label, that
just won't work, as a label has no value. You need to change the label to a
text box.

BTW: There's a function to retrieve the network user name of the currently
logged in user at the following URL ...

http://www.mvps.org/access/api/api0008.htm

Using that function, and assuming your "tblMainData" actually has a
"ChangedBy" field, a query to retrieve only records changed by the currently
logged in user would look like this ...

SELECT * FROM tblMainData WHERE ChangedBy = '" & fOSUserName() & "'"

--
Brendan Reynolds

"cmiller" wrote in message
...
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.

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" wrote in message
...
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" wrote in message
...
snip
now when I try to open the switchboard form, I get the following error:

"Microsoft Office Access can't find the Macro 'Me.'

The Macro (or its macro group) doesn't exist, or the macro is new but
hasn't
been saved."

Most likely you have tried to use the keyword "Me" in an expression in the
one of the properties of the form or one of the form controls. You can't do
that, the keyword "Me" can only be used in VBA code. Possibly you may have
tried to enter some of the code that was suggested to you earlier directly
in the "On Open" property of the form? That won't work, you have to choose
"[Event Procedure]" in the "On Open" property and then enter the code in the
VBA event procedure. When you click in the "On Open" property in the
Properties Window, notice the two buttons that appear to the right of the
field? Click the first one, with the downward pointing arrow, to choose
"[Event Procedure]", then click the second one, with the ellipsis, to open
the event procedure in the VBA editor.

--
Brendan Reynolds