DLOOKUP Needs to Reference Form or Subform

[samjh]

Hi,

I have a DLOOKUP which is used to auto populate a field on my Income Commitment Form based on a field on my Expenditure Commitment Form.

It works perfectly when you enter a new commitment.

Currently you enter a new Expenditure Commitment, save it but don't close it, then from the Expenditure Commitment Form there is is a button to enter a related Income Commitment. You save the Income commitment then close it, then close the expenditure form and you are on the Client screen with both these forms now showing as subforms, both have record selectors.

My issue is, that sometimes the Expenditure Subjective can change, so from the client form you use the record selector to open the Expenditure Commitment and select the correct Subjective, you then save it and close it. You should then click on the Income Record selector which opens the income commitment, but when you re-click on the Combo42 button, it doesn't reselect the Income subjective and I think it is because the Expenditure Commitment which is what it is looking up isn't open. My issue is how do I get the DLOOKUP to work and refer to the Commitment Form if it is a new commitment, or the Subform if I have gone back in through the record selector.

I hope that made sense. Here is the code I have.

Private Sub Combo42_AfterUpdate()
If (Me.Combo42 = 1) Then
Me.IncomeSubjective = DLookup("[Subjective]", "tblIncomeSubjective", "[ExpSubjective]='" & Forms![frmCommitments]![cmbSubjective] & "'")
Me.OtherIncomeConfirmed.Enabled = False
Me.OtherConfirmedBy.Enabled = False
Me.OtherConfirmedOn.Enabled = False
Else
Me.IncomeSubjective = DLookup("[IncomeSubjective]", "tblIncomeSource", "[IncomeSourceID]=" & Me.Combo42)
Me.IncomeAssessed.Enabled = False
Me.IncomeReceivedatSource.Enabled = False
Me.AssessedOn.Enabled = False
Me.AssessedBy.Enabled = False
Me.AssessmentType.Enabled = False
Me.DeferredIncome.Enabled = False
End If

End Sub


Thanks for any help you can give.

 

[Estuardo]

G'd morning Sam,
I don't understand what your app is doing, but if all what you want is to requery your cmbSubjective then do so.

If (Me.Combo42 = 1) Then
Forms![frmCommitments]![cmbSubjective].requery
Me.IncomeSubjective = DLookup("[Subjective]", "tblIncomeSubjective", "[ExpSubjective]='" & Forms![frmCommitments]![cmbSubjective] & "'")
Me.OtherIncomeConfirmed.Enabled = False
Me.OtherConfirmedBy.Enabled = False
Me.OtherConfirmedOn.Enabled = False

G'd luck