Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2011
    Posts
    47

    Unanswered: Need a field to change depending on a value in a different field?

    Hi,

    I have a form called frmlegalaidledger.
    In the form i have 5 fields in the following order:

    cboCategory cboCT cboRates Activity Cost

    I need to be able to do the following:

    I need to be able to pick a category in the cboCategory box, then i need to be able to pick a type of court from the cboCT. Then i need the appropiate rate according to the two fields selected before.
    These fields are based on tblCostRates.

    I had a formula where you would pick the category and then the appropiate rates would show and you could pick the rate you want. However I had to add in the court type and i cannot seem to get it working right.
    The code i had is:

    Dim sSource As String

    sSource = "SELECT tblCostRates.WorkRatesID , tblCostRates.WorkCategoryID, tblCostRates.Code, tblCostRates.Activity, tblCostRates.Rates " & _
    "FROM tblCostRates " & "WHERE [WorkCategoryID] = " & Me.cboCategory

    Me.cboRates.RowSource = sSource


    Can anyone help?
    thanks in advance
    Last edited by rasghar; 07-11-11 at 11:29.

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    If court type is a governing factor in the rates, it seems to me that you need to add the court type into the tblCostRates table, with costs, rates and maybe activities differing by court type. You can then finish the SQL statement by adding the court type into the select clause as well as filtering it in the where clause.

    Sam

  3. #3
    Join Date
    Jul 2011
    Posts
    47
    Quote Originally Posted by Sam Landy View Post
    If court type is a governing factor in the rates, it seems to me that you need to add the court type into the tblCostRates table, with costs, rates and maybe activities differing by court type. You can then finish the SQL statement by adding the court type into the select clause as well as filtering it in the where clause.

    Sam

    Hi Sam,

    thanks for your reply,

    I have already added in court type in the tblcostRates,
    there are two governing factors, the cboCategory and cboCT(court type).

    Could you please give me sample code please?
    I have very little experience with SQL.

    For example, a cbocategory maybe Children and then the court type maybe high court.


    Thanks for your reply, I really appreciate it.

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    sSource = "SELECT tblCostRates.WorkRatesID , tblCostRates.WorkCategoryID, tblCostRates.Code, tblCostRates.Activity, tblCostRates.Rates " & _
    "FROM tblCostRates " & "WHERE [WorkCategoryID] = " & Me.cboCategory
    I would change as follows, assuming the table field name for court type is CourtType:
    Code:
    sSource = "SELECT tblCostRates.WorkRatesID , tblCostRates.WorkCategoryID, tblCostRates.Code, tblCostRates.Activity, tblCostRates.Rates, tblCostRates.CourtType " & _
    "FROM tblCostRates " & "WHERE [WorkCategoryID] = '" & Me.cboCategory & "' And [CourtType] = '" & Me.cboCT & "'"
    (By the way, your format assumed that WorkCategoryID is numeric. Since it's string, you need to alter the statement a bit, as I've done.)

    In order to make this whole thing work, you need to put this statement into an event of the form. For example, have a command button (maybe called cmdConfirm; the text of the button might be "Confirm"). The user would select a category/court from the two combo boxes, and then click on cmdConfirm, which would confirm - meaning that you are satisfied with them - all the selected values and "lock them in". The Private Sub cmdConfirm_Click event would then house the SQL statement above, and read the cb's and fill in the other controls.

    Sam

  5. #5
    Join Date
    Jul 2011
    Posts
    47
    Quote Originally Posted by Sam Landy View Post
    I would change as follows, assuming the table field name for court type is CourtType:
    Code:
    sSource = "SELECT tblCostRates.WorkRatesID , tblCostRates.WorkCategoryID, tblCostRates.Code, tblCostRates.Activity, tblCostRates.Rates, tblCostRates.CourtType " & _
    "FROM tblCostRates " & "WHERE [WorkCategoryID] = '" & Me.cboCategory & "' And [CourtType] = '" & Me.cboCT & "'"
    (By the way, your format assumed that WorkCategoryID is numeric. Since it's string, you need to alter the statement a bit, as I've done.)

    In order to make this whole thing work, you need to put this statement into an event of the form. For example, have a command button (maybe called cmdConfirm; the text of the button might be "Confirm"). The user would select a category/court from the two combo boxes, and then click on cmdConfirm, which would confirm - meaning that you are satisfied with them - all the selected values and "lock them in". The Private Sub cmdConfirm_Click event would then house the SQL statement above, and read the cb's and fill in the other controls.

    Sam


    Thanks for your help Sam, it worked

    thanks

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Wonderful!

    You are welcome.

    Sam

Posting Permissions

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