Set field dependent on other subform

[JMarie]

I have a tabbed form. MainTabForm
On one tab I have a billing subform. The billingsubform is basic shows date and a field called timeperiod. The client bills by specific time periods so all bills/fees for March go into one bill- all fees for June another/etc.
This billingsubform has 2 subforms on it.
The 1st subform is simple and shows by timeperiodSubfrm a count of needed workers by timeperiod. It’s a simple reference table- no editing on it.
e.g. March 6
April 2
June 4
The 2nd is the billingdetails subform where they can add individual fees for each time period. The user adds the fee type from a drop down box and the number of needed workers (field= txtWorkersNeeded).
They get this number of workers from the 1st subform and put that number in the 2nd subform.
Right now it works fine. But the users now wants the number of workers to automatically fill in when a fee is chosen in the billingdetailsSubform.
I understand what needs to happen, but not sure how to make it happen.
This is what I have as an idea:
If BillingdetailsSubform.feeId =5,4 or is null set BillingdetailsSubform.txtWorkersNeed=1 (these are annual fees and we don’t need the count of workers)
Else set
BillingdetailsSubform.txtWorkersNeeded=timeperiodSubform.txtWorkersNeeded
Where Billingsubform.TimePeriod=timeperiodSubform.Timeperiod

Make sense? Doable? I think it should be relatively easy on an onChange event with the BillingdetailsSubform.feeID column. But… well I’m feeling a bit stuck.
Any ideas would be greatly helpful in turning the logic into something useful.

 

[Uncle Gizmo]

I note you have yet to receive a reply to your question.

I haven't replied until now because although I get the gist of what you want, it's not quite clear to me how to go about it. I would find a simple sample database with just the form in question and some test data helpful.

 

[JMarie]

The If statement with the dlookup did work for this! Thanks.

I've been thinking about it and I think I made it too complicated- I don't need to involve all three subforms.

I am wondering if I can use an OnChange event with a Iif statment and Dlookup. Is this possible? (the where clause coming from the other form)
Something like this- if the feeID is 1,2,or3 find number Else set the field to 1.

Iif(FeeType=1 or 2 or 3, Me.WorkerNum=DLookup("numHowMany", "tblWorkersNeeded", "Where ID=frm1.IDNO and timePeriod=frm1.timeframe", "1")

Would that logic work?