Consulting

Results 1 to 3 of 3

Thread: Calculate an input field based on other fields

  1. #1

    Calculate an input field based on other fields

    Hi everyone, I'm sure this is simple, but I don't know how to approach it.

    I have a simple data entry form "NewEntry" that makes new entries to my master table "JobList".

    One of the fields is simply "Charge". This price varies depending on who the client is, and where the job is done.

    In the past, when my client list was very short and I worked for the same clients over and over, I simply manually entered it.

    Now, however, I've got a few new clients that are infrequent, so I'd like to have the field automatically enter my base price for me.

    I'm thinking some nested "CASE" Statements, but I'm unsure how to use them with fields from the form.

    For example (And I know this isn't workable code)

    Select Case "Client"
    Case "Exxon"
    Select Case "Port"
    Case "Oakland"
    NewEntry,Charge.Value=500
    Case "Seattle"
    NewEntry.Charge.Value=600
    End Select
    Case "Chevron"
    Select Case "Port"
    Case "Oakland"
    NewEntry,Charge.Value=450
    Case "Seattle"
    NewEntry.Charge.Value=650
    End Select
    Case Else
    Debug.print "Client not on file"
    End Select

    Is this a workable method, and if so, how to I go about getting actual results?

    If thats a bad way to approach it, How should I start?

    Thank,
    Richard

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    You could use this method, but it is not a good idea for a couple of reasons.
    The first being that you are ignoring the power of Relational tables, Queries and Combo boxes.
    The second is that the prices are "hard code" by you, which means that you have to keep the code up to date.
    So ideally you would have a price field in your table that holds price for the customer.
    When the customer is selected in the Combo the price field is included in the combo columns and can be displayed and stored if required or just used in calculations.
    I am not sure of your Table Relationships, so I am not sure where the price should be in a current table or a many to many table.
    You would then have a simple form for keeping the prices up to date or adding new ones.

  3. #3
    Yes, thanks, that makes more sense.

    I'll make a 'prices' table and go from there.

    Richard

Posting Permissions

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