Results 1 to 5 of 5
  1. #1
    DubCap01 is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2014
    Location
    Dubbo, Australia
    Posts
    104

    Update table entries not quite right

    Hi all,



    I am trying to update a field in a table with calculated values, dependent on the values from another table which can change every so often.
    Here's the scenario. TblTripCosts contains many fields with numeric values, but for this example let's say it only has three. [Destination], [AmboLSCharge] and [Kilometres]
    I am trying to update the table's [AmboLSCharge] field by combining the results of looking up two other fields in another table, and multiplying one of those results by the kilometres in TblTripCosts.
    The other table, TblDataTypes, has two fields called [DataType] and [Value].

    Example:
    TblTripCosts
    Destination = Dubbo
    AmboLSCharge =
    Kilometres = 45

    TblDataTypes
    DataType = AmboLSFlagFall, Value = 617.00
    DataType = AmboLSKms, Value = 1.77

    So, what I want the function to do is to update all entries in TblTripCosts to reflect the AmboLSCharge to equate to [TblDataTypes].[AmboLSFlagFall] + ([TblDataTypes].[AmboLSKms] * [TbltripCosts].[Kilometres])
    The code I tried in the function is not good enough, as it wipes the field completely! haha
    Can someone point me in the right direction please?

    Code:
    Public Function TripCoster()
    
            Dim ALSFF, ALSKM As Integer
            
            ALSFF = DLookup("Value", "TblDataTypes", "Datatype = 'AmboLSFlagFall'")
            ALSKM = DLookup("Value", "TblDataTypes", "Datatype = 'AmboLSKms'")
    
    
            With TblTripCosts
                    .AmboLSCharge = ALSFF + (ALSKM * [TblTripCosts].[KILOMETRES])
            End With
            
    End Function
    thanks in anticipation
    Pete

  2. #2
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Can you not do this in a query itself?
    cost:[TblDataTypes].[AmboLSFlagFall] + ([TblDataTypes].[AmboLSKms] * [TbltripCosts].[Kilometres])

  3. #3
    DubCap01 is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2014
    Location
    Dubbo, Australia
    Posts
    104
    Thanks Andy,
    I don't want to build a query, the db is already top heavy with over 250 queries. It's easier to control the function than search for another query
    Plus, the function will not just be doing this one task....
    Pete

  4. #4
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Code:
    With TblTripCosts
                    .AmboLSCharge = ALSFF + (ALSKM * [TblTripCosts].[KILOMETRES])
            End With
    What do you expect this line to do? Update the table?

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    First and foremost, it's generally considered bad practice to store calculated values. If you're bent on doing that anyway, ensure that your lookup values are what you expect. For one thing, ALSFF has been declared as a variant, not an integer, so who knows what you're getting? Any calculation that returns a Null usually results in a Null, so that's probably why your data is disappearing. To have multiple declarations on one line is done as Dim ALSFF As Integer, ALSKM As Integer. Try either a msgbox or debug.print statement to check your returned values:
    Code:
            ALSFF = DLookup("Value", "TblDataTypes", "Datatype = 'AmboLSFlagFall'")
            ALSKM = DLookup("Value", "TblDataTypes", "Datatype = 'AmboLSKms'")
    
    debug.print "ALSFF is: " & ALSFF & vbcrlf & "ALSKM is: " & ALSKM
    I've never seen code like that to update a table. It actually does?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 2
    Last Post: 05-07-2015, 02:55 PM
  2. Update combobox selection for new entries
    By Pegasus in forum Access
    Replies: 1
    Last Post: 03-23-2015, 10:15 AM
  3. Update table entries and values from form
    By Master Klick in forum Access
    Replies: 0
    Last Post: 03-15-2015, 11:49 AM
  4. Replies: 1
    Last Post: 11-18-2013, 06:33 PM
  5. Replies: 1
    Last Post: 01-21-2013, 12:15 PM

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