perform calculations after textboxes updated

[Sketchin]

I have a subform based on a query. That query contains a couple of calculations that end up in textboxes on the subform. The problem is that the calculations require me to enter 2 dates that are on the subform. If these are not entered before the calculation is performed I get a "Cannot update record" error message and then #error in both Lease cost and Discount Cost textboxes.

I understand why I am getting this error, my question is, how do I get around it? I have included a picture for clarity.

 

[pr2-eugin]

Sketchin, is it possible for us to see the calculation involved? or the SQL Query?

 

[Sketchin]

No prob

SELECT tblReservation_details.*, tblBOM_Master.PurchasePrice,
 IIf([tblCompanies].[Portfolio]=0,(IIf((([PurchasePrice]*[DiscountMultiplier])/780)<10,(10*[WorkingDays]),(([PurchasePrice]*[DiscountMultiplier])/780)*[workingdays])),0) AS ZZZLeaseCost, tblCompanies.Category, tblReservations.DateOutReq, tblReservations.DateInReq, tblLookup_Contact_types.DiscountMultiplier, weekdays([dateoutreq],[dateinreq]) AS WorkingDays, tblCompanies.CompanyID, tblCompanies.CompanyName, tblCompanies.Address, tblCompanies.City, tblCompanies.[State/Province], tblCompanies.PostalCode, tblCompanies.Country, tblCompanies.PhoneNumber, tblCompanies.ProvinceStates, tblCompanies.Portfolio, ([Leasecost]*[discountMultiplier]) AS Discount, IIf([PurchasePrice]=0,0,(IIf([tblCompanies].[Portfolio]=0,(IIf((Nz([PurchasePrice]*[DiscountMultiplier])/780)<10,Nz(10*[WorkingDaysNew]),Nz(([PurchasePrice]*[DiscountMultiplier])/780)*[workingdaysNew])),0))) AS LeaseCost, tblReservation_details.DateOut, tblReservation_details.DateIn, weekdays([dateout],[datein]) AS WorkingDaysNew
FROM (tblLookup_Contact_types INNER JOIN tblCompanies ON tblLookup_Contact_types.[Contact Code] = tblCompanies.Category) INNER JOIN (tblReservations INNER JOIN (tblBOM_Master INNER JOIN tblReservation_details ON tblBOM_Master.BOMID = tblReservation_details.BOMNumber) ON tblReservations.ReservationID = tblReservation_details.ReservationID) ON tblCompanies.CompanyID = tblReservations.CompanyID;

I hope this doesnt look like a huge mess...not sure how to post it so its legible.

 

[boblarson]

The recordset of the subform, from what you've posted, should NOT be updateable. So that is your problem. It looks to me that you are trying to do too much with too many tables for a simple subform. The record source should be the table where the data is stored and other fields to DISPLAY would be garnered using a DLookup, DCount, Expression, Function, etc.

 

[Sketchin]

Just to clarify, lease cost and discount cost are not being stored anywhere, they are just being calculated on the fly. The problem is that date out and date in are used in the calculation and are null right after I choose a piece of equipment for the reservation.

 

[Sketchin]

I think I see what you mean. Just have the reservation details table as my record source, and then place my lease cost and discount cost equations directly in the textboxes.

Looking back, I think I thought I was saving time re-using the same query that I have used for other things and really should've used a new one.

 

[boblarson]

A good reference:
http://allenbrowne.com/ser-61.html