DLookup problem

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

Well, let me try this again. I posted to the reports group on the 15th. No
response what so ever. So I'm guessing why.
1. Wrong group? I don't think that would have stopped a response.
2. Not adequate info? I have included more info in this post.
3. Doing something I shouldn't be doing? This is my hunch, but I don't have
a clue what I am doing wrong.

If I am correct about #3 above, Please give me your thoughts or point me in
the right direction! I will work to correct my ways.
Thanks for looking. I hope someone you can shed some light on the situation
or supply a fix for my problem.

I have a form to enter billing visits. It contains a control for the
following:
CoId (Number)
ContactID (Number)
PatientFName (Text)
PatientLName (Text)
VisitDate (Short Date)
RateTypeID (Number)
RateType (Text)
CurrRateAmt (Currency)

In the AfterUpdate event of the RateTypeID control, I have the following
lookup to fill the CurrRateAmt control with the current billing rate from
the CoRatestbl for the RateTypeID entered for the specific CoID entered. It
is working great, EXCEPT if you make an error after a record is entered and
have to go back and change the CoID to a different company. The CurrRateAmt
does not change unless you re-enter the RateTypeID. Each company may have a
different rate for the same type of visit. I am trying to work out a way to
get the CurrRateAmt to update if the CoID is changed as explained above.

Private Sub RateTypeID_AfterUpdate()

Me!CurrRateAmt = DLookup("RateAmt", "CoRatestbl", "CoID=" & CoID & " AND
RateTypeID=" & RateTypeID)
Me![CurrRateAmt].Requery

End Sub

Here is the SQL view behind the form:

SELECT PatientTbl.PtID, PatientTbl.PtFName, PatientTbl.PtLName,
PatientTbl.VisitDate, PatientTbl.ContactID, PatientTbl.CoID,
PatientTbl.RateTypeID, PatientTbl.CurrRateAmt, PatientTbl.Comments,
RateTypetbl.RateType, RateTypetbl.RateDescription
FROM RateTypetbl INNER JOIN PatientTbl ON RateTypetbl.RatetypeID =
PatientTbl.RateTypeID
WHERE (((PatientTbl.RateTypeID)=1 Or (PatientTbl.RateTypeID)=2 Or
(PatientTbl.RateTypeID)=11 Or (PatientTbl.RateTypeID)=21 Or
(PatientTbl.RateTypeID)=22 Or (PatientTbl.RateTypeID)=9));

I have searched google, can't find anything specific to my problem. Tried
"recalc" in different places on the form, no good.
I am at a loss.
Thanks for any assistance you can give me.
Bill
 
Bill: Easy one I think. Duplicate the RateTypeID_AfterUpdate() code after
the CoID so that when CoID is updated it will auto re-calc the numbers.

Private Sub CoID_AfterUpdate()

Me!CurrRateAmt = DLookup("RateAmt", "CoRatestbl", "CoID=" & CoID & " AND
RateTypeID=" & RateTypeID)
Me![CurrRateAmt].Requery

End Sub
 
Tynerr,
I feel foolish. I have messed with this for the last week.
You are correct. Thank You very much!
Here is what goes on though.
When the CoID is being changed on a record with an error, it has an entry in
the RateTypeID.
Your recommendation works. However, when entering a NEW record, the
RateTypeID is empty, and the code errors.
So I added the
If RateTypeID >0 Then
to the beginning of the code and it works like a dream.

Private Sub CoID_AfterUpdate()

If RateTypeID >0 Then
Me!CurrRateAmt = DLookup("RateAmt", "CoRatestbl", "CoID=" & CoID & " AND
RateTypeID=" & RateTypeID)
Me![CurrRateAmt].Requery
End If

End Sub

Thanks again and have a great night!
Bill


tynerr said:
Bill: Easy one I think. Duplicate the RateTypeID_AfterUpdate() code after
the CoID so that when CoID is updated it will auto re-calc the numbers.

Private Sub CoID_AfterUpdate()

Me!CurrRateAmt = DLookup("RateAmt", "CoRatestbl", "CoID=" & CoID & " AND
RateTypeID=" & RateTypeID)
Me![CurrRateAmt].Requery

End Sub


Bill said:
Well, let me try this again. I posted to the reports group on the 15th. No
response what so ever. So I'm guessing why.
1. Wrong group? I don't think that would have stopped a response.
2. Not adequate info? I have included more info in this post.
3. Doing something I shouldn't be doing? This is my hunch, but I don't have
a clue what I am doing wrong.

If I am correct about #3 above, Please give me your thoughts or point me in
the right direction! I will work to correct my ways.
Thanks for looking. I hope someone you can shed some light on the situation
or supply a fix for my problem.

I have a form to enter billing visits. It contains a control for the
following:
CoId (Number)
ContactID (Number)
PatientFName (Text)
PatientLName (Text)
VisitDate (Short Date)
RateTypeID (Number)
RateType (Text)
CurrRateAmt (Currency)

In the AfterUpdate event of the RateTypeID control, I have the following
lookup to fill the CurrRateAmt control with the current billing rate from
the CoRatestbl for the RateTypeID entered for the specific CoID entered. It
is working great, EXCEPT if you make an error after a record is entered and
have to go back and change the CoID to a different company. The CurrRateAmt
does not change unless you re-enter the RateTypeID. Each company may have a
different rate for the same type of visit. I am trying to work out a way to
get the CurrRateAmt to update if the CoID is changed as explained above.

Private Sub RateTypeID_AfterUpdate()

Me!CurrRateAmt = DLookup("RateAmt", "CoRatestbl", "CoID=" & CoID & " AND
RateTypeID=" & RateTypeID)
Me![CurrRateAmt].Requery

End Sub

Here is the SQL view behind the form:

SELECT PatientTbl.PtID, PatientTbl.PtFName, PatientTbl.PtLName,
PatientTbl.VisitDate, PatientTbl.ContactID, PatientTbl.CoID,
PatientTbl.RateTypeID, PatientTbl.CurrRateAmt, PatientTbl.Comments,
RateTypetbl.RateType, RateTypetbl.RateDescription
FROM RateTypetbl INNER JOIN PatientTbl ON RateTypetbl.RatetypeID =
PatientTbl.RateTypeID
WHERE (((PatientTbl.RateTypeID)=1 Or (PatientTbl.RateTypeID)=2 Or
(PatientTbl.RateTypeID)=11 Or (PatientTbl.RateTypeID)=21 Or
(PatientTbl.RateTypeID)=22 Or (PatientTbl.RateTypeID)=9));

I have searched google, can't find anything specific to my problem. Tried
"recalc" in different places on the form, no good.
I am at a loss.
Thanks for any assistance you can give me.
Bill
Click to expand...
Click to expand...
 
Back
Top