Simple combo box solution, difficult problem description

[etalent]

I've got a little database for English, Spanish, and Portuguese verbs, which is comprised of 4 related tables and a form. The form has text boxes and 2 combo boxes. Rather than try to explain all that from the bottom up (as I did on my first try to get help on this), I'm just providing an explantion of the desired functionality (see following), and I'm attaching the database itself.

A little Spanish/Portuguese lesson first: The majority of the infinitive verb forms in Spanish and Portuguese may be grouped as ending in "-ar," "-er," or "-ir," for example "to walk" (andar), "to eat" (comer), "to lie" (mentir).

Let's say you need to enter a new infinitive. First you'd enter an infinitive in each of the text fields labeled "English," "Espanol," or "Portugues." Then, based on the ending (conjugation) of the infinitive, you'd select the model conjugation from the combo box labeled "conjugacion."

The model conjugation for "-ar" endings is "hablar." The model conjugation for "-er" endings is "comer," and the model conjugation for "-ir" endings is "mentir."

Let's say I want to enter a new record for the infinitive "to walk," which is "andar" in both Spanish and Portuguese. Notice the "-ar" ending (conjugation). The infinitive "andar" has the "-ar" conjugation. Here is the functionality I want:

1. Launch form "frmVerbs"

2. Click the ">*" button to enter a new record.

3. Enter "to walk" in the text box labeled "English"

4. Tab to the text box labeled "Espanol." Enter "andar"

5. Tab to the text box labeled "Portugues." Enter "andar"

6. Click the carrot of the combo box labeled "Conjugacion." Select "hablar"

Here what is supposed to happen now: the text boxes labeled "Present Participle," "Past Participle," and "Imperative" are supposed to be populated with the respective forms of the infinitive "hablar," which has the "-ar" ending (conjugation).

Here is another example, picking up from step #3 from before.

3. Enter "to dress" in the text box labeled "English"

4. Tab to the text box labeled "Espanol." Enter "vestir"

5. Tab to the text box labeled "Portugues." Enter "vestir"

6. Click the carrot of the combo box labeled "Conjugacion." Select "mentir"

Here what is supposed to happen now: the text boxes labeled "Present Participle," "Past Participle," and "Imperative" are supposed to be populated with the respective forms of the infinitive "mentir," which has the "-ir" ending (conjugation).

The whole problem is in the combo boxes. That's all I need help with!
Please help ASAP!

-Dave

 

[charityg]

It needed a rework

If I could have told you how to make your combo box work without restructuring the database, I definitely would have done that. Unfortunately, your tables structures were very unnormalized. I'm attaching a copy of the db with the new tables, form, and subforms, but I also want to explain why I changed things, and how it works.

First I created a table just for the conjugations. The fields include the ConjugationID, the LanguageID for the conjugation (1 for Espanol, 2 for Portuguese), the Conjugation verb, and then columns for Past, Present, and Imperitive. This table should only have the conjugations that you would like to reference for your verbs (NOT all of the verbs).
The second table created was a table specifically to store the verbs and the conjugations associated with them. The fields are VerbID (autonumber), English, Espanol, and Portuguese translations, and the associated ConjugationID from the Conjugation table.

I created a new Main form frmVerbsNew, and added subforms for each language tab. The combo for the Espanol subform is limited to only conjugations with a LanguageID=1 and the Portuguese subform combo is limited to only conjugations with LanguageID=2. Each subform has a datasource of the Conjugation with the same LanguageID filter. The reasoning behind the design is that the Main form will handle the verbs and the subforms will handle the conjugations. ConjugationID links the Main and subforms.

On the main form, there is a hidden field for storing the ConjugationID for each verb. In the afterUpdate event of the combo box the selected ConjugationID from the combo is stored in this hidden field on the main form.

The last piece is setting the combo box to display the currently saved Conjugation for each verb as you scroll through the verb records on the main form. This is done in the OnCurrent event of the main form.

I know you thought it was a simple problem, but the root of your issue was in the database design. Look it over and let me know if you have any questions.
Hopefully, it will make sense.