Scenario Involving 3 Tables / Forms

[wilderfan]

I'd like to describe a scenario involving 3 Tables / Forms and ask a few questions. I think the answers may involve VBA code, which I am still trying to learn. Here's the scenario:

TableA has 2 fields: PK field is populated with autonumbers which I will call PKA. The 2nd field is a text field which I will call TextA.

TableB has 2 fields: PK field is populated with autonumbers which I will call PKB. The 2nd field is a text field which I will call TextB.

TableAB is an intersection table with 3 fields: PKA, PKB and a text field which I will call TextAB.

Let's assume that the end user has poplulated TableA and TableB through the use of some simple forms: FormA and FormB. TableAB remains unpopulated at this point.

The end user wants to be able to do the following:

Start within FormA and select a particular record through the use of a combo box which shows the TextA entries.

Then go to FormAB (closing FormA at the same time) and have FormAB show the selected TextA entry previously selected from the FormA combo box.

If possible, FormAB would show TextA, a combo box for TextB (with records coming from TableB) and TextAB. A Save command button would be placed on the form.

The objective is to allow the end user to show the relationship(s) between TextA and TextB, but save the PKA associated with TextA and the PKB associated with TextB in TableAB through the use of FormAB.

Questions:

Does TableAB need to include additional fields for TextA and TextB? Or can we get by with just the PKA, PKB and TextAB fields?

Does FormAB need to show PKA and PKB (in addition to the user friendly fields TextA and TextB)?

Once the end user has shown what TextB record is associated with the TextA record (carried over from FormA) and possibly entered some text into the 3rd field (TextAB), how can we ensure that PKA, PKB and TextAB is saved in TableAB?

This scenario is beyond my capabilities right now, so any assistance would appreciated very much.


-- Wilderfan

 

[MStef]

What is the PRIMARY KEYS in your tables ??
What is the RELATIONSHIPS ??

 

[wilderfan]

For Table A, the primary key is the field PKA

For Table B, the primary key is the field PKB

For Table AB, the primary key is concatenated: both PKA & PKB

Table A has a 1 to many relationship with Table AB

Table B has a 1 to many relationship with Table AB

 

[georgedwilkinson]

Does TableAB need to include additional fields for TextA and TextB? Or can we get by with just the PKA, PKB and TextAB fields?

No. Just use a combo box on the AB form with its source the query of the 2 fields in either A or B.

Does FormAB need to show PKA and PKB (in addition to the user friendly fields TextA and TextB)?

No, the sub-form does not need to show PKA or PKB at all, just reference them in the combo box.

Once the end user has shown what TextB record is associated with the TextA record (carried over from FormA) and possibly entered some text into the 3rd field (TextAB), how can we ensure that PKA, PKB and TextAB is saved in TableAB?

Just use the form wizard and base the sub-form on TableAB. Access will deal with saving changes to the data.

This scenario is beyond my capabilities right now, so any assistance would appreciated very much.

You should really work through several of the Form tutorials on the Office site. Especially those dealing with sub-forms. There is a scenario in the tutorials that includes taking the subform onto a separate form.

Also, do some research on how combo-boxes work.

 

[Pat Hartman]

I would create form ab as a subform of a or b whichever makes the most sense. The subform would show only a combo listing the values of tblB and the text box textab.

You could also have a subform for ab on form b. In this case, the subform would show a combo for the values of tblA and the textboxab.

As long as the master/child links are properly set, Access will populate the "missing" FK field in the subform.

 

[MStef]

I don't know your task, but it seems to me that your tables not normalized well.

 

[georgedwilkinson]

I don't know your task, but it seems to me that your tables not normalized well.

Actually, they are at the height of normalization and simplicity. They are reminiscent of the long hours I spent trying to learn how Oracle Designer worked with Java.

I think wilderfan is just trying to figure out how all this stuff works together. I remember having a hard time adjusting to Access from an Oracle/Java background.

wilderfan, just try making a form/subform combo with the form wizard, include table A (or B) and table AB and let table A represent the main form. I think you'll enjoy the results.