Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Setting rates from a table vs code (2000)

    Hi Again,

    The database I'm working on presently has preset rates for the work done. The rates presently are stored in code that is executed when Jobdescription is updated. For example, if the jobdescription = sewing then LaborRate automatically fills with the associated rate. This works fine. The problem is that every time the user changes rates or adds a new catagory to the JobDescription either the code needs to be changed or they have to type in the associated rate. I'd like to be able to have LaborRate fill by looking back to tblJobDescription (which contains the JobDescription in column 1 and the rate in column 2). This way the user can make changes to tblJobDescription without having to worry about making changes to code. Is this even possible?

    Thanks,
    Leesha

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Setting rates from a table vs code (2000)

    Do you mean that when you add a rate to the tblJobDescription table the combobox (I'm presuming this) does not show the latest addition.
    If this is the case then just put a requery in the GotFocus of the combobox.

    If I'm way off target, please enlighten me, and explain the problem in more detail.

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Setting rates from a table vs code (2000)

    Hi Pat,

    No, that isn't the problem. I've set it up that way as well as with code that runs after JobDescription is updated to show the corresponding rate in LaborCharge. Both ways work fine. What I'm looking to do is to have the user not have to select a rate from a combo box at all, but to have LaborCharge automatically be filled in by looking back to the tblJobDescription which contains both the rate and the JobDescription.

    I was reading in a book where this an be done using DLookup but for the life of me can't quite figure it out and wondered if there was an easier way.

    Leesha

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Setting rates from a table vs code (2000)

    So you are trying to fill a control on a form with a rate based upon a control on the form that has the JobDescription, is this right?

    If so, then put the following in the control source:
    =DLookup("LAbourCharge","tblJobDescription ","JobDescription=" & Chr(34) & Me!JobDescription & Chr(34))

    I have assumed that the field JobDescription is in the table tblJobDescription, if not, you will have to change it to your field name.

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Setting rates from a table vs code (2000)

    >>>So you are trying to fill a control on a form with a rate based upon a control on the form that has the JobDescription, is this right?

    Almost. I am trying to fill a control by having the control look back to a table (tblJobCost) ............(I think I referred to it as tblJobDescription in my previous post)...............

    Leesha

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Setting rates from a table vs code (2000)

    What field in the form are you using to look back at table tblJobCost, also what is the name of the field in the table you are matching this field name with?
    What is the field name of the Rate in the table tblJobCost?

    With these questions answered you should be able to make up your DLookup command.

    If you have problems, then please post back.

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Setting rates from a table vs code (2000)

    Since this is the first time I've tried this, I'm lost. IE: where do I place the code? Do I place it on the control itself and if so on what event? Or do I place it on the JobDescription control (which preceeds it on the form). My goal, is that after the user selects the JobDescription, the LaborCharge will automatically be updated. So, with this in mind, do I put the code (whatever that may be) on the afterUpdate event of JobDescription?

    To answer your questions:

    >>What field in the form are you using to look back at table tblJobCost,
    LaborCharge

    >>also what is the name of the field in the table you are matching this field name with?
    JobDescription.................presently there are multiple records in this column, IE Sewing, Decorating, Consultation, Other etc.

    What is the field name of the Rate in the table tblJobCost?
    JobCost................the costs for the above mention records are in this column.

    Thanks for your help,
    Leesha

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Setting rates from a table vs code (2000)

    >>Do I place it on the control itself and if so on what event? <<
    You place the code in the Control Source, not in any event.

    From what you have described try the following in the Control Source of the LabourCharge:
    =DLookup("JobCost", "tblJobCost", "JobDescription = " & Chr(34) & Me!LabourCharge & Chr(34))

    Are you sure that LabourCharge is a control on the form that contains the description to lookup the table tblJobCost?

  9. #9
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Setting rates from a table vs code (2000)

    >>Are you sure that LabourCharge is a control on the form that contains the description to lookup the table tblJobCost?

    It is a textbook for a field from tblBilling. Not sure if that qualifies it as a control???? <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    Leesha

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Setting rates from a table vs code (2000)

    A control on a form is the same thing as a field on a form.
    Anyway, if what I gave you does not populate you may have to go into the AfterUpdate event of the description control and put the following in there:
    textJobCost = DLookup("JobCost", "tblJobCost", "JobDescription = " & Chr(34) & Me!LabourCharge & Chr(34))

    The variable name textJobCost is assumed to be the control name that you want to show the rate.

    If you have anymore problems, post a zipped version of your database and I will make the changes and describe what I have changed in a return post.

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Setting rates from a table vs code (2000)

    Controls are objects on a form or report--textboxes, checkboxes, listboxes, comboboxes, commandbuttons, etc. Data controls like textboxes, comboboxes, and so forth, can be bound to a field in the underlying recordset of the form or report. Strictly speaking, fields are NOT the same thing as controls, but it isn't unusual for the terms to get interchanged. A field belongs to a table or query or recordset, and contains data. The control that is bound to that field is a window into the data it contains.
    Charlotte

  12. #12
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Setting rates from a table vs code (2000)

    You guys are certainly saints to have such patience. Thanks for the class. I REALLY hesitated in answering whether I was using a control or not.

    Leesha

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Setting rates from a table vs code (2000)

    Don't worry the hesitation, Charlotte is absolutely brilliant <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> when it comes to explaining terms, etc.

    BTW, did you solve your problem?

  14. #14
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Setting rates from a table vs code (2000)

    >>Don't worry the hesitation

    Thanks!! I really hate to have people think I'm a total idiot. And you're right re Charlotte's explanations!! I wish the books were so clear.

    I tried the code and it didn't change anything. I even tried changing textJobCost to textLaborCost since that's the name of the text box and I thought that is what you might mean, but still it didn't work. I'm presently trying to get the database small enough to post it to you. Can't get it below 178KB. I've removed all the unnecessary tables as well as all but one Account. I tried to convert it back to 97 as Hans had me do with another database once before but I get compile errors. Any suggestions?

    Leesha

  15. #15
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Setting rates from a table vs code (2000)

    Have you tried compacting then zipping it?

Page 1 of 2 12 LastLast

Posting Permissions

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