ow in cont. FAuto Populate a rorm for a remaining amount (1 Viewer)

Auto populate a row in a subform based on a remaining amount



Hi,



I have been trying to figure out how to make a row auto populate into a continuous SubForm.



What i am dealing with is this,


the Continuous form is basically a list of active ingredients with the appropriate dose needed of that active ingredient next to it.



before the list is filled with Active ingredients a target dose amount is set for the batch. Most of the time the active ingredient dose will fall short of the target dose amount by 50 or so. The thing is that the remaining weight has to be filled with a cheap filler ingredient so that the total batch comes out at the right dose. (I hope i am making sense here)



basically,



Total/Target dose amount set to -- 800



Active Ingredients list:



Ingredient1 -- 100
Ingredient2 -- 100

Ingredient3 -- 100

Ingredient5 -- 100

Ingredient6 -- 250


TOTAL Actives ----650



(take the difference of the target dose minus the total actives dose and you have the amount of filler to add to the list. )



Dose Remainder = 150 ( this is what needs to be auto added to the list with that dose amount,150)


I'm not sure if its doable because i have been trying to figure it out for like three days.


you guys are my last hope...

I expect you will need VBA custom function.

it sounds like you have a spreadsheet type table structure.
Please can you tell us the field names in your table.
If possible, please supply a screenshot.

OP describes a continuous form is a list of ingredients. Sounds like they want code to calculate the remainder after records for active ingredients are entered.

Certainly doable. The calculation shouldn't really be that difficult (I think VBA using DSum() domain aggregate should be possible). The real trick is figuring out what event to put code into.

Hey,



Here are the field names,



For the Target Dose Per Capsule:
Product_ID
OrderQTY

MgLimitPerCapsule (this is the max mg load a capsule can be)

CapsulesPerUnit
CapsulesPerOrder
(this is all of the information that the order needs, this would be the main form. The MgLimit would stay on this page



For the Ingredients List:
RawPowder_ID

RawPowderName

MgPerCapsule
(this is the sub form the ingredients list. first you would select the RP Name and then tab to the next field and input the mg dose needed)





I dont have an example just yet. But basically the ingredients list will be interacting with a textbox control that has the mglimitPerCapsule field on the main form.

Maybe:

1. textbox in subform footer with expression =Sum([MgPerCapsule])

2. another textbox subtracts the sum from MgLimitPerCapsule to show the remainder

3. user enters record for the filler or code in some event (button Click maybe) creates record for filler ingredient

June7,



Thank you for your advice. It works great!