table joins

[krymzon]

i'm doing a many to many table join using a 1 to many relationship for each table and having a joining table in between each table. its not a hard concept, but i'm having problems creating a form that updates my join table fields according to the fields in the other tables that need to be joined...if you have any idea at what i'm asking for any advice would be great, i just started to use access now and in the past have been more familar with SQL, so this is a bit of a platform change for me, so any advice would be great.
thanks

there are 10 people in this world. those who know binary, and those who don't.

 

[grande]

Can you give me some more information? For example, is the user entering this information in a form, or are you just trying to send this information from two tables into three?

 

[krymzon]

ohhh, never mind, i just figured it out but thanks for the response...i just had some bad syntax in my VBA code.

<i>there are 10 people in this world. those who know binary, and those who don't.</i>

 

[grande]

No problem, glad to hear you got it working!

-------------------------
Just call me Captain Awesome.

 

[willir]

As a follow up, instead of using VBA code to update the tables, another approach is to use a combo box within the subform.

Assumptions:
Table1
Table1PK
Table1Description

Table2
Table2PK
Table2Description

tblJoiner
Table1PK
Table2PK

primary key = Table1PK + Table2PK

Approaching the data from Table1
Main form based on Table1
Subform based on tblJoiner
- LinkMaster and LinkChild fields based on Table1PK
- On subform,
--- Contineous
--- ControlSource: Table1PK - text box, hidden
--- ControlSource: Table2PK - combo box,
RowSource: SELECT Table2PK, Table2Description FROM Table2
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0";1"

Approaching the data from Table2 (swap Table2 for Table1)
Approaching the data from Table2
Main form based on Table2
Subform based on tblJoiner
- LinkMaster and LinkChild fields based on Table2PK
- On subform,
--- Contineous
--- ControlSource: Table2PK - text box, hidden
--- ControlSource: Table1PK - combo box,
RowSource: SELECT Table1PK, Table1Description FROM Table1
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0";1"

Although this may seem like a lot of work, it is actually a very powerful approach in that you can easily switch from one view to the other.

For example, a M:M between products and suppliers. You can view which suppliers supply a product, and easily switch to what products a supplier produces.

Richard