JerryC4174
asked on
How to make computed control in subform only update on the current record.
I have a parent child form where the child records are shown below the parent, see attache screen shot. I have a computed control that computes based on the value of the combo box [drug]. This part works. However when the value is computed, it copies itself into the same field for all child records. How can I make it so that only the current record is changed?
screenshot-dosage.PNG
screenshot-dosage.PNG
bfuchs
what is the control source of the computed text box?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The control is computed on the update of the cboDrug in the child form. I have attached the code. The value computed based on the drug selected. The druglist table has entries for each drug and a column of dosage per mL for dogs and a separate column for cats. The recommended dosage is the weight of the animal * the dosage per lb.
cboDrug_AfterUpdate
cboDrug_AfterUpdate
textbox should be based on =Iif( Me.Parent!cboPetType = "1" , DLookup("DosagePerlbCat", "DrugList", "DrugName = drug") * Me.Parent!Weight
, DLookup("DosagePerlbDog", "DrugList", "DrugName = drug") * Me.Parent!Weight)
, DLookup("DosagePerlbDog", "DrugList", "DrugName = drug") * Me.Parent!Weight)
actually instead of "Me.Parent!cboPetType" use forms!YourParentFormName.c
Let me know if you need additional help.
I'm not sure this is working at all so I changed it to use the form field rather than the variable name. The parser won't be able to evaluate Drug inside the quotes. It will assume it is part of the string being sent to the query created by the DLookup()
In order for this to be different on each row, Me.txtRecDosage must be a bound control. If you don't want to save the dosage in the table, then you need to replicate the If statement as an IIf() function and add it into the query.
Private Sub cboDrug_AfterUpdate()
If Me.Parent!cboPetType = "1" Then
Me.txtRecDosage = DLookup("DosagePerlbCat", "DrugList", "DrugName = '" & Me.cboDrug & "'") * Me.Parent!Weight
Else
Me.txtRecDosage = DLookup("DosagePerlbDog", "DrugList", "DrugName = '" & Me.cboDrug & "'") * Me.Parent!Weight
End IfIn order for this to be different on each row, Me.txtRecDosage must be a bound control. If you don't want to save the dosage in the table, then you need to replicate the If statement as an IIf() function and add it into the query.
Was my suggestion (ID: 42009085) ever tested?!
ASKER
Jim Dettmans comment was used and found to resolve the problem. I was remiss in not selecting the solution after I tried it and found it worked. My apologies.
No problem....glad to hear you got it resolved.
Jim.
Jim.