Joined table problem

[Lukael]

Hello,

I have joined table and when I create a subform for main form and enter data, this record is being also added in 3rd table, not just linked table...

My tables:

Programme
ProgrammeID(PK)
Programme name

Programme-Location
ProgrammeId(FK)
LocationID(FK)


Location
LocationID(PK)
Address

My subform is created through query of ProgrammeID from table Programme-Location, and Address from table Location.



When I enter data in subform, records are also saved in LocationID(PK), which is not OK since in my subform there is a combobox where records from Address are shown, so they increase their value. I also
receive Error : "cannot add record, join key of table is not in recordset" What am I doing wrong ?

[ranman256]

you dont need tLocation in the join. That is just a lookup table to fill the field.

[Lukael]

Hi ranman,

then how must I create join table ? I still need a relationship to between tables, I have many to many right now.

[ssanfu]

Not quite sure what is wrong with your form/subform.

Maybe this example will help.
You can add new programmes in the main form.
Have to use a different form to add new addresses to the address table or edit the table directly.

BTW, this is in A2K format......

[Lukael]

Hi ssanfu,

Thanks for providing sample, but this is not what I wanted, but I have managed to solve my problem.

My problem:

My main form is for entering programme names and It's location on PC. That's what you posted in sample, but my form starts with a blank table record, and you can only view addresses in Subform Comboboxes, from 3rd table.

So, If you have blank record, Autonumber is not set, and when entering in Subform you receive error :"Primary key cannot contain null value".

All I did was to set one textbox field .value="" in form's load_event and now everything works - all data is writen directly to junction table, without any errors and adding records in table Location.


Thanks for help !!

[ssanfu]

Would need to see your dB, but it sounds like the link between the main form/sub form are not set.

Great that you finally found the solution.