Move fields from Subform to a table not on the form

  • Thread starter Thread starter Roger via AccessMonster.com
  • Start date Start date
R

Roger via AccessMonster.com

I have a subform on my main form. The subform is just used as a list and
working area for the users. I’m making edits to the subform, but do not want
to save the edits to the subform. Upon Save of record, I’d like to save/or
MOVE the field edits from the subform to an events table for that record,
which is tied back to the companyid and the vendor# on the main form. (I'm
doing it this way because when I query the subform to include the events
table, the query is not updatable. If I can just move the fields to the
table, this would be a quicker solution).

The fields to be moved from the subform to the events table are:
vendorReqmnts; Yes/No; date; companyid.

How do move these fields from the subform to my events table upon save of the
record and leave the subform unedited for the next user?
 
More simply, how can I move fields from a subform to fields in another
subform?
 
I have a subform on my main form. The subform is just used as a list and
working area for the users. I’m making edits to the subform, but do not want
to save the edits to the subform. Upon Save of record, I’d like to save/or
MOVE the field edits from the subform to an events table for that record,
which is tied back to the companyid and the vendor# on the main form. (I'm
doing it this way because when I query the subform to include the events
table, the query is not updatable. If I can just move the fields to the
table, this would be a quicker solution).

The fields to be moved from the subform to the events table are:
vendorReqmnts; Yes/No; date; companyid.

How do move these fields from the subform to my events table upon save of the
record and leave the subform unedited for the next user?
Click to expand...

You're doing something simple the hard way.

If you base the Subform directly on the Events table, using

CompanyID;[Vendor#]

as the Master and Child Link Fields, it will be updateable and will
store the data with the correct links. It is not necessary (nor
appropriate) to base the subform on a multitable query; that's not how
subforms work!

If this doesn't work, please explain in more detail how your form is
set up.

John W. Vinson[MVP]
 
I'm now using two subforms.

One Subform1 is list of requirements that will change each time a new company
name is picked from the drop-down on the main form.

I need the other subform2 to copy the requirements from subform1 onto
subform2. Then subform2 is the event table that is being built. Next to
each requirement in subform2 is a yes/no check box for the User to click.
The full list of requirements must be copied to subform2 so that the user
knows which requirements to click yes/no for.

My problem is I don't know the code to copy the requirements from subform1 to
subform2. One the list is populated in subform1, I need those fields
copied/written to subform2. Then the user can start working in subform2.

Any suggestions??
 
I'm now using two subforms.

One Subform1 is list of requirements that will change each time a new company
name is picked from the drop-down on the main form.

I need the other subform2 to copy the requirements from subform1 onto
subform2. Then subform2 is the event table that is being built. Next to
each requirement in subform2 is a yes/no check box for the User to click.
The full list of requirements must be copied to subform2 so that the user
knows which requirements to click yes/no for.

My problem is I don't know the code to copy the requirements from subform1 to
subform2. One the list is populated in subform1, I need those fields
copied/written to subform2. Then the user can start working in subform2.

Any suggestions??
Click to expand...

Well, one: don't do it this way!

Subforms DO NOT CONTAIN DATA, any more than my office window contains
the Owyhee Mountains. They are display tools. The data resides in your
Tables, and only in your Tables. Get out of the mindset of wanting to
"copy data from a subform" - there is no data there to copy!

Secondly, it makes no sense to have two subforms containing the exact
same data. I see no point to the existance of Subform1, if the user is
interacting only with Subform2.

What are the underlying table structures? I'm wondering how you have
structured the Requirements and Events tables; I suspect that an
Append query, or perhaps a subform based on an outer join will work,
with a single subform; but not knowing your data structures, it's hard
to be sure.

John W. Vinson[MVP]
 
John,
Note well taken. I’m copying data from one table to another table. Below is
my table structure.........

Requirements Table Structure:

My users need a way to bring up custom requirements for each company that
they are working with. These requirements have a companyID associated with
it. The requirements vary and may be a list of 12 requirements or a list of
only 5 depending upon the companyID. So, for this table, the structure only
has companyID(duplicates ok), and Requirement. Example:

Companyid Requirement
1 social security check
1 employment check
1 education check
2 social security check
3 employment check
3 Exhibit A Form

Events Table Structure:

For the Events table, the structure has companyID(duplicates ok), Requirement,
Recd(yes/no), JO#. The JO# and the companyid will come from the main form
entry. The events table will build like the following:

Companyid Requirement Recd JO#
1 social security check yes 123
1 employment check NO 123
1 education check yes 123
1 social security check yes 987
1 employment check no 987
1 education check no 987
2 social security check yes 445
3 employment check yes J903
3 Exhibit A Form yes J903
3 employment check no J356
3 Exhibit A Form no J356

This is a data entry form. Basically, the User needs a subform on the main
data entry form where they can enter yes/no next to each requirement for the
company that they are working with.

I already have the link correct to pull up the special requirements for each
company based on the companyid. When the User clicks on a company from the
company drop-down on the main form, the subform1 populates with the special
requirements for that company. If they choose another company, those
different requirements populates the subform1.

My tasks now, is to enable the User to click yes or no next to each
requirement and save the JO# to the table. This yes or no field needs to
associate with the requirement for each company which can be different and
will change on the data entry form every time a new record is created and a
new company is picked from the drop-down list of companies.

My original thing was to keep subform1 which list the correct list of
requirements but then copy those requirements from the requirements table to
the events table as a starting point for subform2. Then subform 2, the user
can click yes/no, but then I needed a way to populate subform2 with the JO#
from the main form. I was placing the two subforms side by side so that the
User didn't know that it was two forms. To the user it looked like one form.
example:

subform 1 subform2
social security yes/no
Employment yes/no
Education yes/no
Exhibit B yes/no

The JO# is being used later in the process for reporting purposes and
modification. The User doesn't see it on the subform2 during data entry.


Any suggestions?? Am I building this wrong??
 
My tasks now, is to enable the User to click yes or no next to each
requirement and save the JO# to the table. This yes or no field needs to
associate with the requirement for each company which can be different and
will change on the data entry form every time a new record is created and a
new company is picked from the drop-down list of companies.

My original thing was to keep subform1 which list the correct list of
requirements but then copy those requirements from the requirements table to
the events table as a starting point for subform2. Then subform 2, the user
can click yes/no, but then I needed a way to populate subform2 with the JO#
from the main form. I was placing the two subforms side by side so that the
User didn't know that it was two forms. To the user it looked like one form.
example:
Click to expand...

One way you can make it one form is to base Subform2, not on Events,
but on a Query joining Requirements to Events by a Left Outer Join on
the Requirement field - "show all records in Requirements and matching
records in Events". Make the Requirement fields from BOTH tables are
included in the query's selected fields.

CompanyID;Jo#

and set the Child Link Field to the same. Be sure that

When the user checks a checkbox (bound to the yes/no field in Events)
it will automatically fill in the Requirement (from the join autofill)
and the CompanyID and Jo# (from the master/child link).

John W. Vinson[MVP]
 
I joined the requirements field for the two tables in a query. But the query
is notupdatable. Here's my SQL:

SELECT tblVerificationCkList.Companyid, tblVerificationCkList.CustPkg,
tblEventJOVerification.CustPkg, tblEventJOVerification.[Recd /Completed?],
tblEventJOVerification.Companyid, tblEventJOVerification.[JO#]
FROM tblVerificationCkList LEFT JOIN tblEventJOVerification ON
tblVerificationCkList.CustPkg = tblEventJOVerification.CustPkg;

companyid;custpkg,and JO# is (dups okay) on both tables

I think this will work for me, if I can get the query to be updatable.

Can you see where I might make a change?

Thanks!
 
okay I exhausted myself on this one. Since the query is not updatable, is it
possible to pull up the info that I need in subform1 (requirements table) and
then upon click of the yes/no from the subform2 (events table) have it copy
the requirements, companyid and JO# to the events table?

I tried it and it works, but it only copies the first requirement.

When I click on the first yes/no it copies the requirement, companyid and JO#.
A companyid can have several requirements. But then when I click on the next
yes/no it still copies the first requirement and not the 2nd one. Like the
requirements are not linked to the corresponding yes/no.

Do you have any ideas on this way? or solution for updating the query is
prefered.

Thanks,
 
I joined the requirements field for the two tables in a query. But the query
is notupdatable. Here's my SQL:

SELECT tblVerificationCkList.Companyid, tblVerificationCkList.CustPkg,
tblEventJOVerification.CustPkg, tblEventJOVerification.[Recd /Completed?],
tblEventJOVerification.Companyid, tblEventJOVerification.[JO#]
FROM tblVerificationCkList LEFT JOIN tblEventJOVerification ON
tblVerificationCkList.CustPkg = tblEventJOVerification.CustPkg;

companyid;custpkg,and JO# is (dups okay) on both tables

I think this will work for me, if I can get the query to be updatable.

Can you see where I might make a change?

Thanks!
Click to expand...

What is the Primary Key of the "one" side table in this relationship?
You might need to join on both CompanyID and Custpkg.

John W. Vinson[MVP]
 
Back
Top