If you just run the query normally... you do have a field called Under21?
It doesn't show up as something like TableName.Under21? And does the
field
have a caption assigned to it on the table?
Rob I have tried that and it gives the same Error Message "You cancelled
the
previous operation"
Because the age is worked out for me I entered a new Field ExcessAge
where
you have to manually enter the driver's age after changing the code to
ExcessAge instead of Text480 this produces a different error The
expression
you entered as a query parameter produced the error The object doesn't
contain the automation object 'Under21."
when entering DLookup("[Under21]", "qryCheckRecSent") in the immediate
window
The others still give the same error "You cancelled the previous
operation"
I have 3 different versions of this db all 3 return the same errors so I
don't think all 3 of them could be corrupt, if there are though how do I
sort that out? I have run the repair db facility in Access.
Thanks again Bob
Very strange. It's possible that your db has got corrupted but,
just
to
check, what does
DLookup("[Under21]", "qryCheckRecSent")
give you in the same situation?
Hello Rob,
Rob
?DLookup("[Under21]", "qryCheckRecSent", crit) Gives the error message
"You
canceled the previous operation"
?DLookup("[EMExcess]", "qryCheckRecSent", crit) Gives you 100
which
is
correct
for the record I was in.
?DLookup("[EMExcess]+[Under21]", "qryCheckRecSent", crit) Gives the
error
message "You canceled the previous operation"
Does that help you?
Bob
Oops. Apologies for the decimal thing... I was thinking VB.Net
instead of VBA.
The addition in the lookup works for me. What happens if you
lose
the
EMExcess parts and just try something like...
DLookup("[Under21]", "qryCheckRecSent", crit)
Best way of trying that is to drop a breakpoint in on one of the
addition lookups... and then when the code pauses go to the
immediate
window and try...
?DLookup("[Under21]", "qryCheckRecSent", crit)
and
?DLookup("[EMExcess]", "qryCheckRecSent", crit)
and
?DLookup("[EMExcess]+[Under21]", "qryCheckRecSent", crit)
Hello Rob,
Well I have make some progress although I have come across the
following problems:
I Have entered the code as follows:
I had a problem with
dim crit as string, ed as decimal
This line of code gave me the following error "Compile Error
Expected
New or Type Name
So I entered the code as below, it only picks up the EMExcess
though,
can you help?
Thanks for your time it is really appreciated.
Bob
Private Sub RepairConfirmationSent_AfterUpdate()
Dim crit As String
Dim ed As Currency
crit = "[CustomerID]=" & Me.CustomerID
Select Case Me.Text480
Case Is < 21
ed = DLookup("[EMExcess]+[Under21]", "qryCheckRecSent", crit)
Case 22 To 25
ed = DLookup("[EMExcess]+[Under25]", "qryCheckRecSent", crit)
Case Else
ed = DLookup("[EMExcess]", "qryCheckRecSent", crit)
End Select
If Me.LicenceHeldFor < 1 Then
ed = ed + DLookup("Novice25Plus]", "qryExcess", crit)
End If
Me.ExcessDue = ed
End Sub
In that case it would be something along the lines of...
A subroutine called SetExcess in the form that gets called in the
after update event of any of the controls that can cause a change
to
be made.
private sub setexcess()
dim crit as string, ed as decimal
'If you don't have a customerid control, then add it
'Set criteria statement for use in lookups
crit="[CustomerID]="&me.customerid
select case me.textbox480 'see below
case <21
ed=dlookup("[EMExcess]+[Under21]","qryCheckRecSent",crit)
case 22 to 25
ed=dlookup("[EMExcess]+[Under25]","qryCheckRecSent",crit)
case else
ed=dlookup("[EMExcess]","qryCheckRecSent",crit)
end select
if me.licenceheldfor<1 then
ed=ed+dlookup("[Novice25Plus]","qryCheckRecSent",crit)
endif
me.excessdue=ed
end sub
.... you really should rename the textbox480 control to something
more
descriptive.
Rob your right of course the table Customers and Employers is
linked
together in a query called qryCheckRecSent
The Field CustomerID is the unique reference for the driver.
If the driver is under 21 then the excess would be ExcessDue =
Field
"EMExcess" plus Field "Under21"
That's correct
Thanks Bob
I'm afraid that your question doesn't make sense (to me at
least)....
There will need to be a unique reference for the driver... where
is
that?
If the driver is under 21 then the excess would be ExcessDue =
Field "EMExcess" plus Field "Under21"
Where is the field Under21? On the Employers table? That
doesn't
sound right. (Unless there's a foreign key on the table storing
drivers which allows you to look up the parent employer.)
Hi I need to known what a policy excess should be, how do I go
about
setting
it up so that the correct excess shows in my ExcessDue
field?
The drivers age is in "TextBox480" on form "COEvents" The field
"LicenceHeldFor" and "ExcessDue" are on the above form, all
the
other fields are in the Employers table.
The following criteria would need to be used:
If the driver is equal to 25 or over then
ExcessDue = Field "EMExcess"
If the above driver has held a licence for less than 1 Year
which is shown in the Field "LicenceHeldFor"
then I would need to add the value in Field "Novice25Plus"
to
the
"EMExcess"
field.
Also
If the driver is under 21 then the excess would be
ExcessDue = Field "EMExcess" plus Field "Under21"
If the driver is 21 or over but under 25 then the excess would
be
ExcessDue = Field "EMExcess" plus Field "Under25"
The "Noice25Plus" Field only applies to drivers 25 or over who
have
held
a licence for less than 1 year.
If anyone could give me some help on how to set this up I would
be very
grateful
Thanks Bob
