Form, Query, and Updating Records

[pcstechnical.sfhs]

I have an employee profile form that I would like to filter by what
location the employee works out of.

The location is already determined based on a login I have in the
beginning. The location value is stored in a Query (call it query1).

When I get the records filtered to what I want, I still want to be
able to edit/update them.

Is this possible?

I have played around with the Adavanced Filter but haven't been
successful because when I filter the correct field in the employee
profile form, and put my EXPRESSION (I think this is part of the
problem) of what I want it filted by ([Query1]![Location]) it will ask
my, when I run the query, to give a value for Query1!Location. When I
do, it works. But, I can't have that popping up every time.

When I edit the filter and save it as a query, I then add the Query1
as a table. Then run the query, and it doesn't ask me for a value.
But, when I then make a form based on this new query (Query2) then it
won't let me update/edit the records.

Please help.

 

[Guest]

Hi

You say that the location is stored in a query. Queries don't store
anything so I'll assume the location is stored in a table, say, tblLocation,
with a single field called Location.

Create a query with all the info you want in your form and also join to
tblLocation.
The query SQL should look something like...

Select tblEmployee.myfield1, tblEmployee.myfield2, tblEmployee.myfield3
From tblEmployee inner join tblLocation
On tblEmployee.Location = tblLocation.Location

Then use this query as the source of your form.

Note that the above SQL is a guide only. Your actual query may have more
than 2 tables and you'll probably be using something like LocationID to join
instead of Location.

If you still can't edit the data in your form there may be other reasons why
the query isn't updateable - look up "updateable query" in the help and post
the query SQL back here.

An alternative approach is to avoid joining to the location table at all.
Just create a query with all info required and in the criteria for the
Location field put the following

= dfirst("Location", "tblLocation")

This function will find the first value of the field Location in the table
tblLocation.
Replace with your own field and table names. You can also put a query name
instead of a table so you could use your Query1 if necessary.

hth

Andy Hull

 

[pcstechnical.sfhs]

Hi

You say that the location is stored in a query. Queries don't store
anything so I'll assume the location is stored in a table, say, tblLocation,
with a single field called Location.

Create a query with all the info you want in your form and also join to
tblLocation.
The query SQL should look something like...

Select tblEmployee.myfield1, tblEmployee.myfield2, tblEmployee.myfield3
From tblEmployee inner join tblLocation
On tblEmployee.Location = tblLocation.Location

Then use this query as the source of your form.

Note that the above SQL is a guide only. Your actual query may have more
than 2 tables and you'll probably be using something like LocationID to join
instead of Location.

If you still can't edit the data in your form there may be other reasons why
the query isn't updateable - look up "updateable query" in the help and post
the query SQL back here.

An alternative approach is to avoid joining to the location table at all.
Just create a query with all info required and in the criteria for the
Location field put the following

= dfirst("Location", "tblLocation")

This function will find the first value of the field Location in the table
tblLocation.
Replace with your own field and table names. You can also put a query name
instead of a table so you could use your Query1 if necessary.

hth

Andy Hull

I have an employee profile form that I would like to filter by what
location the employee works out of.

The location is already determined based on a login I have in the
beginning. The location value is stored in a Query (call it query1).

When I get the records filtered to what I want, I still want to be
able to edit/update them.

Is this possible?

I have played around with the Adavanced Filter but haven't been
successful because when I filter the correct field in the employee
profile form, and put my EXPRESSION (I think this is part of the
problem) of what I want it filted by ([Query1]![Location]) it will ask
my, when I run the query, to give a value for Query1!Location. When I
do, it works. But, I can't have that popping up every time.

When I edit the filter and save it as a query, I then add the Query1
as a table. Then run the query, and it doesn't ask me for a value.
But, when I then make a form based on this new query (Query2) then it
won't let me update/edit the records.

Please help.- Hide quoted text -

- Show quoted text -

Well, stored may be a deceptive word. Supervisor logs in, and I have
a query that takes the their ID number and uses the Supervisor profile
table to figure out what location they belong to. Then the query only
shows the location name. So It's a column, one record query that has a
location name. I wanted to use that location name to filter all the
data in my forms.

I'm not very proficiant in VBA or SQL, but I can try. Any other
suggestions based on this new info?

 

[pcstechnical.sfhs]

Hi

You say that the location is stored in a query. Queries don't store
anything so I'll assume the location is stored in a table, say, tblLocation,
with a single field called Location.

Create a query with all the info you want in your form and also join to
tblLocation.
The query SQL should look something like...

Select tblEmployee.myfield1, tblEmployee.myfield2, tblEmployee.myfield3
From tblEmployee inner join tblLocation
On tblEmployee.Location = tblLocation.Location

Then use this query as the source of your form.

Note that the above SQL is a guide only. Your actual query may have more
than 2 tables and you'll probably be using something like LocationID to join
instead of Location.

If you still can't edit the data in your form there may be other reasons why
the query isn't updateable - look up "updateable query" in the help and post
the query SQL back here.

An alternative approach is to avoid joining to the location table at all.
Just create a query with all info required and in the criteria for the
Location field put the following

= dfirst("Location", "tblLocation")

This function will find the first value of the field Location in the table
tblLocation.
Replace with your own field and table names. You can also put a query name
instead of a table so you could use your Query1 if necessary.

hth

Andy Hull

I have an employee profile form that I would like to filter by what
location the employee works out of.

The location is already determined based on a login I have in the
beginning. The location value is stored in a Query (call it query1).

When I get the records filtered to what I want, I still want to be
able to edit/update them.

Is this possible?

I have played around with the Adavanced Filter but haven't been
successful because when I filter the correct field in the employee
profile form, and put my EXPRESSION (I think this is part of the
problem) of what I want it filted by ([Query1]![Location]) it will ask
my, when I run the query, to give a value for Query1!Location. When I
do, it works. But, I can't have that popping up every time.

When I edit the filter and save it as a query, I then add the Query1
as a table. Then run the query, and it doesn't ask me for a value.
But, when I then make a form based on this new query (Query2) then it
won't let me update/edit the records.

Please help.- Hide quoted text -

- Show quoted text -

This also might help: When they log in, they select their name from a
combo box and punch in their password. The combo box binds their
employee id number to a table. Is there any way I can bind two columns
to two different tables?

 

[Guest]

Hi again

Ok, if I understand, you already have a query set up that will tell you the
location for the user currently logged in. Good - this is exactly what we
need.

You also need a query to base your form on - which you also have.

The final step is to restrict the form's query using the location query.
2 possible options are:

1) Go into the form's query and add the location query as a table joining on
the location field.

2) Go into the form's query and put the following as the criteria for the
location field
= dlookup("Location", "Query1")

where Query1 is the name of the location query (and Location is its field
name).


If I haven't explained very clearly then post the SQL of your form's query
and the location query and I'll try to post back the SQL you need.

hth

Andy Hull

Hi

You say that the location is stored in a query. Queries don't store
anything so I'll assume the location is stored in a table, say, tblLocation,
with a single field called Location.

Create a query with all the info you want in your form and also join to
tblLocation.
The query SQL should look something like...

Select tblEmployee.myfield1, tblEmployee.myfield2, tblEmployee.myfield3
From tblEmployee inner join tblLocation
On tblEmployee.Location = tblLocation.Location

Then use this query as the source of your form.

Note that the above SQL is a guide only. Your actual query may have more
than 2 tables and you'll probably be using something like LocationID to join
instead of Location.

If you still can't edit the data in your form there may be other reasons why
the query isn't updateable - look up "updateable query" in the help and post
the query SQL back here.

An alternative approach is to avoid joining to the location table at all.
Just create a query with all info required and in the criteria for the
Location field put the following

= dfirst("Location", "tblLocation")

This function will find the first value of the field Location in the table
tblLocation.
Replace with your own field and table names. You can also put a query name
instead of a table so you could use your Query1 if necessary.

hth

Andy Hull

I have an employee profile form that I would like to filter by what
location the employee works out of.

The location is already determined based on a login I have in the
beginning. The location value is stored in a Query (call it query1).

When I get the records filtered to what I want, I still want to be
able to edit/update them.

Is this possible?

I have played around with the Adavanced Filter but haven't been
successful because when I filter the correct field in the employee
profile form, and put my EXPRESSION (I think this is part of the
problem) of what I want it filted by ([Query1]![Location]) it will ask
my, when I run the query, to give a value for Query1!Location. When I
do, it works. But, I can't have that popping up every time.

When I edit the filter and save it as a query, I then add the Query1
as a table. Then run the query, and it doesn't ask me for a value.
But, when I then make a form based on this new query (Query2) then it
won't let me update/edit the records.

Please help.- Hide quoted text -

- Show quoted text -

This also might help: When they log in, they select their name from a
combo box and punch in their password. The combo box binds their
employee id number to a table. Is there any way I can bind two columns
to two different tables?