join table form and main form

[Lukael]

Hello everyone,



I have to link two tables with many to many relationship. Then I want to create main form with subform, where subform would show items in combobox from 3rd table for Main form record.

In other words: When I open main form (based on 1st table), Autonumber field is ready to save record. In subform I want to have combobox with list of values from 3rd table, select It and then Access must link this Item to Main form record, automatically - via joined table.


HOW on Earth can I do this, I've tried everything I can possibly remember but I cannot make this happen

Please help !

[ItsMe]

...select It and then Access must link this Item to Main form record, automatically - via joined table...

I don't see this happening. If you have a junction table, create a query to display the relevant records. If you need to add new records, you can use a combo. However, you will need to use VBA to append records to your junction table as well as the other two tables (using the value of the combo and another PK).

[NTC]

the forms will do all the work; presuming you have a form for each table - and the tables all have the proper cross referencing key fields as is appropriate..... then when you inset the first sub form into the main form the wizard is going to prompt for cross reference unless you have already defined it in table relationships then it won't need to.

and likewise when you inset the 3rd form into the sub form - the same occurs....

the data will all be properly cross referenced.

[ItsMe]

the forms will do all the work;...

That is interesting, would you set the subform that is bound to the junction table to .Visible = False?

[Lukael]

Hi guys,

thanks for response. I have managed to figure out my problems. I'm not used to Access and It's "record based structure".

I did everything right, but couldn't figure why I can't enter data in subform. Solution was simple - I entered in subform on blank record, that's why I got error "Primary key cannot contain null value".

I just added this in Form's load and CreateNewRecord_Button:

Me.TextboxName.Value = ""

This creates new record Autonumber automatically, so you can write to Subform even on "blank" form, and records are saved in junction table, nowhere else.

P.S.: Yes, I have a Subform with a combobox directing to list items from 3rd table.


Thanks for all help to both of you !!!