DLookup?

  • Thread starter Thread starter Stockwell43
  • Start date Start date
S

Stockwell43

Hello,

I have a form that when the Collateral code is entered in a text box in my
AfterUpdate event, it automatically prefills the Rate field. The code is long
and I was wondering if there was an easier way to handle this. I thought
maybe a DLookup but that would mean I would have to use a drop down correct?
This is the code I am currently using:

Private Sub CollateralCode_AfterUpdate()
If Me.CollateralCode = "130" Then
Me.Rate = "0.0833"
ElseIf Me.CollateralCode = "131" Then
Me.Rate = "0.0833"
ElseIf Me.CollateralCode = "17" Then
Me.Rate = "0.0833"
ElseIf Me.CollateralCode = "18" Then
Me.Rate = "0.0833"
ElseIf Me.CollateralCode = "132" Then
Me.Rate = "0.0833"
ElseIf Me.CollateralCode = "133" Then
Me.Rate = "0.0833"
ElseIf Me.CollateralCode = "134" Then
Me.Rate = "0.0833"
ElseIf Me.CollateralCode = "135" Then
Me.Rate = "0.0833"
ElseIf Me.CollateralCode = "136" Then
Me.Rate = "0.0833"
ElseIf Me.CollateralCode = "335" Then
Me.Rate = "0.0833"
ElseIf Me.CollateralCode = "336" Then
Me.Rate = "0.0833"
ElseIf Me.CollateralCode = "41" Then
Me.Rate = "0.1000"
ElseIf Me.CollateralCode = "42" Then
Me.Rate = "0.1000"
ElseIf Me.CollateralCode = "43" Then
Me.Rate = "0.1000"
ElseIf Me.CollateralCode = "44" Then
Me.Rate = "0.1000"
ElseIf Me.CollateralCode = "45" Then
Me.Rate = "0.1000"
ElseIf Me.CollateralCode = "47" Then
Me.Rate = "0.1000"
ElseIf Me.CollateralCode = "48" Then
Me.Rate = "0.1000"
ElseIf Me.CollateralCode = "49" Then
Me.Rate = "0.1000"
ElseIf Me.CollateralCode = "50" Then
Me.Rate = "0.1000"
ElseIf Me.CollateralCode = "55" Then
Me.Rate = "0.1000"
ElseIf Me.CollateralCode = "56" Then
Me.Rate = "0.1000"
ElseIf Me.CollateralCode = "57" Then
Me.Rate = "0.1000"
ElseIf Me.CollateralCode = "58" Then
Me.Rate = "0.1000"
ElseIf Me.CollateralCode = "59" Then
Me.Rate = "0.1000"
Else
Me.Rate = 0.1041
End If
End Sub

If there is a simpler more efficient way, please simply answer and
explanation.

Thanks!!!
 
A Select statement would do the trick.

Select Case Me.CollateralCode
Case 17, 18, 130 to 136, 335, 336
Me.Rate = "0.0833"
Case 41 to 45, 47 to 59
Me.Rate = "0.1000"
Case Else
Me.Rate = 0.1041
End Select
 
Hi Klatuu, Thank for the quick response!

So are you saying just replace my code your code and that's it? That sounds
too easy. I'll give it try.

Thank you Sir!!
 
It is that easy.
Just replace my code with yours.
Be sure to double check the number in case I made an error.
 
Works like a gem Klatuu!!!! Thank you, that's much easier than writing and
Else If novel.
 
While Dave gave you simpler VBA code, I'd strongly suggest that you store
the rates in a table, rather than hard-coding them in your application.

Once you've stored them in a table, you can use DLookup to retrieve the rate
for the specific CollateralCode.
 
Hi Marshall,

That looks good as well. One question: Where do I place the code? Would it
be in the after update event of my Collateral Code field?

Thanks!!
 
Marshall,

I created the table and placed the code in the after update event of the
Collateral Code field then I tried in the open event field of the form and it
gives my the same error:

Run Time Error '3075'
Syntax error (missing operator) in query
expression 'Codes='.

Any suggestions on what this might be?

Thanks!!
 
The name Marsh suggested for the field was Code, and your error message says
Codes. What name did you use?
 
The code Marshall posted has a line continuation character in it.
If you put it all on one line, that would be the problem

Me.Rate = DLookup("Rate", "ColatteralRates", _
"Code=" & Me.CollateralCode)

The Underline means continue this code line on the next physical line. It
is used to keep all your code visible in the edit window without having to
scroll left and right.
It you have it all on one line, it would be"

Me.Rate = DLookup("Rate", "ColatteralRates", "Code=" & Me.CollateralCode)

And, the syntax assumes Code is a numeric field in your table. If it is a
text field, then change it to this:

Me.Rate = DLookup("Rate", "ColatteralRates", "Code=""" & Me.CollateralCode)
& """"
 
Hi Guys,

Ok, I followed all the instructions and I get a different error.


Run-Time error "3464"

Data type mismatch in criteria expression.

It has all the code highlighted in yellow.

Both the collateral code data type and rate are set up as text. What did I
do wrong?
 
Thank you Marshall, it seems to work now. Did you change something? I copy
and pasted your original code to a T and this time I copy and pasted it and
now it works. I'm just glad it worked and now I saved all the information in
my word doc for another time.

Thank you very much for staying with me and also thank you Doug and Klatuu
for all your help as well!!!!
 
Back
Top