Do I need DLookup? or..... 1

[gusbrunston]

Hi:

Thanks for your attention to this post.

I am not familiar with the use of

DLookup

. Perhaps I need it or if you can help with the VBA for the following:

I use a form to post voided checks to a check register (checks mis-printed, etc.).

The form works very well, but it requires the user to input the original amount of the check. If an there is an input error, the check register is incorrect by the amount of the error.

I would like the form to find the payment amount of the original check and populate the control bound to the voided check amount.

The data source for the form is a query of tblOne.

The original check is a record in tblTwo.

In pigeon basic, this is what I need to do:

Private Sub txtVoidCheckNo_AfterUpdate()
   'Find "VoidCheckNo" in tblTwo
   '(where it's name is "CheckNumber"

How?

   'Populate txtDepAmt with the PaymentAmount
   'of the original check found above.
   Me.txtDepAmt = PaymentAmount of original check
End Sub

I've tried to make this as clear as possible. Let me know if additonal info is required.

Thanks greatly, Gus Brunston An old PICKer, using Access2000.

 

[TerpFan2001]

Gus:

I think this is what you are looking for. Assuming the check number is always a number.

Dim lCheckNumber As Long
Dim vPaymentAmount As Variant
Dim sLookupString As String

If IsNull(Me.txtVoidCheckNo) = False Then

lCheckNumber = Me.txtVoidCheckNo

sLookupString = "[CheckNumber]=" & lCheckNumber

vPaymentAmount = DLookup("[PaymentAmount]", "[Table2]", sLookupString)

Me.txtDepAmt = Format$(vPaymentAmount, "currency&quot

Else

MsgBox "Please Enter a Check Number", vbInformation

End If

 

[gusbrunston]

Dear TerpFan2001:

Wow! Works like a dream! This will be my DLookup paradigm.
Thanks so much. Gus Brunston An old PICKer, using Access2000.