Auto-populating a text box

[sstasiak]

I have a form with fields 'Height' and 'BSA' stored in table OncRegMain, and a subform with fields 'Weight' and 'Datetaken' stored in table WeightUpdate. The two tables are linked by a Medical record#(MedRecNo) which is the primary key of OncRegMain. The ID for the WeightUpdate fields is assigned the value in MedRecNo from the main form.

So each MedRecNo can have multiple entries in the WeightDate table. It's meant to be a weight history.

The BSA field on the main form is equal to the 'Height' in inches multiplied by the 'Weight' in pounds. I can't figure out how to take the most current weight for a particular patient stored in the 'WeightUpdate' table, multiply it by the height, then populate the BSA field with the result.

The 'WeightUpdate' table is automatically sorted by dates going from most recent to oldest. So the first MedRedNo associated with the patient I'm looking for will be the most current weight taken.

Here's my data entry process:

In main form I enter 'Height', then tab to the subforms 'Weight' field and enter data, tab down to 'Date Taken' and enter that, then click the 'Save' button in the subform which saves those entries and clears the text boxes in case a series of dates and weights needs to be entered.

Any ideas on how to take the data in the 'MedRecNo' text box, search the 'WeightUpdate' table for the first entry matching the MedRecNo, take the weight for that record, multiply it by the # stored in the height field, then populate the BSA field with the result?

That cycle should happen each time a new weight entry is added to a record so that the BSA is always updated

I'm a beginner at this so that's why I'm adding as much info as possible, hoping that the replies will be just as detailed

 

[niteraven]

Try this in the control source of BSA field:

= [forms]![mainformname]![subformname]![subformfieldname] * [forms]![mainform]![mainformfieldname]



ANd the properties of the subform, on the event tab, place this inside of the afterupdate event:
[forms]![mainformname]![BSAfieldname].requery

Hope this helps

 

[sstasiak]

niteraven

That didn't work. In the BSA field I now see '#Name?'

 

[PHV]

Have a look here:

http://support.microsoft.com/?kbid=209099

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886