Forcing an Update to a Table Field From a Form

[Guest]

We have two tables; PERSON and WORKORDERS. These two tables are linked on a
field called USERNAME.
We also have a single form of which the top half of the form updates the
PERSON table and the bottom half of the form updates the WORKORDERS table.

Problem:
There are times when we only have Person information (which includes
USERNAME) but not Workorder information. When this occurs, we only fill out
the top half of the form until we get a Workorder number at a later date. The
PERSON table gets updated with the USERNAME but the WORKORDER table does not.
As a result, when we run a report using fields from both tables we get
missing records because there is not a match on USERNAME in the WORKORDER
table. (Without going into details, we still need to see some defaulted
Workorder values on the report, even if we don't have an actual Workorder
number yet.)

Question:
When we're only filling out Peson information, is there a way to force the
Person's USERNAME from the top half of the form to populate the WORKORDERS
table USERNAME field?

Thank you in advance,
Bob

 

[Dirk Goldgar]

We have two tables; PERSON and WORKORDERS. These two tables are
linked on a field called USERNAME.
We also have a single form of which the top half of the form updates
the PERSON table and the bottom half of the form updates the
WORKORDERS table.

Problem:
There are times when we only have Person information (which includes
USERNAME) but not Workorder information. When this occurs, we only
fill out the top half of the form until we get a Workorder number at
a later date. The PERSON table gets updated with the USERNAME but the
WORKORDER table does not. As a result, when we run a report using
fields from both tables we get missing records because there is not a
match on USERNAME in the WORKORDER table. (Without going into
details, we still need to see some defaulted Workorder values on the
report, even if we don't have an actual Workorder number yet.)

Question:
When we're only filling out Peson information, is there a way to
force the Person's USERNAME from the top half of the form to populate
the WORKORDERS table USERNAME field?

Thank you in advance,
Bob

You may be able to do that using code in the form's BeforeUpdate event.
However, it would probably be a lot simpler just to change the report's
RecordSource query to use an OUTER JOIN rather than an INNER JOIN. An
outer join is one that includes all the records from one of the joined
tables, even if there is no match in the other table for that record.
If you open the report's recordsource query in Query Design View, you
can double-click on the join line between the USERNAME and WORKORDER
table to edit the join type. If you change the option from "Only
include rows where the joined fields from both tables are equal" to the
one that says, "Include ALL records from 'USERNAME' and only those
records from 'WORKORDER' where the joined fields are equal", and let the
change to the recordsource query be saved, then your report will show
all USERNAMEs whether there are any matching WORKORDERs or not.

 

[Guest]

Hi Bob,

When we're only filling out Peson information, is there a way to force the
Person's USERNAME from the top half of the form to populate the
WORKORDERS table USERNAME field?

Populating a field in the WorkOrders table will create a new record, which
mean that you'd have to also populate any other fields that were set as
required.

Instead, you can base your report on an outer join between the two tables.
The default join is an inner join which, as you have discovered, will return
records only when there are matching values in both tables. An outer join can
be used to include *all* records from one table (PERSON) and matching records
from another table (WORKORDERS). In query design view, double-click on the
join line to change this setting.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

We have two tables; PERSON and WORKORDERS. These two tables are linked on a
field called USERNAME. We also have a single form of which the top half of
the form updates the PERSON table and the bottom half of the form updates the
WORKORDERS table.

Problem:
There are times when we only have Person information (which includes
USERNAME) but not Workorder information. When this occurs, we only fill out
the top half of the form until we get a Workorder number at a later date. The
PERSON table gets updated with the USERNAME but the WORKORDER table does not.
As a result, when we run a report using fields from both tables we get
missing records because there is not a match on USERNAME in the WORKORDER
table. (Without going into details, we still need to see some defaulted
Workorder values on the report, even if we don't have an actual Workorder
number yet.)

Question:
When we're only filling out Peson information, is there a way to force the
Person's USERNAME from the top half of the form to populate the WORKORDERS
table USERNAME field?

Thank you in advance,
Bob