Read/Write FORM from Multiple Tables...

[Guest]

I’m building a form for users to make changes to certain records. The
control source of the form has to be a query because the displayed records
are from two different tables as follows:

tblEmployee + tblEmployeeActivity LINKED by EmployeeID

My goal is to display the records from tblEmployeeActivity for users to
update but need to show the employee [Name] field from tblEmployee next to
each record.

ISSUE: A multiple table query is read-only in return the form does not allow
changes to the fields from tblEmployeeActivity.

As an alternative approach I attempted to set the form record source
directly to the tblEmployeeActivity table and create a sub-query ( (select
[tblEmployee]![Name] from [tblEmployee] where [tblEmployee]![EmployeeID] =
[EmployeeID]))in the control source of a text box on the form to pull the
[Name] field from tblEmployee based on the records EmployeeID but after many
failures I heard that setting a sub-query in a text control on a form does
not work in Access.(not sure if this is 100% true)

Any fix or corrections to my statements above would be greatly appreciated.

Thank you

 

[Guest]

I hope this may help if I understand your situation correctly. I have a
couple of databases I use with this method. Try it to see if it works for
your situation.

Make a Main form based on tblEmployee. Make another form on
tblEmployeeActity. Make the form on tblEmployeeActivity the subform of
tblEmployee. Make sure the Wizard box is selected before adding the subform
to the main form. The Wizard will suggest the linking based on EmployeeID.
Now, when you select the employee in the main form, the corresponding
'Activity' will appear in the subform. If you make a combo box as part of the
main form, you can use it to select the employee from a list and the
corresponding record will appear. You'll need some coding for that to work.
Special queries are not needed.

 

[Guest]

Thank you, but I failed to mention that due to the amount of data to be
managed in the table tblEmployeeActivity this would have to be a continous
form which eliminates the subform option since you may only use a sub-form in
a single form view.

I hope this may help if I understand your situation correctly. I have a
couple of databases I use with this method. Try it to see if it works for
your situation.

Make a Main form based on tblEmployee. Make another form on
tblEmployeeActity. Make the form on tblEmployeeActivity the subform of
tblEmployee. Make sure the Wizard box is selected before adding the subform
to the main form. The Wizard will suggest the linking based on EmployeeID.
Now, when you select the employee in the main form, the corresponding
'Activity' will appear in the subform. If you make a combo box as part of the
main form, you can use it to select the employee from a list and the
corresponding record will appear. You'll need some coding for that to work.
Special queries are not needed.

--
Jim Ory

I’m building a form for users to make changes to certain records. The
control source of the form has to be a query because the displayed records
are from two different tables as follows:

tblEmployee + tblEmployeeActivity LINKED by EmployeeID

My goal is to display the records from tblEmployeeActivity for users to
update but need to show the employee [Name] field from tblEmployee next to
each record.

ISSUE: A multiple table query is read-only in return the form does not allow
changes to the fields from tblEmployeeActivity.

As an alternative approach I attempted to set the form record source
directly to the tblEmployeeActivity table and create a sub-query ( (select
[tblEmployee]![Name] from [tblEmployee] where [tblEmployee]![EmployeeID] =
[EmployeeID]))in the control source of a text box on the form to pull the
[Name] field from tblEmployee based on the records EmployeeID but after many
failures I heard that setting a sub-query in a text control on a form does
not work in Access.(not sure if this is 100% true)

Any fix or corrections to my statements above would be greatly appreciated.

Thank you

 

[Guest]

I don't know what version you are using, but I've used A2K and am using A2002
and my subforms are in datasheet view. Perhaps you are using a version I am
not at all familiar with. There are also some examples in the Northwind
Database that show subforms in datasheet view.
--
Jim Ory

Thank you, but I failed to mention that due to the amount of data to be
managed in the table tblEmployeeActivity this would have to be a continous
form which eliminates the subform option since you may only use a sub-form in
a single form view.

I hope this may help if I understand your situation correctly. I have a
couple of databases I use with this method. Try it to see if it works for
your situation.

Make a Main form based on tblEmployee. Make another form on
tblEmployeeActity. Make the form on tblEmployeeActivity the subform of
tblEmployee. Make sure the Wizard box is selected before adding the subform
to the main form. The Wizard will suggest the linking based on EmployeeID.
Now, when you select the employee in the main form, the corresponding
'Activity' will appear in the subform. If you make a combo box as part of the
main form, you can use it to select the employee from a list and the
corresponding record will appear. You'll need some coding for that to work.
Special queries are not needed.

--
Jim Ory

I’m building a form for users to make changes to certain records. The
control source of the form has to be a query because the displayed records
are from two different tables as follows:

tblEmployee + tblEmployeeActivity LINKED by EmployeeID

My goal is to display the records from tblEmployeeActivity for users to
update but need to show the employee [Name] field from tblEmployee next to
each record.

ISSUE: A multiple table query is read-only in return the form does not allow
changes to the fields from tblEmployeeActivity.

As an alternative approach I attempted to set the form record source
directly to the tblEmployeeActivity table and create a sub-query ( (select
[tblEmployee]![Name] from [tblEmployee] where [tblEmployee]![EmployeeID] =
[EmployeeID]))in the control source of a text box on the form to pull the
[Name] field from tblEmployee based on the records EmployeeID but after many
failures I heard that setting a sub-query in a text control on a form does
not work in Access.(not sure if this is 100% true)

Any fix or corrections to my statements above would be greatly appreciated.

Thank you

 

[Guest]

My mistake, sorry. My subform is based on a select query of the second table,
[tblEmployeeActivity] in your case. A txt field in each table are linked.

--
Jim Ory

Thank you, but I failed to mention that due to the amount of data to be
managed in the table tblEmployeeActivity this would have to be a continous
form which eliminates the subform option since you may only use a sub-form in
a single form view.

I hope this may help if I understand your situation correctly. I have a
couple of databases I use with this method. Try it to see if it works for
your situation.

Make a Main form based on tblEmployee. Make another form on
tblEmployeeActity. Make the form on tblEmployeeActivity the subform of
tblEmployee. Make sure the Wizard box is selected before adding the subform
to the main form. The Wizard will suggest the linking based on EmployeeID.
Now, when you select the employee in the main form, the corresponding
'Activity' will appear in the subform. If you make a combo box as part of the
main form, you can use it to select the employee from a list and the
corresponding record will appear. You'll need some coding for that to work.
Special queries are not needed.

--
Jim Ory

I’m building a form for users to make changes to certain records. The
control source of the form has to be a query because the displayed records
are from two different tables as follows:

tblEmployee + tblEmployeeActivity LINKED by EmployeeID

My goal is to display the records from tblEmployeeActivity for users to
update but need to show the employee [Name] field from tblEmployee next to
each record.

ISSUE: A multiple table query is read-only in return the form does not allow
changes to the fields from tblEmployeeActivity.

As an alternative approach I attempted to set the form record source
directly to the tblEmployeeActivity table and create a sub-query ( (select
[tblEmployee]![Name] from [tblEmployee] where [tblEmployee]![EmployeeID] =
[EmployeeID]))in the control source of a text box on the form to pull the
[Name] field from tblEmployee based on the records EmployeeID but after many
failures I heard that setting a sub-query in a text control on a form does
not work in Access.(not sure if this is 100% true)

Any fix or corrections to my statements above would be greatly appreciated.

Thank you

 

[John Vinson]

Thank you, but I failed to mention that due to the amount of data to be
managed in the table tblEmployeeActivity this would have to be a continous
form which eliminates the subform option since you may only use a sub-form in
a single form view.

You're mistaken: a *mainform* must be a single form. A Subform may be
single, continuous or datasheet, whichever you prefer.

Continuous Subforms are very, very commonly used! See the Orders form
in the Northwind sample database for an example.

John W. Vinson[MVP]

 

[Guest]

The “Activities†are uploaded into tblEmployeeActivity on a daily basis which
can be anywhere between 500-1000 records for over 400 employees. These
records need to be "eye-balled" validated by a single user who will view and
if need be make changes to the latest uploads and older existing records at
his discretion. This is where the update FORM comes into question. If I’m
understanding the sub form approach correctly the “main form†will display
the [name] from tblEmployee and the sub form will display the “Activities" in
Datasheet or continuous form view. This would mean that the user would have
to flip through 400 forms to find all the recent "Activities" and at the
same time will not allow them to sort/filter by a particular field value
which requires to view all the records not only the latest uploads for all
the employees.

My goal is as simple as displaying the [name] next to these “Activities†in
a text box or label retrieved from the tblEmployee on the EmployeeID of the
"Activity" since I don’t have the name in the “Activityâ€table. The name can
be locked and does not need to be modified; it's only for display other then
that the rest of the fields are the tblEmployeeActivity fields as follows...

NAME EMPID UPLOAD DATE ACTIVITY DATE
ACTIVITY TYPE ACTIVITY NOTES ACTIVITY STATUS

I hope this helps and thanks again for your efforts.

 

[John Vinson]

My goal is as simple as displaying the [name] next to these “Activities” in
a text box or label retrieved from the tblEmployee on the EmployeeID of the
"Activity" since I don’t have the name in the “Activity”table. The name can
be locked and does not need to be modified; it's only for display other then
that the rest of the fields are the tblEmployeeActivity fields as follows...

NAME EMPID UPLOAD DATE ACTIVITY DATE
ACTIVITY TYPE ACTIVITY NOTES ACTIVITY STATUS

I hope this helps and thanks again for your efforts.

I'd suggest, then, using a continuous Form based on tblActivity; use a
*COMBO BOX* bound to the EmployeeID. This combo box can store the ID
while displaying the name.

You can display the EmpID or leave it just bound to the combo box, as
you see fit.

John W. Vinson[MVP]

 

[Guest]

THANK YOU!

My goal is as simple as displaying the [name] next to these “Activities†in
a text box or label retrieved from the tblEmployee on the EmployeeID of the
"Activity" since I don’t have the name in the “Activityâ€table. The name can
be locked and does not need to be modified; it's only for display other then
that the rest of the fields are the tblEmployeeActivity fields as follows...

NAME EMPID UPLOAD DATE ACTIVITY DATE
ACTIVITY TYPE ACTIVITY NOTES ACTIVITY STATUS

I hope this helps and thanks again for your efforts.

I'd suggest, then, using a continuous Form based on tblActivity; use a
*COMBO BOX* bound to the EmployeeID. This combo box can store the ID
while displaying the name.

You can display the EmpID or leave it just bound to the combo box, as
you see fit.

John W. Vinson[MVP]

 

[Guest]

I'm a new user in using Microsoft Excel, hope micosoft can help me to solve
my problem.

I'm linking 2 sheets now , that is Worker sheet and Salary sheet.

Worker sheet is records all the informations of the workers, like code,
place, product color and so on.

Salary sheet is records different products that produced by the worker,
salary and so on.
So, when i key in the name in salary sheet, i need more than one record will
display in the same person. But I just can display only one record for one
person.

So, I hope microsoft can reply me as soon as possible. Thank you..

 

[Joseph Meehan]

I'm a new user in using Microsoft Excel, hope micosoft can help me to
solve my problem.

I'm linking 2 sheets now , that is Worker sheet and Salary sheet.

Worker sheet is records all the informations of the workers, like
code, place, product color and so on.

Salary sheet is records different products that produced by the
worker, salary and so on.
So, when i key in the name in salary sheet, i need more than one
record will display in the same person. But I just can display only
one record for one person.

So, I hope microsoft can reply me as soon as possible. Thank you..

Sorry. This is a newsgroup dedicated to questions about Access, the
database program in Office Professional. It appears your question may not
be related to these subjects. The Microsoft help system is not all that
clear and may have misdirected you here.

It is best to ask your questions in a newsgroup dedicated to the
subject of your question. You should find people better able to address
your problem there.

Note: It is always best to indicate the name and version of any
program(s) you may be using when asking a question and also indicate the
operating system (like Windows XP or 98) when you ask a question.

 

[Guest]

Thanks Jim - I had a similar problem and I think I've got around this using
your suggestion - Nice one!

I hope this may help if I understand your situation correctly. I have a
couple of databases I use with this method. Try it to see if it works for
your situation.

Make a Main form based on tblEmployee. Make another form on
tblEmployeeActity. Make the form on tblEmployeeActivity the subform of
tblEmployee. Make sure the Wizard box is selected before adding the subform
to the main form. The Wizard will suggest the linking based on EmployeeID.
Now, when you select the employee in the main form, the corresponding
'Activity' will appear in the subform. If you make a combo box as part of the
main form, you can use it to select the employee from a list and the
corresponding record will appear. You'll need some coding for that to work.
Special queries are not needed.

--
Jim Ory

I’m building a form for users to make changes to certain records. The
control source of the form has to be a query because the displayed records
are from two different tables as follows:

tblEmployee + tblEmployeeActivity LINKED by EmployeeID

My goal is to display the records from tblEmployeeActivity for users to
update but need to show the employee [Name] field from tblEmployee next to
each record.

ISSUE: A multiple table query is read-only in return the form does not allow
changes to the fields from tblEmployeeActivity.

As an alternative approach I attempted to set the form record source
directly to the tblEmployeeActivity table and create a sub-query ( (select
[tblEmployee]![Name] from [tblEmployee] where [tblEmployee]![EmployeeID] =
[EmployeeID]))in the control source of a text box on the form to pull the
[Name] field from tblEmployee based on the records EmployeeID but after many
failures I heard that setting a sub-query in a text control on a form does
not work in Access.(not sure if this is 100% true)

Any fix or corrections to my statements above would be greatly appreciated.

Thank you