Selecting New Record On Form Not Automatically Recalculating Fields (1 Viewer)

[greensleeves149]

Hi all,

I have a form with a couple of simple fees. One of these is a 'basic fee' which applies to all records. I have put a default value in this field for any new records created (let's for example say it was $1). Then there's an 'Additional Fee' for example, where the user would have to update an applicable amount if necessary (let's say $0.50). Finally I have a simple 'Total Fee', which adds the basic fee with the additional fee (if there is one).

What I have found is that when I select a new record, the 'Total Fee' doesn't show anything (or the $1 in this example). If I was to add a value to the additional fee, only then does the Total field autocalculate correctly.

What I would like is that the basic fee amount be displayed in the Total Fee field each time a new record is created as some items won't attract the additional fee.

I know I could put a default value in the Total field I guess, but was wondering if there was some sort of Requery action when a new record was created that would automatically recalc the "Total' field?

Would someone be able to advise me please?

Thank you.

 

[The_Doc_Man]

When you say "Select a new record" can you tell us what you mean by that (in the mechanical sense)? HOW do you select a new record? The reason I ask is that automatic behavior is linked to certain events and it is not clear from your description which event we should use. Tell us the mechanical aspect of this selection.

 

[arnelgp]

it's because, maybe, the Default value for Additional Fee is Null (not zero).
therefore:

Default Fee (not zero) + Additional Fee (null) = Null, while
Default Fee (not zero) + Additional Fee (default 0) = Default Fee

 

[greensleeves149]

When you say "Select a new record" can you tell us what you mean by that (in the mechanical sense)? HOW do you select a new record? The reason I ask is that automatic behavior is linked to certain events and it is not clear from your description which event we should use. Tell us the mechanical aspect of this selection.

Sure. At the moment I'm just using the arrows at the bottom of the form to go to start a new record. I also tried adding a form button to test that earlier too, so the button for the "Add New Record". That does the same thing. A new record doesn't show the 'total', although the 'basic' fee has a default of $1.

I hope that answers your question? In the total field on the form, I have something like =[BasicFee]+[AdditionalFee]

Reading arnelgp's comment about having a null has just given me another idea to try. I'll try it and be back to report.

 

[greensleeves149]

it's because, maybe, the Default value for Additional Fee is Null (not zero).
therefore:

Default Fee (not zero) + Additional Fee (null) = Null, while
Default Fee (not zero) + Additional Fee (default 0) = Default Fee

I think you're spot on with this but I'm obviously overlooking doing something here.

I realized that I need to have a default '0' for 'Additional fee' as like you said, 'Default Fee (not zero) + Additional Fee (null) = Null', so I added a 'Default Value' of 0 in the 'Additional Fee' form field earlier today. I realized this as I couldn't get my 'Total' to show! Once I added that, the Total worked ok, but I just couldn't get it working when there's a new record (that is, show the 'Basic Fee' always in the Total field).

After reading your comment though, I just wondered if it would make a difference if I set the 'Default' to 0 in the respective place in the table instead of the form? So I just tried that then and unfortunately it hasn't made a difference.

I still think you are correct and I think I've accidently missed something so I'll go back through and ensure it has 'Defaults' in it- just to make sure.

If I find I've made a mistake, I'll be right back to report it.

 

[oleronesoftwares]

1. All the fee columns must have default set to zero at table level.
2. The control source of the total fee= basic fee +additional fee
3. You might consider using =nz([basicfee]+[additionalfee]) as control source of the total fee field
4. Finally manually go through your records on the form and input 0 in the fields (e.g additional fee) that has null value, the changing of default value to 0 at table level, will affect new records, not existing one.

 

[greensleeves149]

1. All the fee columns must have default set to zero at table level.
2. The control source of the total fee= basic fee +additional fee
3. You might consider using =nz([basicfee]+[additionalfee]) as control source of the total fee field
4. Finally manually go through your records on the form and input 0 in the fields (e.g additional fee) that has null value, the changing of default value to 0 at table level, will affect new records, not existing one.

Thanks for this oleronesoftwares. This database is still under design with test data, so luckily your last point I won't have to do But thank you for clarifying that this should be done at the table level.

 

[greensleeves149]

it's because, maybe, the Default value for Additional Fee is Null (not zero).
therefore:

Default Fee (not zero) + Additional Fee (null) = Null, while
Default Fee (not zero) + Additional Fee (default 0) = Default Fee

You were spot on- again! I figured out the problem.

I actually have about 4 other 'optional fees' but I didn't include it in the OP as I didn't want to bore you guys and thought it was the same theory being applied.

After your post arnelgp, I was more suspicious of the Null element with one of those fields (and hence the Total wasn't showing), so I created another dummy 'total' field on the form and started adding each variable one by one and testing to find where the new Total field would stop working. I eventually found the culprit.

As you said, this problematic field was coming up to Access as a Null for some strange reason (although I had a default of 0 for it in the Table) so it was causing the 'Total' to fail. So I deleted the problematic field and re-added it to the form and it all started working perfectly including the Total showing when adding a new record.

Thank you to everyone for your time and effort in helping- very much appreciated.