DLookup?

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!!!
 
K

Klatuu

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
 
S

Stockwell43

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!!
 
K

Klatuu

It is that easy.
Just replace my code with yours.
Be sure to double check the number in case I made an error.
 
S

Stockwell43

Works like a gem Klatuu!!!! Thank you, that's much easier than writing and
Else If novel.
 
D

Douglas J. Steele

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.
 
M

Marshall Barton

Stockwell43 said:
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
Click to expand...


You can certainly make that code shorter:

Select Case Me.CollateralCode
Case 130,131,17,18,132,133,134,135,136,335,336
Me.Rate = 0.0833
Case 41,42,43,44,45,47,48,49,50,55,56,57,58,59
Me.Rate = 0.1000
Case Else
Me.Rate = 0.1041
End Select

BUT, you're right, a DLookup would be better because rate
changes could be made without changing any code.

First create a table (named ColatteralRates) with two fields
(named Code and Rate) and populate it with records like:

130 0.0833
131 0.0833
17 0.0833
,,,
41 0.1000
42 0.1000
...
1 0.1041
2 0.1041
...

Then the code would look like:

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

Stockwell43

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!!
 
S

Stockwell43

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!!
 
D

Douglas J. Steele

The name Marsh suggested for the field was Code, and your error message says
Codes. What name did you use?
 
K

Klatuu

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)
& """"
 
M

Marshall Barton

Stockwell43 said:
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:
Click to expand...

The Open event is way too early. The AfterUpdate event
seems like it should be appropriate, but, if your form is
loaded with mutiple records, it should also be in the form's
Current event.

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

You will have to post a Copy/Paste of the code you used
before I can make a meaningful attempt to debug it for you.
 
S

Stockwell43

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?
 
M

Marshall Barton

Stockwell43 said:
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?
Click to expand...


Text values have to be quoted:

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

Stockwell43

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!!!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error Handler problem - Please help 3
Multiple numbers on IF Statement 1
Same Results - which is better? 5
http://mohsin.motionsforum.com/16april-finaltip 0
Code problem. 5
Auto calculate 1
Ron DeBruin's "Enable_Keys" program 2
//autoaverage. 2

Top