Results 1 to 6 of 6
  1. #1
    Cavebay is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    3

    Need Guidance on Best Practice

    Hello!



    In a subform, using the expression builder I want to fill a Currency field with a calculated value from a General Number field in a table (=[field]*.56), but the Property page sheet Events do not run the code. Are there rules around data types, or "where" this needs the be done, to work? I'm stymied here. Thanks for the help!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You can set the field property (=txtBoxName * .56)
    be sure to use CONTROL NAME and not field names. (tho they can have the same name)
    and the textbox is on the same form (or subform). otherwise you need a longer path.

    but beware nulls will give bad results , so :
    =Nz(txtBoxName,0) * .56

  3. #3
    Cavebay is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    3
    Thank you for the reply. I tried your idea, no change. Hopefully my screen shot tells the story. Click image for larger version. 

Name:	Access.png 
Views:	19 
Size:	110.2 KB 
ID:	46820

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    What do you mean by "fill a Currency field"? You want to save a calculated value into table field? Why? That requires code (macro or VBA).
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    looks like your are trying to apply lowcode/nocode principles to access

    you just need to put =nz(....

    in the controlsource, not the event.

    as this is a calculated value, it should not be stored.

    But I would also say the 0.56 needs to be in a table somewhere since it could change in the future. Changing the formula down the line means all values would change, past and future.

    alternatively, do your calculation in the form recordsource - Amount: nz(mileage,0)* 0.56

    And if you do want to store the actual amount, you would use the mileage after update event

    Code:
    Private Sub mileage_afterUpdate()
    
    me.amount=nz(me.mileage)*0.56
    
    end sub
    but the point about the 0.56 remains, if it changes, you need to modify your code. You need a table of mileage rates with an effective from date

    tblMileageRates
    PK...Rate...EffectiveFrom
    1.....0.56...1/1/2020
    2.....0.58...1/8/2021
    etc

    then use a dlookup or similar to find the rate applicable for a particular date

    Would also advise don't have spaces in table and field names and don't use reserved words (Description in your example). Also give ID's relevant names - down the line Access will be asking which ID you mean - use something like ExpenseID or better ExpensePK (which also tells you it is the primary key, use ExpenseFK for the foreign name in another table)

  6. #6
    Cavebay is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    3
    Thanks Ajax, this was very helpful and good advice.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Needs Guidance - How to go about
    By fareed in forum Access
    Replies: 2
    Last Post: 09-27-2018, 08:03 PM
  2. Guidance Needed
    By DSProductionz in forum Access
    Replies: 8
    Last Post: 08-20-2017, 09:03 PM
  3. Need guidance on something that seems simple
    By Locke_Valken in forum Access
    Replies: 17
    Last Post: 09-02-2016, 11:01 AM
  4. Best Practice Guidance
    By dpick in forum Access
    Replies: 2
    Last Post: 02-26-2016, 02:25 PM
  5. Need a bit of guidance
    By pipermac in forum Access
    Replies: 3
    Last Post: 10-14-2014, 08:18 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums