Enter data into one table while entering other data into another table

[hilian]

I have a database that tracks amounts spent for Interpretation services. There is a main table (tblInterp). Each row is an episode of interpretation. This table contains fields for case number, date of service, language and amount charged. There is also a table (tblLanguage) with two fields: language and Rate (the rate charged per hour of interpretation). The user enters data from a form that has a combo box for language, which uses tblLanguage as its row source.

What I want to do is allow the user to enter/modify the rate charged at the same time as data on individual episodes of interpretation are entered. For example, if the user needed to enter one hour of Mandarin (into tblInterp), but there was no rate in tblLanguage for Mandarin, the user should be able to enter the rate into tblLanguage in the same operation as entering that episode of interpretation into tblInterp. The rate would be entered into say, a text box and would be stored in tblLanguage and be available the next time an episode of interpretation for Mandarin was entered. the rate, itself isn't stored in tblInterp; the rate is multiplied by the number of hours from another text box, and that amount is stored in tblInterp.

I tried a text box whose content was governed by the language combo box. Wherever there was a rate for a language in tbllanguage, it showed in the text box, but I couldn’t make tblLanguage the control source and store the rate there. How else can I do this?

[June7]

Look at the NotInList event of combobox. Review http://support.microsoft.com/kb/197526

[hilian]

I read the article, "Use NotInList Event to Add a Record to Combo Box." That isn't exactly what I want to do. I don't want to add a new record to tlblanguage. What I want to do is fill in the Rate field in tblLanguage for the language selected in the combo box. There must be a way of doing this. Alternately I could redirect the user to a separate form bound to tblLanguage. I'd rather not to this if I can avoid it, since I think it's distracting to someone who is entering a lot of data. Also, if the user entered the rate via the separate form, would it immediately update the combo box or would it be necessary to close the original form and reopen it?

[Missinglinq]

...I want to do is fill in the Rate field in tblLanguage for the language selected in the combo box.

...if the user entered the rate via the separate form, would it immediately update the combo box or would it be necessary to close the original form and reopen it?

I think you're going to have to pop up a small Form to add the translation rate, since you're not talking about a language that's not in the list, but only part of that item's data. The alternative would be to not list a language until you have an established rate.

After adding the data to tblLanguage , you don't need to Close and then Open the Form, to have the new data appear, but simply Requery the Combobox.

Linq ;0)>

[June7]

If language is already in tblLanguage why doesn't it already have a rate?

[hilian]

Poppnig up a small form seems to be what I want to do. Thanks. Where do I put the requery statement? Do I put it in the command that closes the pop-up form, or elsewhere?

[hilian]

That certainly is the obvious question to ask. The the answer is that I'm adding an enhancement to the db. The table of languages already existed, and I added a field for rate. The schedule of rates exists on paper, but not in any form that I can use to import into a table--there are around 30 languages.

My choice was ether to type in the rates myself, or give them a form to enter them. I thought it might facilitate data entry if they could enter the rates as they went along. I didn't want them to think that couldn't enter any data until they entered all of the rates. I see that I have a choice: I can give them the pop-up form, or I can simply give them a form to enter all the rates before they begin. I suppose I could give them both, although I'm not sure there's a real need for it. The fastest thing might be to type in the rates myself, but they'll need a way to to change them in the future.

[June7]

Only 30 languages? And you have rate info? I would just go ahead and enter them into the table, takes maybe 2 minutes. Why make users do this work? And you've already spent way more than 2 minutes just trying to develop code to deal with this.

[Missinglinq]

...Only 30 languages? And you have rate info? I would just go ahead and enter them into the table...

I have to agree! This kind of scut work is simply one of the things developers do to facilitate matters for the end users! One of the basic precepts of programming is that the developer does something once so that the end users don't have to do the same thing over and over and over, again!

And, of course, it gives you control over the rate data that is entered! What if the first translator to want to use Mandarin enters an incorrect amount? Everyone who does Mandarin, thereafter, will be using the same, incorrect rate, and one of the two reasons to use a Combobox is to prevent incorrect data from being entered!

Linq ;0)>

[hilian]

Thank you June7 and MissingLinq,

I think I was over-thinking the problem. I'll type the rates in myself and give them a form for the supervisor to change them later when they need to.

[June7]

Keep in mind that if rate is changed in Language table, this change will be reflected in ALL existing related records.

[Missinglinq]

Keep in mind that if rate is changed in Language table, this change will be reflected in ALL existing related records.

Which is why I almost never use Bound Comboboxes! To work around this the Rate has to be assigned to a Textbox which is Bound to a Field in the Record's underlying Table. I believe the OP stated that this was done, at least in one of the earlier attempts at solving this problem.

Your revised plan sounds like the way to go, hilian! Running up a two-Field Form for the supervisors to use to update the Rates should be very simple to do.

Good luck with your project!

Linq ;0)>

[hilian]

Keep in mind that if rate is changed in Language table, this change will be reflected in ALL existing related records.

Thanks for warning me about a potential danger, but it's actually ok.

The language table isn't related to any other table. It just exists to supply the combo box. The languages from tbllanguage are stored in the main data table once they are entered from the combo box, and the rates are used to compute the amount charged, which is also stored in the main data table.

[hilian]

Missinglinq, Thanks for your suggestion.

I can set up the two-field form to enter rates and have it separate from the main data-entry form or I han have it as a pop-up that is accessable through the main form. I can also do it both ways. If I have it as a pop-up, where do I put the requery statement to update the rate on the main form?

hilian