Results 1 to 15 of 15
  1. #1
    Join Date
    Nov 2011
    Posts
    413

    Unanswered: Sales Commission Rate Problem

    I have a master form named TimeCards, and on it are many fields.
    One is a combo that looks up the Employee and his commission rate
    It is named SalesEmpID and gets its info from a table/query with 2 tables in it.
    Employees and TempOrCon

    I also have a bound control on the form which gets its data from the Time table and it is named SalesCom, it has no set default other than 0.
    SalesCom is used in the payroll function of the database to calculate the employees commission for that particular payroll time period.

    I am confused as to what to do here. I cant really change the control source of SalesCom since it will throw off my payroll.
    I tried using the default for it with =SalesEmpID.Column(2) which comes from the combo lookup named SalesEmpID, I have another unbound textbox named
    TextBox500 which stores the value of the combo sales commission rate
    =SalesEmpID.Column(2)

    Any suggestions as what to do so that SalesCom will use the value that SalesEmpID looks up and not change its control source?
    Attached Thumbnails Attached Thumbnails TimeCard.png  

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I'm confused because you don't say what the problem is, or if you're getting any error messages.

    Also, are you aware that Column(2) is really looking for the 3rd column? Column() is a zero-based array.

    Sam

  3. #3
    Join Date
    Nov 2011
    Posts
    413
    No error message.The SalesCom is set at 0 and right now has to be set manually.
    I am trying to figure out a way to automate this so that when a employee is selected via the SalesEmpID Lookup Combo, it would fill in the SalesCom field. I can do this by setting an unbound textbox of course which is what I have done and it works but even if I set the default value of the SalesCom field to this Textbox, nothing happens. I wrote this database a long time ago when my brain actually worked a little. It started out in Access 2.0

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Is the second technique here what you're after?

    Autofill
    Paul

  5. #5
    Join Date
    Nov 2011
    Posts
    413
    Yes, but I have figured that out. Here are some pics that will hopefully shed some light on the problem.
    Attached Thumbnails Attached Thumbnails QueryforEmployeeLookup.png   SalesCom.png   SalesComTextBox.png  

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Using the default value won't work because no selection will have been made in the combo when the record is first created.
    Paul

  7. #7
    Join Date
    Nov 2011
    Posts
    413
    The Lookup does get the commission value correctly and the unbound textbox displays it as well. If I manually enter a commission rate all is well, just that I can figure a way to automate this.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    personally I'd bung the commission rate into the combo box as a hidden column and then pull that value as required as part fo the on click event

    As an aside
    I seriously hope I never have to use an application designed by you matey. those forms are confusingly tightly packed. If all that is coming from one table then I suspect there are some serious design flaws in that schema
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Nov 2011
    Posts
    413
    Yeah, Thanks. It is a very complex form and NO I dont have any training. But it does work believe it or not.

    Thanks,

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Quote Originally Posted by Burrina View Post
    If I manually enter a commission rate all is well, just that I can figure a way to automate this.
    Did you see the second method in the link:

    Me.TextboxName = Me.ComboName.Column(2)
    Paul

  11. #11
    Join Date
    Nov 2011
    Posts
    413
    Yes, Thanks. For now I use a msgbox when the user chooses a employee to pay commission to and then have them click on the SalesCom field and this works.

    The DB is extremely complex and date/time sensitive and because of the nature of the requested way for it to perform I wound up with this. Now would I have designed it differently knowing what I know, of course. But it works and it quite idiot proof.
    Thanks again, I think I will leave well enough alone and call it a day.

    Cheers

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you stuff the commission rate into the combo box that the user selects the employee form then it means there is no further user interaction. selecting an employee should then also trigger the commission calculation . this is what pbaldy is referring to
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Nov 2011
    Posts
    413
    I did stuff it into the lookup but it uses different tables than the one on the main form.
    The main form uses the Time table and of course the lookup uses the Employee table.
    Other code is already being run using the SalesCom control source so this cant be changed.
    You would probably have to see the db and let me explain to get it.

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    do you have a combo with employees on
    if so add the commission to that combo box as a hidden column
    ....
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Nov 2011
    Posts
    413
    Yes I did and it works but that does not solve the problem, I will give it some
    more thought

Posting Permissions

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