Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculated Field (A2K)

    Hi,

    On a form I have three fields ActualWeight, VolumeWeight and ChargeableWeight. ActualWeight is typed in free hand and VolumeWeight is calculated from other fields on the form. What I need if it is possible to have a statement in the control source of the ChargeableWeight to say "Populate me with whatever is the greater value of these 2 boxes" and then have the tab stop set to No so that the user cannot change the value.

    Any ideas appreciated

    Thanks

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Calculated Field (A2K)

    A simple IF statement comparing the values as the data source for the text box should do the trick, but I assume you want to save the value once the form is completed. In that case you may want to put some code behind the ON UPDATE property of the ActualWeight text box and calculate the value ChargeableWeight. That way it can be bound to the record where you want to save it.
    Wendell

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculated Field (A2K)

    Thanks for the response.

    As you may have gathered I am very much a novice and learning 'on the hoof' so to speak. In the first part of your answer you said use a simple If statement, would this be an event procedure, and if so where shoud it go. Once I know I will play about and try to write something and then shout help when it fails badly!!.

    Thanks

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculated Field (A2K)

    Hey, I can't believe it, I managed to figure it out myself. I shall print out and frame these famous first 2 lines for prosperity. I don't know if it was good for my laptop because it went to 'sleep', I however in true male tradition laid back on bed and had a cigarette.

    One thing I still need help on though is how to stop the user going into the field. Because I have made my if statement in the 'OnEnter' event I cannot set the tab stop to No. Any ideas please.

    Thanks

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Calculated Field (A2K)

    I don't understand why you used the 'OnEnter' event? It seems to me you want to run the code after the user has typed in the ActualWeight value, in which case the code should run in the 'AfterUpdate' event for the text box where that value is typed. That way you can set the tab stop to 'NO' for the CalculatedWeight and actually set ENABLED to NO, and LOCKED to YES on the Data tab of Properties for that text box. Hope this helps.
    Wendell

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculated Field (A2K)

    The reason I put it there was because I didn't have a ******* clue were else to put it and it seemed logical to me that if the box was taking its value from 2 other fields it should find / calculate the value as it entered the box. I am currently (1200 Saturday) sitting in an internet pub in Singapore, if I am still sober (I doubt it) when I get home I will try your suggestion, thanks.

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  7. #7
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculated Field (A2K)

    Hi Wendell?

    That did not work, the field returned a balnk value, perhaps I am being thick. As the box is taking its value from another source I would not have thought that there would be an AfterUpdate event? When I switch back to OnEnter it determines which is the greater value ActualWeight or VolumeWeight and then uses it, all I want to do then is make this field un changeable.

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Calculated Field (A2K)

    Hi Steve,
    Is ChargeableWeight always simply the greater of ActualWeight and VolumeWeight? If so, there's no need to actually store the value, you can just use something like:
    =iif(ActualWeight>VolumeWeight,ActualWeight,Volume Weight)
    as your control source. As it would then be a calculated field, the user won't be able to change its value, though you can still set its tabstop to No to stop them having to tab through it each time.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Calculated Field (A2K)

    Are you certain you put it on the AfterUpdate event of the ActualWeight text box? If you put it on the AfterUpdate of the CalculatedWeight text box the event will never fire because it is never being updated. You could also leave the calculation on the OnEnter event, do the calculation, and then set the focus to the next control in the tab sequence, but then you are dependent on the user to tab into the text box in order to run the calculation. Does the user have to enter both the ActualWeight and the VolumeWeight on the form? If so you may want code on both text boxes to check and see if the value in the other text box has been entered so if a user mouses rather than tabs you don't get arithmetic errors when you try to calculate the weight.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •