Create a table from 2 tables

[JIM]

Access 2000. I need to create/add to tblWorkOrders from info in two existing
tables tblCustomers and tblBuildings. Relationships are 1 to many
tblCustomers to tblWorkOrders and tblbuildings. The form is related to the
tblWorkOrders which has a combo box which calls tblCustomers. The row source
is:
GetCustomerNameForInvoice
a query used in invoicing.
I want to be able to select a customer in the combo box and the
corresponding buildings related to that customer come up in the subform.
When I select a customer there is nothing in the subform. If form is closed
and reopened the same customer is in the combo box and the subform populates
with the correct buildings. If another customer is selected though the
buildings do not updated and previous building from former customer are still
in the subform.
I read somewhere last night in discussions that the main form must be
related to the master file (1 file in the 1 to many) and any subforms must be
related to one of the many files. How do you suggest this be set up? The
purpose of the form is to create a Work Order Table to print and track Work
Orders.
Thanks, JIM

 

[John W. Vinson]

Access 2000. I need to create/add to tblWorkOrders from info in two existing
tables tblCustomers and tblBuildings. Relationships are 1 to many
tblCustomers to tblWorkOrders and tblbuildings. The form is related to the
tblWorkOrders which has a combo box which calls tblCustomers. The row source
is:
GetCustomerNameForInvoice
a query used in invoicing.
I want to be able to select a customer in the combo box and the
corresponding buildings related to that customer come up in the subform.
When I select a customer there is nothing in the subform. If form is closed
and reopened the same customer is in the combo box and the subform populates
with the correct buildings. If another customer is selected though the
buildings do not updated and previous building from former customer are still
in the subform.
I read somewhere last night in discussions that the main form must be
related to the master file (1 file in the 1 to many) and any subforms must be
related to one of the many files. How do you suggest this be set up? The
purpose of the form is to create a Work Order Table to print and track Work
Orders.
Thanks, JIM

WHOA.

You're mixing levels here.

Tables are for *storing* data. You don't need to create a new table in order
to print a work order.

Forms are for *displaying and editing* data. You very well might want a Form
based on WorkOrders and a subform based on Buildings. You can get the subform
to automatically display the buildings for that customer by making the Master
Link Field property of the Subform control the name of the combo box. This
option won't be offered by the wizard but you can just type in the combo box's
name.

And if you want to print anything, use a report. Base the report on a Query
joining tblCustomers to tblWorkorders to tblBuildings, and pull whatever
information you need; use an appropriate criterion, such as

=Forms!yourformname!WorkOrderNo

to select only the records for that work order.

John W. Vinson [MVP]

 

[JIM]

Thanks, John. Actually I solved my problem by adding
Forms!frmWorkOrders.Form.Requery
to my subroutine and it works perfect. I had two things going on. When I
added line to subroutine it turned red. I thought there was something wrong
with my syntax - I had never seen red before. It was trying to tell me my
name"frmWork Orders" was spelled wrong and when I changed it to
"frmWorkOrders" it worked fine-didn't like the embedded blank.
I will consider what you said in reply. My plan was to pull up correct
building number and update the work order with the building number then go to
a report to print work order. Is there anything wrong with that? It's not
important if building number changes in data base as work orders are only
internal docs.
Thanks, JIM

 

[John W. Vinson]

Thanks, John. Actually I solved my problem by adding
Forms!frmWorkOrders.Form.Requery
to my subroutine and it works perfect. I had two things going on. When I
added line to subroutine it turned red. I thought there was something wrong
with my syntax - I had never seen red before. It was trying to tell me my
name"frmWork Orders" was spelled wrong and when I changed it to
"frmWorkOrders" it worked fine-didn't like the embedded blank.
I will consider what you said in reply. My plan was to pull up correct
building number and update the work order with the building number then go to
a report to print work order. Is there anything wrong with that? It's not
important if building number changes in data base as work orders are only
internal docs.

My reaction was because you were - and apparently still are - creating a
tblWorkOrders containing data which is already stored in the two other tables.
This is redundant and unnecessary. If your tblWorkOrders contains only *links*
- the building ID for example - to the other tables I withdraw my objection.
If you're trying to put all of the data needed to print a workorder report
into this table, then you're missing out on the power of Access; the report
should be based on a multitable query pulling information from all the needed
tables, and each piece of information (building name, employee name, etc.)
should exist only once in its appropriate table.

John W. Vinson [MVP]

 

[JIM]

My reaction was because you were - and apparently still are - creating a
tblWorkOrders containing data which is already stored in the two other tables.
This is redundant and unnecessary. If your tblWorkOrders contains only *links*
- the building ID for example - to the other tables I withdraw my objection.
If you're trying to put all of the data needed to print a workorder report
into this table, then you're missing out on the power of Access; the report
should be based on a multitable query pulling information from all the needed
tables, and each piece of information (building name, employee name, etc.)
should exist only once in its appropriate table.

John W. Vinson [MVP]

The only item that goes into the tblWorkOrders from the subform is the
building number or link as you describe. Thanks for all your input, JIM