Joining Event Proceedures

D

dbl

Hi I have the following 2 pieces of code which give me the results that I
need but I need them to work together, how do I go about joining them
together so that the code works out that its a "PrivateCar" so that the
excess will be X or if its a "CommercialVeh/Van" so the excess will be Y
rather that the operator having to see if its a car or commercial vehicle
then selecting which procedure to use to work out the excess. The first
procedure works from a combo box on exit event procedure and the second
procedure currently working from a tick box.

I would like it to all work from the combo box on exit procedure.

(First Procedure)
If Me.CarCommercial = "PrivateCar" Then
Dim crit As String
Dim ed As Currency
crit = "[CustomerID]=" & Me.CustomerID
Select Case Me.ExAge
Case Is < 21
ed = DLookup("[EMExcess]+[Under21]", "qryCheckRecSent", crit)
Case 21 To 24
ed = DLookup("[EMExcess]+[Under25]", "qryCheckRecSent", crit)
Case Else
ed = DLookup("[EMExcess]", "qryCheckRecSent", crit)
End Select
If Me.ExAge >= 25 And Me.Text472 < 1 Then
ed = ed + DLookup("[Novice25Plus]", "qryCheckRecSent", crit)
End If
End If
Me.ExcessDue = ed

(Second Procedure)
If Me.CarCommercial = "CommercialVeh/Van" Then
Dim crit As String
Dim ed As Currency
crit = "[CustomerID]=" & Me.CustomerID
Select Case Me.ExAge
Case Is < 21
ed = DLookup("[EMCommercialExcess]+[Under21]", "qryCheckRecSent", crit)
Case 21 To 24
ed = DLookup("[EMCommercialExcess]+[Under25]", "qryCheckRecSent", crit)
Case Else
ed = DLookup("[EMCommercialExcess]", "qryCheckRecSent", crit)
End Select
If Me.ExAge >= 25 And Me.Text472 < 1 Then
ed = ed + DLookup("[Novice25Plus]", "qryCheckRecSent", crit)
End If
End If
Me.ExcessDue = ed

Any help would be appreciated.

Thanks Bob
 
K

Klatuu

This should do it except for syntax errors on untested "air" code. A note:
Always do you Dims as the very first thing in you Sub or Function. It makes
them easier to find if you have to go back for debugging or modification
later. Also if your customer Id is a number, then your first line is okay.
If it is a string, then you need:

crit = "[CustomerID]='" & Me.CustomerID & "'"


Dim crit As String
Dim ed As Currency
Din strLookup As String

crit = "[CustomerID]=" & Me.CustomerID
If Me.CarCommercial = "PrivateCar" Then
Select Case Me.ExAge
Case Is < 21
strLookup = IIf(Me.CarCommercia = "PrivateCar", _
"[EMExcess]+[Under21]","[EMCommercialExcess]+[Under21]")
Case 21 To 24
strLookup = IIf(Me.CarCommercia = "PrivateCar", _
"[EMExcess]+[Under25]","[EMCommercialExcess]+[Under25]")
Case Else
strLookup = IIf(Me.CarCommercia = "PrivateCar", _
"[EMExcess]","[EMCommercialExcess]")
End Select
ed = DLookup(strLookup,"qryCheckRecSent",crit)
If Me.ExAge >= 25 And Me.Text472 < 1 Then
ed = ed + DLookup("[Novice25Plus]", "qryCheckRecSent", crit)
End If
End If
Me.ExcessDue = ed


dbl said:
Hi I have the following 2 pieces of code which give me the results that I
need but I need them to work together, how do I go about joining them
together so that the code works out that its a "PrivateCar" so that the
excess will be X or if its a "CommercialVeh/Van" so the excess will be Y
rather that the operator having to see if its a car or commercial vehicle
then selecting which procedure to use to work out the excess. The first
procedure works from a combo box on exit event procedure and the second
procedure currently working from a tick box.

I would like it to all work from the combo box on exit procedure.

(First Procedure)
If Me.CarCommercial = "PrivateCar" Then
Dim crit As String
Dim ed As Currency
crit = "[CustomerID]= " & Me.CustomerID
Select Case Me.ExAge
Case Is < 21
ed = DLookup("[EMExcess]+[Under21]", "qryCheckRecSent", crit)
Case 21 To 24
ed = DLookup("[EMExcess]+[Under25]", "qryCheckRecSent", crit)
Case Else
ed = DLookup("[EMExcess]", "qryCheckRecSent", crit)
End Select
If Me.ExAge >= 25 And Me.Text472 < 1 Then
ed = ed + DLookup("[Novice25Plus]", "qryCheckRecSent", crit)
End If
End If
Me.ExcessDue = ed

(Second Procedure)
If Me.CarCommercial = "CommercialVeh/Van" Then
Dim crit As String
Dim ed As Currency
crit = "[CustomerID]=" & Me.CustomerID
Select Case Me.ExAge
Case Is < 21
ed = DLookup("[EMCommercialExcess]+[Under21]", "qryCheckRecSent", crit)
Case 21 To 24
ed = DLookup("[EMCommercialExcess]+[Under25]", "qryCheckRecSent", crit)
Case Else
ed = DLookup("[EMCommercialExcess]", "qryCheckRecSent", crit)
End Select
If Me.ExAge >= 25 And Me.Text472 < 1 Then
ed = ed + DLookup("[Novice25Plus]", "qryCheckRecSent", crit)
End If
End If
Me.ExcessDue = ed

Any help would be appreciated.

Thanks Bob
Click to expand...
 
D

dbl

Hi Klatuu that works if its a Private Car but returns zero if its a
Commercial. Any ideas.

Bob


Klatuu said:
This should do it except for syntax errors on untested "air" code. A
note:
Always do you Dims as the very first thing in you Sub or Function. It
makes
them easier to find if you have to go back for debugging or modification
later. Also if your customer Id is a number, then your first line is
okay.
If it is a string, then you need:

crit = "[CustomerID]='" & Me.CustomerID & "'"


Dim crit As String
Dim ed As Currency
Din strLookup As String

crit = "[CustomerID]=" & Me.CustomerID
If Me.CarCommercial = "PrivateCar" Then
Select Case Me.ExAge
Case Is < 21
strLookup = IIf(Me.CarCommercia = "PrivateCar", _

"[EMExcess]+[Under21]","[EMCommercialExcess]+[Under21]")
Case 21 To 24
strLookup = IIf(Me.CarCommercia = "PrivateCar", _

"[EMExcess]+[Under25]","[EMCommercialExcess]+[Under25]")
Case Else
strLookup = IIf(Me.CarCommercia = "PrivateCar", _
"[EMExcess]","[EMCommercialExcess]")
End Select
ed = DLookup(strLookup,"qryCheckRecSent",crit)
If Me.ExAge >= 25 And Me.Text472 < 1 Then
ed = ed + DLookup("[Novice25Plus]", "qryCheckRecSent", crit)
End If
End If
Me.ExcessDue = ed


dbl said:
Hi I have the following 2 pieces of code which give me the results that I
need but I need them to work together, how do I go about joining them
together so that the code works out that its a "PrivateCar" so that the
excess will be X or if its a "CommercialVeh/Van" so the excess will be Y
rather that the operator having to see if its a car or commercial vehicle
then selecting which procedure to use to work out the excess. The first
procedure works from a combo box on exit event procedure and the second
procedure currently working from a tick box.

I would like it to all work from the combo box on exit procedure.

(First Procedure)
If Me.CarCommercial = "PrivateCar" Then
Dim crit As String
Dim ed As Currency
crit = "[CustomerID]= " & Me.CustomerID
Select Case Me.ExAge
Case Is < 21
ed = DLookup("[EMExcess]+[Under21]", "qryCheckRecSent", crit)
Case 21 To 24
ed = DLookup("[EMExcess]+[Under25]", "qryCheckRecSent", crit)
Case Else
ed = DLookup("[EMExcess]", "qryCheckRecSent", crit)
End Select
If Me.ExAge >= 25 And Me.Text472 < 1 Then
ed = ed + DLookup("[Novice25Plus]", "qryCheckRecSent", crit)
End If
End If
Me.ExcessDue = ed

(Second Procedure)
If Me.CarCommercial = "CommercialVeh/Van" Then
Dim crit As String
Dim ed As Currency
crit = "[CustomerID]=" & Me.CustomerID
Select Case Me.ExAge
Case Is < 21
ed = DLookup("[EMCommercialExcess]+[Under21]", "qryCheckRecSent", crit)
Case 21 To 24
ed = DLookup("[EMCommercialExcess]+[Under25]", "qryCheckRecSent", crit)
Case Else
ed = DLookup("[EMCommercialExcess]", "qryCheckRecSent", crit)
End Select
If Me.ExAge >= 25 And Me.Text472 < 1 Then
ed = ed + DLookup("[Novice25Plus]", "qryCheckRecSent", crit)
End If
End If
Me.ExcessDue = ed

Any help would be appreciated.

Thanks Bob
Click to expand...
Click to expand...
 
K

Klatuu

Sorry, I can't see the problem. Here is what I would do next:

Set up and run each query stand alone to see what the results are.
Put a Breakpoint at the beginning of the code and step through it, watching
all the variable and field values to see if the logic is working as expected.

Let me know what happens.

I thought Y was always 0 :)

dbl said:
Hi Klatuu that works if its a Private Car but returns zero if its a
Commercial. Any ideas.

Bob


Klatuu said:
This should do it except for syntax errors on untested "air" code. A
note:
Always do you Dims as the very first thing in you Sub or Function. It
makes
them easier to find if you have to go back for debugging or modification
later. Also if your customer Id is a number, then your first line is
okay.
If it is a string, then you need:

crit = "[CustomerID]='" & Me.CustomerID & "'"


Dim crit As String
Dim ed As Currency
Din strLookup As String

crit = "[CustomerID]=" & Me.CustomerID
If Me.CarCommercial = "PrivateCar" Then
Select Case Me.ExAge
Case Is < 21
strLookup = IIf(Me.CarCommercia = "PrivateCar", _

"[EMExcess]+[Under21]","[EMCommercialExcess]+[Under21]")
Case 21 To 24
strLookup = IIf(Me.CarCommercia = "PrivateCar", _

"[EMExcess]+[Under25]","[EMCommercialExcess]+[Under25]")
Case Else
strLookup = IIf(Me.CarCommercia = "PrivateCar", _
"[EMExcess]","[EMCommercialExcess]")
End Select
ed = DLookup(strLookup,"qryCheckRecSent",crit)
If Me.ExAge >= 25 And Me.Text472 < 1 Then
ed = ed + DLookup("[Novice25Plus]", "qryCheckRecSent", crit)
End If
End If
Me.ExcessDue = ed


dbl said:
Hi I have the following 2 pieces of code which give me the results that I
need but I need them to work together, how do I go about joining them
together so that the code works out that its a "PrivateCar" so that the
excess will be X or if its a "CommercialVeh/Van" so the excess will be Y
rather that the operator having to see if its a car or commercial vehicle
then selecting which procedure to use to work out the excess. The first
procedure works from a combo box on exit event procedure and the second
procedure currently working from a tick box.

I would like it to all work from the combo box on exit procedure.

(First Procedure)
If Me.CarCommercial = "PrivateCar" Then
Dim crit As String
Dim ed As Currency
crit = "[CustomerID]= " & Me.CustomerID
Select Case Me.ExAge
Case Is < 21
ed = DLookup("[EMExcess]+[Under21]", "qryCheckRecSent", crit)
Case 21 To 24
ed = DLookup("[EMExcess]+[Under25]", "qryCheckRecSent", crit)
Case Else
ed = DLookup("[EMExcess]", "qryCheckRecSent", crit)
End Select
If Me.ExAge >= 25 And Me.Text472 < 1 Then
ed = ed + DLookup("[Novice25Plus]", "qryCheckRecSent", crit)
End If
End If
Me.ExcessDue = ed

(Second Procedure)
If Me.CarCommercial = "CommercialVeh/Van" Then
Dim crit As String
Dim ed As Currency
crit = "[CustomerID]=" & Me.CustomerID
Select Case Me.ExAge
Case Is < 21
ed = DLookup("[EMCommercialExcess]+[Under21]", "qryCheckRecSent", crit)
Case 21 To 24
ed = DLookup("[EMCommercialExcess]+[Under25]", "qryCheckRecSent", crit)
Case Else
ed = DLookup("[EMCommercialExcess]", "qryCheckRecSent", crit)
End Select
If Me.ExAge >= 25 And Me.Text472 < 1 Then
ed = ed + DLookup("[Novice25Plus]", "qryCheckRecSent", crit)
End If
End If
Me.ExcessDue = ed

Any help would be appreciated.

Thanks Bob
Click to expand...
Click to expand...
Click to expand...
 
D

dbl

The car excess is usually £50 and the Commercial £300 plus more if its a
young or inexperienced driver.
Klatuu said:
Sorry, I can't see the problem. Here is what I would do next:

Set up and run each query stand alone to see what the results are.
Put a Breakpoint at the beginning of the code and step through it,
watching
all the variable and field values to see if the logic is working as
expected.

Let me know what happens.

I thought Y was always 0 :)

dbl said:
Hi Klatuu that works if its a Private Car but returns zero if its a
Commercial. Any ideas.

Bob


Klatuu said:
This should do it except for syntax errors on untested "air" code. A
note:
Always do you Dims as the very first thing in you Sub or Function. It
makes
them easier to find if you have to go back for debugging or
modification
later. Also if your customer Id is a number, then your first line is
okay.
If it is a string, then you need:

crit = "[CustomerID]='" & Me.CustomerID & "'"


Dim crit As String
Dim ed As Currency
Din strLookup As String

crit = "[CustomerID]=" & Me.CustomerID
If Me.CarCommercial = "PrivateCar" Then
Select Case Me.ExAge
Case Is < 21
strLookup = IIf(Me.CarCommercia = "PrivateCar", _

"[EMExcess]+[Under21]","[EMCommercialExcess]+[Under21]")
Case 21 To 24
strLookup = IIf(Me.CarCommercia = "PrivateCar", _

"[EMExcess]+[Under25]","[EMCommercialExcess]+[Under25]")
Case Else
strLookup = IIf(Me.CarCommercia = "PrivateCar", _
"[EMExcess]","[EMCommercialExcess]")
End Select
ed = DLookup(strLookup,"qryCheckRecSent",crit)
If Me.ExAge >= 25 And Me.Text472 < 1 Then
ed = ed + DLookup("[Novice25Plus]", "qryCheckRecSent", crit)
End If
End If
Me.ExcessDue = ed


:

Hi I have the following 2 pieces of code which give me the results
that I
need but I need them to work together, how do I go about joining them
together so that the code works out that its a "PrivateCar" so that
the
excess will be X or if its a "CommercialVeh/Van" so the excess will be
Y
rather that the operator having to see if its a car or commercial
vehicle
then selecting which procedure to use to work out the excess. The
first
procedure works from a combo box on exit event procedure and the
second
procedure currently working from a tick box.

I would like it to all work from the combo box on exit procedure.

(First Procedure)
If Me.CarCommercial = "PrivateCar" Then
Dim crit As String
Dim ed As Currency
crit = "[CustomerID]= " & Me.CustomerID
Select Case Me.ExAge
Case Is < 21
ed = DLookup("[EMExcess]+[Under21]", "qryCheckRecSent", crit)
Case 21 To 24
ed = DLookup("[EMExcess]+[Under25]", "qryCheckRecSent", crit)
Case Else
ed = DLookup("[EMExcess]", "qryCheckRecSent", crit)
End Select
If Me.ExAge >= 25 And Me.Text472 < 1 Then
ed = ed + DLookup("[Novice25Plus]", "qryCheckRecSent", crit)
End If
End If
Me.ExcessDue = ed

(Second Procedure)
If Me.CarCommercial = "CommercialVeh/Van" Then
Dim crit As String
Dim ed As Currency
crit = "[CustomerID]=" & Me.CustomerID
Select Case Me.ExAge
Case Is < 21
ed = DLookup("[EMCommercialExcess]+[Under21]", "qryCheckRecSent",
crit)
Case 21 To 24
ed = DLookup("[EMCommercialExcess]+[Under25]", "qryCheckRecSent",
crit)
Case Else
ed = DLookup("[EMCommercialExcess]", "qryCheckRecSent", crit)
End Select
If Me.ExAge >= 25 And Me.Text472 < 1 Then
ed = ed + DLookup("[Novice25Plus]", "qryCheckRecSent", crit)
End If
End If
Me.ExcessDue = ed

Any help would be appreciated.

Thanks Bob
Click to expand...
Click to expand...
Click to expand...
 
D

dbl

Klatuu I have checked every part of it, it works correctly if its a
"Private Car" but does not work at all if its a "CommercialVeh/Van" the qry
returns all the values needed to give the correct answer, I have cut and
pasted the code, is this how you would expect it to be?

I cannot see what triggers the code to see its a Commercial but I assume
that if its not a Private Car it defaults to Commercial is that correct?

Thanks for your help Bob

Dim crit As String
Dim ed As Currency
Dim strLookup As String

crit = "[CustomerID]=" & Me.CustomerID
If Me.CarCommercial = "Private Car" Then
Select Case Me.ExAge
Case Is < 21
strLookup = IIf(Me.CarCommercial = "Private Car", _
"[EMExcess]+[Under21]", "[EMCommercialExcess]+[Under21]")
Case 21 To 24
strLookup = IIf(Me.CarCommercial = "Private Car", _
"[EMExcess]+[Under25]", "[EMCommercialExcess]+[Under25]")
Case Else
strLookup = IIf(Me.CarCommercial = "Private Car", _
"[EMExcess]", "[EMCommercialExcess]")
End Select
ed = DLookup(strLookup, "qryCheckRecSent", crit)
If Me.ExAge >= 25 And Me.Text472 < 1 Then
ed = ed + DLookup("[Novice25Plus]", "qryCheckRecSent", crit)
End If
End If
Me.ExcessDue = ed


Klatuu said:
Sorry, I can't see the problem. Here is what I would do next:

Set up and run each query stand alone to see what the results are.
Put a Breakpoint at the beginning of the code and step through it,
watching
all the variable and field values to see if the logic is working as
expected.

Let me know what happens.

I thought Y was always 0 :)

dbl said:
Hi Klatuu that works if its a Private Car but returns zero if its a
Commercial. Any ideas.

Bob


Klatuu said:
This should do it except for syntax errors on untested "air" code. A
note:
Always do you Dims as the very first thing in you Sub or Function. It
makes
them easier to find if you have to go back for debugging or
modification
later. Also if your customer Id is a number, then your first line is
okay.
If it is a string, then you need:

crit = "[CustomerID]='" & Me.CustomerID & "'"


Dim crit As String
Dim ed As Currency
Din strLookup As String

crit = "[CustomerID]=" & Me.CustomerID
If Me.CarCommercial = "PrivateCar" Then
Select Case Me.ExAge
Case Is < 21
strLookup = IIf(Me.CarCommercia = "PrivateCar", _

"[EMExcess]+[Under21]","[EMCommercialExcess]+[Under21]")
Case 21 To 24
strLookup = IIf(Me.CarCommercia = "PrivateCar", _

"[EMExcess]+[Under25]","[EMCommercialExcess]+[Under25]")
Case Else
strLookup = IIf(Me.CarCommercia = "PrivateCar", _
"[EMExcess]","[EMCommercialExcess]")
End Select
ed = DLookup(strLookup,"qryCheckRecSent",crit)
If Me.ExAge >= 25 And Me.Text472 < 1 Then
ed = ed + DLookup("[Novice25Plus]", "qryCheckRecSent", crit)
End If
End If
Me.ExcessDue = ed


:

Hi I have the following 2 pieces of code which give me the results
that I
need but I need them to work together, how do I go about joining them
together so that the code works out that its a "PrivateCar" so that
the
excess will be X or if its a "CommercialVeh/Van" so the excess will be
Y
rather that the operator having to see if its a car or commercial
vehicle
then selecting which procedure to use to work out the excess. The
first
procedure works from a combo box on exit event procedure and the
second
procedure currently working from a tick box.

I would like it to all work from the combo box on exit procedure.

(First Procedure)
If Me.CarCommercial = "PrivateCar" Then
Dim crit As String
Dim ed As Currency
crit = "[CustomerID]= " & Me.CustomerID
Select Case Me.ExAge
Case Is < 21
ed = DLookup("[EMExcess]+[Under21]", "qryCheckRecSent", crit)
Case 21 To 24
ed = DLookup("[EMExcess]+[Under25]", "qryCheckRecSent", crit)
Case Else
ed = DLookup("[EMExcess]", "qryCheckRecSent", crit)
End Select
If Me.ExAge >= 25 And Me.Text472 < 1 Then
ed = ed + DLookup("[Novice25Plus]", "qryCheckRecSent", crit)
End If
End If
Me.ExcessDue = ed

(Second Procedure)
If Me.CarCommercial = "CommercialVeh/Van" Then
Dim crit As String
Dim ed As Currency
crit = "[CustomerID]=" & Me.CustomerID
Select Case Me.ExAge
Case Is < 21
ed = DLookup("[EMCommercialExcess]+[Under21]", "qryCheckRecSent",
crit)
Case 21 To 24
ed = DLookup("[EMCommercialExcess]+[Under25]", "qryCheckRecSent",
crit)
Case Else
ed = DLookup("[EMCommercialExcess]", "qryCheckRecSent", crit)
End Select
If Me.ExAge >= 25 And Me.Text472 < 1 Then
ed = ed + DLookup("[Novice25Plus]", "qryCheckRecSent", crit)
End If
End If
Me.ExcessDue = ed

Any help would be appreciated.

Thanks Bob
Click to expand...
Click to expand...
Click to expand...
 
D

dbl

Klatuu if you change the code as below: from "Private Car" to " Commercial
Veh/Van" the commercial side works but the private car side returns zero. I
hope this helps.

Thanks again Bob

Dim crit As String
Dim ed As Currency
Dim strLookup As String

crit = "[CustomerID]=" & Me.CustomerID
If Me.CarCommercial = "Commercial Veh/Van" Then
Select Case Me.ExAge
Case Is < 21
strLookup = IIf(Me.CarCommercial = "Commercial Veh/Van", _
"[EMExcess]+[Under21]", "[EMCommercialExcess]+[Under21]")
Case 21 To 24
strLookup = IIf(Me.CarCommercial = "Private Car", _
"[EMExcess]+[Under25]", "[EMCommercialExcess]+[Under25]")
Case Else
strLookup = IIf(Me.CarCommercial = "Private Car", _
"[EMExcess]", "[EMCommercialExcess]")
End Select
ed = DLookup(strLookup, "qryCheckRecSent", crit)
If Me.ExAge >= 25 And Me.Text472 < 1 Then
ed = ed + DLookup("[Novice25Plus]", "qryCheckRecSent", crit)
End If
End If
Me.ExcessDue = ed


Klatuu said:
Sorry, I can't see the problem. Here is what I would do next:

Set up and run each query stand alone to see what the results are.
Put a Breakpoint at the beginning of the code and step through it,
watching
all the variable and field values to see if the logic is working as
expected.

Let me know what happens.

I thought Y was always 0 :)

dbl said:
Hi Klatuu that works if its a Private Car but returns zero if its a
Commercial. Any ideas.

Bob


Klatuu said:
This should do it except for syntax errors on untested "air" code. A
note:
Always do you Dims as the very first thing in you Sub or Function. It
makes
them easier to find if you have to go back for debugging or
modification
later. Also if your customer Id is a number, then your first line is
okay.
If it is a string, then you need:

crit = "[CustomerID]='" & Me.CustomerID & "'"


Dim crit As String
Dim ed As Currency
Din strLookup As String

crit = "[CustomerID]=" & Me.CustomerID
If Me.CarCommercial = "PrivateCar" Then
Select Case Me.ExAge
Case Is < 21
strLookup = IIf(Me.CarCommercia = "PrivateCar", _

"[EMExcess]+[Under21]","[EMCommercialExcess]+[Under21]")
Case 21 To 24
strLookup = IIf(Me.CarCommercia = "PrivateCar", _

"[EMExcess]+[Under25]","[EMCommercialExcess]+[Under25]")
Case Else
strLookup = IIf(Me.CarCommercia = "PrivateCar", _
"[EMExcess]","[EMCommercialExcess]")
End Select
ed = DLookup(strLookup,"qryCheckRecSent",crit)
If Me.ExAge >= 25 And Me.Text472 < 1 Then
ed = ed + DLookup("[Novice25Plus]", "qryCheckRecSent", crit)
End If
End If
Me.ExcessDue = ed


:

Hi I have the following 2 pieces of code which give me the results
that I
need but I need them to work together, how do I go about joining them
together so that the code works out that its a "PrivateCar" so that
the
excess will be X or if its a "CommercialVeh/Van" so the excess will be
Y
rather that the operator having to see if its a car or commercial
vehicle
then selecting which procedure to use to work out the excess. The
first
procedure works from a combo box on exit event procedure and the
second
procedure currently working from a tick box.

I would like it to all work from the combo box on exit procedure.

(First Procedure)
If Me.CarCommercial = "PrivateCar" Then
Dim crit As String
Dim ed As Currency
crit = "[CustomerID]= " & Me.CustomerID
Select Case Me.ExAge
Case Is < 21
ed = DLookup("[EMExcess]+[Under21]", "qryCheckRecSent", crit)
Case 21 To 24
ed = DLookup("[EMExcess]+[Under25]", "qryCheckRecSent", crit)
Case Else
ed = DLookup("[EMExcess]", "qryCheckRecSent", crit)
End Select
If Me.ExAge >= 25 And Me.Text472 < 1 Then
ed = ed + DLookup("[Novice25Plus]", "qryCheckRecSent", crit)
End If
End If
Me.ExcessDue = ed

(Second Procedure)
If Me.CarCommercial = "CommercialVeh/Van" Then
Dim crit As String
Dim ed As Currency
crit = "[CustomerID]=" & Me.CustomerID
Select Case Me.ExAge
Case Is < 21
ed = DLookup("[EMCommercialExcess]+[Under21]", "qryCheckRecSent",
crit)
Case 21 To 24
ed = DLookup("[EMCommercialExcess]+[Under25]", "qryCheckRecSent",
crit)
Case Else
ed = DLookup("[EMCommercialExcess]", "qryCheckRecSent", crit)
End Select
If Me.ExAge >= 25 And Me.Text472 < 1 Then
ed = ed + DLookup("[Novice25Plus]", "qryCheckRecSent", crit)
End If
End If
Me.ExcessDue = ed

Any help would be appreciated.

Thanks Bob
Click to expand...
Click to expand...
Click to expand...
 
R

RuralGuy

dbl said:
Klatuu if you change the code as below: from "Private Car" to "
Commercial Veh/Van" the commercial side works but the private car side
returns zero. I hope this helps.

Thanks again Bob

Dim crit As String
Dim ed As Currency
Dim strLookup As String

crit = "[CustomerID]=" & Me.CustomerID
Click to expand...
***''' If Me.CarCommercial = "Commercial Veh/Van" Then
Select Case Me.ExAge
Case Is < 21
strLookup = IIf(Me.CarCommercial = "Commercial Veh/Van", _
"[EMExcess]+[Under21]",
"[EMCommercialExcess]+[Under21]")
Case 21 To 24
strLookup = IIf(Me.CarCommercial = "Private Car", _
"[EMExcess]+[Under25]",
"[EMCommercialExcess]+[Under25]")
Case Else
strLookup = IIf(Me.CarCommercial = "Private Car", _
"[EMExcess]", "[EMCommercialExcess]")
End Select
ed = DLookup(strLookup, "qryCheckRecSent", crit)
If Me.ExAge >= 25 And Me.Text472 < 1 Then
ed = ed + DLookup("[Novice25Plus]", "qryCheckRecSent",
crit)
End If ***''' End If
Me.ExcessDue = ed
Click to expand...

What happens if you remove the 1st test ie: (If Me.CarCommercial)?

I put *** on the two lines to comment out. I believe you will get the
results you are looking for.

-
RuralGuy

Please reply to the newsgroup
 
D

dbl

Thanks that now works fine.

RuralGuy said:
dbl said:
Klatuu if you change the code as below: from "Private Car" to "
Commercial Veh/Van" the commercial side works but the private car side
returns zero. I hope this helps.

Thanks again Bob

Dim crit As String
Dim ed As Currency
Dim strLookup As String

crit = "[CustomerID]=" & Me.CustomerID
Click to expand...
***''' If Me.CarCommercial = "Commercial Veh/Van" Then
Select Case Me.ExAge
Case Is < 21
strLookup = IIf(Me.CarCommercial = "Commercial Veh/Van", _
"[EMExcess]+[Under21]",
"[EMCommercialExcess]+[Under21]")
Case 21 To 24
strLookup = IIf(Me.CarCommercial = "Private Car", _
"[EMExcess]+[Under25]",
"[EMCommercialExcess]+[Under25]")
Case Else
strLookup = IIf(Me.CarCommercial = "Private Car", _
"[EMExcess]", "[EMCommercialExcess]")
End Select
ed = DLookup(strLookup, "qryCheckRecSent", crit)
If Me.ExAge >= 25 And Me.Text472 < 1 Then
ed = ed + DLookup("[Novice25Plus]", "qryCheckRecSent",
crit)
End If ***''' End If
Me.ExcessDue = ed
Click to expand...

What happens if you remove the 1st test ie: (If Me.CarCommercial)?

I put *** on the two lines to comment out. I believe you will get the
results you are looking for.

-
RuralGuy

Please reply to the newsgroup
Click to expand...
 

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

Code error is null 17
Form coding (Is Null) error 2
Still need assistance 3

Top