Filling tables from other tables

[Ray McKinney]

Table 1 has at least 20 pieces of data. Table 2 has 5 pieces of data. I want to fill table 2 from a form that when the first piece of data is pulled from a drop down the next 3 lines of data are "auto filled" from table 1. The remaining data is manual entry.

To be more specific, when filling out form 2, first item drop down is work order number (pulled from table 1) whatever wo# is selected the "start date" and "start time" automatically populate into form 2 (table 2) from table 1. The rest of the fields are manual entry.

Keep it simple guys, your dealing with someone who knows enough to be dangerours. (I am trying to abide by the advice given by the host)

The reason is simple. I will have 150 - 200 work order numbers with Original start dates and times. I need to keep a record of delays for each work order. There will be multiple delays for each work order, starts and stops. The second table is to track each delay. So, I may have to select the same wo 3-4 times (with original start data) and then manually enter the actual start/stop time alond with a reason for the each delay.
Goal is to have an easy "form" that an assistant could select a wo and have pertinent information specific to that wo automatically populate 30%-80% of the form.

 

[Galaxiom]

Repeating the original Work Order start date and start time in the second table is pointless. You can get this information whenever required from the first table through the WO number relationship.

If you want to display these fields on the form show them in a text box that is not bound to the Record Source. The Control Source for the Start Date textbox will be a domain lookup from the first table to the effect:

=DLookUp("[start date]", "[table 1]", "[table 1].[WorkOrderNumber]=Forms!formname!txtboxWorkOrderNumber")

 

[jjturner]

The Control Source for the Start Date textbox will be a domain lookup from the first table to the effect:

=DLookUp("[start date]", "[table 1]", "[table 1].[WorkOrderNumber]=Forms!formname!txtboxWorkOrderNumber")

It should be noted that there is a functional difference between the above quoted formula and this:

=DLookUp("[start date]", "[table 1]", "[table 1].[WorkOrderNumber]="
 & Forms!formname!txtboxWorkOrderNumber)

The first example will not update with immediate results should you change the value in the txtboxWorkOrderNumber control reference, whereas the second affords you the ability to automatically show the recalculation whenever Forms!formname!txtboxWorkOrderNumber is changed.

Secondly, from what you've stated, you have multiple delays which you need to track for each work order. That is a Many-to-1 relationship and can usually be handled with a Main Form / Subform design, linking the common field (wo#) between the Main/Sub forms in a Parent/Child relationship.

HTH,
John

 

[Galaxiom]

John's explanation of the difference between the condition as a simple string and the condition incorporating a concatenated control value is incredibly useful. I have always used the plain string condition and requeried as required. Now I know better this will save me work.

I guess the second technique effectively results in the control source changing when the original textbox changes and hence an automatic requery while the plain string is handled as a static control source.

There is always more to know in Access, even about the parts that might appear to be straightforward. Once again a "by the way" comment comes up with the goods. Thanks for the tip.