I've got two tables. 1) Financials 2)Candidates
I've got a form for "Candidates" with a subform "Financials"
In the subform I'm trying to create an event procedure in field "Status" 'after change' that will Dlookup a value in the Candidates table (ConsultFee) and divide that value by 4 and place the result in the "Financials" table for field (Amount).
This is working, except when I go to another record and change the "status" field prompting the same eventprocedure I will not take the "Amount" value from that record but the first record in the table. I think it must have something to do with my strFilter variable. Any ideas anybody? much appreciated.
Private Sub StatusID_FK_AfterUpdate()
On Error GoTo Err_StatusID_FK_AfterUpdate
Dim strFilter As String
' Evaluate filter before it's passed to DLookup function.
strFilter = "FinID = " & Me!FinID 'the primary key in the Financials table (subform)
' Look up product's unit price and assign it to UnitPrice control.
Me!Amount = DLookup("ConsultFee", "Candidates", strFilter) / 4
Exit_StatusID_FK_AfterUpdate:
Exit Sub
Err_StatusID_FK_AfterUpdate:
MsgBox Err.Description
Resume Exit_StatusID_FK_AfterUpdate
End Sub
I've got a form for "Candidates" with a subform "Financials"
In the subform I'm trying to create an event procedure in field "Status" 'after change' that will Dlookup a value in the Candidates table (ConsultFee) and divide that value by 4 and place the result in the "Financials" table for field (Amount).
This is working, except when I go to another record and change the "status" field prompting the same eventprocedure I will not take the "Amount" value from that record but the first record in the table. I think it must have something to do with my strFilter variable. Any ideas anybody? much appreciated.
Private Sub StatusID_FK_AfterUpdate()
On Error GoTo Err_StatusID_FK_AfterUpdate
Dim strFilter As String
' Evaluate filter before it's passed to DLookup function.
strFilter = "FinID = " & Me!FinID 'the primary key in the Financials table (subform)
' Look up product's unit price and assign it to UnitPrice control.
Me!Amount = DLookup("ConsultFee", "Candidates", strFilter) / 4
Exit_StatusID_FK_AfterUpdate:
Exit Sub
Err_StatusID_FK_AfterUpdate:
MsgBox Err.Description
Resume Exit_StatusID_FK_AfterUpdate
End Sub
