Setting rates from a table vs code (2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

>>>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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

>>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?

Reply | Quote

>>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????

Leesha

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Don’t worry the hesitation, Charlotte is absolutely brilliant when it comes to explaining terms, etc.

BTW, did you solve your problem?

Reply | Quote

>>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

Reply | Quote

Have you tried compacting then zipping it?

Reply | Quote

Yep. Just removed a few more tables after removing their relationships and am now down to 122 KB. Will try converting it again.

Leesha

Reply | Quote

OK, here ya go. You need to use frmInvoiceAccountName to open frmInvoice. You enter the date, the select the the job description, at which point Labor Charge should fill in with the corresponding rate.

Don’t kill yourself on this tonight (or is it daytime for you?) I’m gonna hit the sack. I’ve been at this all day and am blind. Tis not a good thing that I find this soooooooooooooo addicting!!!!!!!

Thanks for everything,
Leesha

Reply | Quote

In frmInvoice replace the DLookup command in the AfterUpdate event of JobDescription with:

Private Sub JobDescription_AfterUpdate()
LaborCharge = DLookup(“JobCost”, “tblJobCost”, “JobDescription = ” & Chr(34) & Me!JobDescription & Chr(34))
End Sub

I also noticed there was a missing Option Explicit after the Option Compare Database statement at the start of the code. This extra instruction makes you define all variables that you use.

Reply | Quote

Cool!!! That worked beautifully!! Thank you so much.

As for option explicit………….uhm I remember reading that was important and why when I first started working in Access. However, since I got so many erro messages asking me define stuff that I didn’t know what was being referred to, I decided to go without it. I can now say, that after the past few months of wonderful help on this site, I feel a tad more comfortable using it. I PROMISE to use it with my next database!!!

Thanks again for everything. Now to try to slow down my brain and get some sleep. I’m still trying to figure out the best way to assign invoice numbers without having to had enter them. Using the autonumber field won’t help in this case.

Adios,
Leesha

Reply | Quote

I thought you had already gone bye byes, but seeing as you are still up (it’s onlt 1.50pm here) I use a special table that holds a long integer number, call it NextAvailableInvoiceNo, and just prior to using it (as in the BeforeUpdate event of the invoice form I put the following:
Dim dbs as DAO.Database, rs as DAO.Recordset, lngInvoiceNo as Long
Set dbs = CurrentDB
Set rs = dbs.OpenRecordset(“Control”)
rs.Edit
rs!NextAvailableInvoiceNo = rs!NextAvailableInvoiceNo + 1
lngInvoiceNo = rs!NextAvailableInvoiceNo
rs.Update
textInvoiceNo = lngInvoiceNo

textInvoiceNo is the name of the text control on the form that holds the Invoice number.

Reply | Quote

I am trying to go to bed but alas my mind is racing with code and ideas. I should know better than to program before I go to bed but I never take my own advise. It’s only midnight here. Have to get in 6 hours to go to my “real” job. Ugh!

Thank you for the idea on invoicing. I started to read it and than thought oh no, this is the next test. Now I’ve gotta figure out what it means. It’s the nurse in me. I have this NEED to understand what the code says and does, not just simply pick the brains of those of you who post. I really have learned sooooo much. BUT, this one will take me some deciphering!!!!

I’m sure to be back with questions.

Leesha

Reply | Quote

To save you a bit of time, I will put comments in the code so you can copy and paste into your form’s code area.
The solution to get the next invoice is:
‘ When you are going to use recordsets in a DAO environment, you first declare the database and recordset variables
Dim dbs as DAO.Database, rs as DAO.Recordset, lngInvoiceNo as Long
‘ Set the dbs to point to the current database, the one that we are in.
Set dbs = CurrentDB
‘ Set the rs to point to the recordset defined by the table Control
Set rs = dbs.OpenRecordset(“Control”)
‘ When you open a recordset it will read the first record in that table (Control must always have only 1 record in it).
‘ The .Edit command sets the recordset into edit mode for the record
rs.Edit
‘ Add 1 to the Next available invoice number
rs!NextAvailableInvoiceNo = rs!NextAvailableInvoiceNo + 1
‘ Save this number in a local variable
lngInvoiceNo = rs!NextAvailableInvoiceNo
‘ Update the table via the recordset
rs.Update
‘ Save the next available number in the text box called textInvoiceNo (this should be defined on the form).
textInvoiceNo = lngInvoiceNo
‘ Clear these 2 objects else memory leaks will occur
Set rs = Nothing
Set dbs = Nothing

Hope this helps.

Hope I have explained it well enough. Now go to sleep, 6 hours before you are up again, wow.

Reply | Quote

I’m going to be really I am. But, since I’ll want to know this in the am when I try to figure this out (may take me weeks – DAO????)

How do you account for invoices that hold a date range. For example, sometimes the user prints the invoice right then and there on the spot. Other times they send a monthly invoice covering a range of dates, depending on the wishes of the account. Invoice numbers would then be used to be able to call up an invoice (based on the number) in the event that the Account needs to have an original one regenerated. Do you use the same process??

I’m now REALLY shutting down my laptop and going to bed!! May not sleep, but have to at least give it a shot or I’ll be at this all night!

Thanks,
Leesha

Reply | Quote

>>I’m now REALLY shutting down my laptop and going to bed!! May not sleep, but have to at least give it a shot or I’ll be at this all night!<>How do you account for invoices that hold a date range. For example, sometimes the user prints the invoice right then and there on the spot. Other times they send a monthly invoice covering a range of dates, depending on the wishes of the account. Invoice numbers would then be used to be able to call up an invoice (based on the number) in the event that the Account needs to have an original one regenerated. Do you use the same process??<<

What do you mean by "invoices that hold a date range", do you mean that when the user invoices the client it may be for 1 or more invoices over a period of time (say a month)?
I don't understand the rest of the sentence, re having an original one generated.

Happy zzzzzsss

Reply | Quote

Morning Pat!

THANKS for the code explanation. It’ll save me time!

>>This makes me chuckle, I used to do this in the good ol’ days. I’m too old for that sh..t now.

Well I’m not so young, but I’ve got the energy and stamina of 2!! Either that or I’m nuts. Could be debatable.

>>What do you mean by “invoices that hold a date range”, do you mean that when the user invoices the client it may be for 1 or more invoices over a period of time (say a month)?
I don’t understand the rest of the sentence, re having an original one generated.

Maybe I should be calling it the final bill vs invoice. The user at times generates a “bill” for a one time occurance such as sewing laterations. At other times they need to generate a bill that covers many occurances, as for a months period etc. They want to be able to track the “bill” which they refer to as an invoice, by number so if the customer looses the bill or needs a new one, they can regenerate the original by putting in the bill number and thus reprinting the original bill without any credits applied. They need the ability to do this so that if they have to rebill on partial payment situations, the rebill has its own number on it.

I’ll bet you wish you didn’t ask!

Sleepy,
Leesha

Reply | Quote

Is there a special reason why you have separate tables tblJobCost and tblJobDescription? It seems to me that you can put the JobCost field in tblJobDescription.

Since you don

Reply | Quote

>>Note: the reason that you had so much trouble getting the zipped database below 100 kb was that you left many queries and reports in the database that are not relevant to the problem at hand.

AH jeeze!! I forgot those were in there!!! Sorry.

>>Is there a special reason why you have separate tables tblJobCost and tblJobDescription? It seems to me that you can put the JobCost field in tblJobDescription.

You’ll be proud. I figured that one out on my own after Pat sent me the code to get the database to find the rate!!! Figured it was redundant to have both.

>>Since you don

Reply | Quote

Pat’s code is excellent (as usual), and there is no objection at all against using it. I just wanted to point out that it is possible to retrieve the value without any code.

Reply | Quote

Can’t wait to get a minute to study it. I love having more than one option!

Leesha

Reply | Quote

Morning Hans,

I finally had a free minute to look at how you set up that query to look back and find that corresponding rate. That was really cool and another learning experience! I’ve never used a query that way before and now of course my mind is racing with the possibilities!! You may regret having showed me this tee hee. To think how easy both your and Pat’s solution were compared to the example in the resource book I was using!!!! Yikes.

As always, Thank you!!!

Leesha

Reply | Quote