Display of calculated value on continuous form (1 Viewer)

[GaP42]

I have built a query that yields a calculated value if conditions are satisfied 0 if not. The form displays the calculated values for each row EXCEPT when the record meeting the criteria resulting in 0 is selected - in which case all calculated values appear as blank in the continuous form. Navigation to a record where the calculation is not 0 displays all correct values.
Is there a way around this?
When OK - appears as:

Navigate to the record with 0.

The calculation in query
Qty: FormatNumber(IIf(IsNull([FuelAmt]),IIf(IsNull([BatteryCharge]),0,[BatteryCharge]),[FuelAmt]),2)

Thanks for any advice given.

 

[theDBguy]

Can you post a sample db to demonstrate the problem?

 

[GaP42]

Thanks thedbGuy. I have attached a cut down version which shows the issue.
Open the SwitchBoard, select Motor Vehicle Logs - from the Find Vehicle Dialog, select first vehicle, select the Travel Diary tab, shows the most recent record of the travel diary. Select Log a Trip button on form to opens the Diary Trips and Purchases - the Purchases tab show the issue.

BTW the report available from that form also shows the issue - it is based off the subform for purchases too - and it does not show any QTY.

 

[theDBguy]

Thanks thedbGuy. I have attached a cut down version which shows the issue.
Open the SwitchBoard, select Motor Vehicle Logs - from the Find Vehicle Dialog, select first vehicle, select the Travel Diary tab, shows the most recent record of the travel diary. Select Log a Trip button on form to opens the Diary Trips and Purchases - the Purchases tab show the issue.

BTW the report available from that form also shows the issue - it is based off the subform for purchases too - and it does not show any QTY.

Thanks for posting your file. I think you may be causing this problem with the code you're using in your form's Current event. For example, if you remove the Current event, the problem goes away. So, you will have to troubleshoot the code to see which line is causing the problem and then try to use a different approach, if possible. For instance, see if you can use Conditional Formatting instead. Cheers!

 

[GaP42]

The thedbguy - moved the code from On Current to On Load on the subform and it now works. Solved.

 

[theDBguy]

The thedbguy - moved the code from On Current to On Load on the subform and it now works. Solved.

Glad to hear you got it sorted out. Good luck with your project.

 

[Pat Hartman]

The OnLoad event is rarely the correct event to use for something like this. Be aware that the calculation will happen only ONCE. The code will not run again if you filter the recordset or move to a new record.

 

[GaP42]

Thanks Pat - the calculation was performed in the query, the issue was the display of the result, which was conditional display of the control on the form. As the condition(s) applied to all records, placing this in OnLoad has worked and apply when new records are added too.