Event Procedure Running Slow

Question

Because you cannot set a default value to look up information in another table I have created an unbound field that looks up the information.

=DLookUp("[FeeCPTFee]","ProviderFeeSchedule","[FeeCptCode] = Form![CPT1]") I have named this field LookUpFee1

I then created and event procedure for On Got Focus

Private Sub LineCharge1_Dirty(Cancel As Integer)
    Form!LineCharge1 = Form!FeeLookUp1
End Sub

In order to get this to actually put the information in the LineCharge1 field I sometimes have to enter all data then go back and click on the field before it actually works.

What am I doing wrong or is there a better way to do this.

Answer 1

Hi. Who said you can't use DLookup() for a Default Value? I do it all the time.

Answer 2

When I put the DLookUp code in the default value it does not do anything.

Answer 3

I think you misunderstand the purpose of the DefaultValue property.  It inserts a value into a control by default when the form is moved to an empty new record.  Consequently it cannot be determined by the value of another column in the row as no such value exists at this stage.  I think you probably need the following code in the AfterUpdate event procedure of the CPT1 control:

    Dim strCriteria As String

    strCriteria = "FeeCptCode = " & Me.CPT1
    Me.LineCharge1 = DLookUp("FeeCPTFee","ProviderFeeSchedule",strCriteria)

This assumes:

1.  The CPT1 control contains a value of number data type.  If it's text amend the code as follows:

    strCriteria = "FeeCptCode = """ & Me.CPT1 & """"

2.  The CPT1 control is updated manually by the user.  Otherwise its AfterUpdate even procedure is not executed, in which case you would need to add the above code to whatever assigns a value to the CPT1 control.

---

Ken Sheridan, Stafford, England

Answer 4

Worked great, thank you.