Many-to-many relationship between form and subform?

[_Nickel_]

Many-to-many relationship between main form and subform?

I am working on a database to keep track of the hardware and the software inventory of the company I work for.
I've got a table for the PCs and a table for the software. These two have a many-to-many relationship via another table (b/c a PC can have more than one program installed and every program can be installed on many PCs).
I would like to create a form where the PC-table is the source for the main form and the software-table for a subform, so I can easily keep track of what software is installed on a PC. The problem is that I don't know how to make the many-to-many relationship to work for this.
Can anyone tell me how to do this?

Thx

 

[Mile-O]

Here's one subform example that might help.

 

[FoFa]

You really do not have a many to many. You keep excluding the middle table. You have a many to one and a many to one.

PCtbl - XREFtbl - HWtbl

Many entries in the XREFtbl to One entry in the PCtbl.
Many entries in the XREFtbl to One entry in the HWtbl.

This a join issue, not a many to many issue.

Base you subform on a query using the XREFtbl.

 

[Pat Hartman]

See the many-to-many sample db that I posted to see how forms are created.

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=40821&highlight=manytomany

 

[_Nickel_]

Thanks for the answers, I get the idea but I am still making a mistake somewhere as I can't get it to work.
These are three tables involved:

tblPCHardware
-PCID
-CPUType
-etc.

tblSoftware
-SoftwareID
-SoftwareName

tblPCHardwareToSoftware
-SoftwareID
-PCID

and the following query:

qryPCHardwareToSoftware
-tblPCHardwareToSoftware.PCID
-tblPCHardwareToSoftware.SoftwareID
-tblSoftware.SoftwareName

The main form is based on tblPCHardware and the subform has the PCID and the SoftwareName fields from the query.

Now when I enter a Software into the subform a new entry is created in the Software table even if I write it exactly the same way as an existing software

I have no idea what I'm doing wrong, plz help!

 

[Pat Hartman]

You don't need to join to the software table in this query. Use a combobox on the subform to select softwarename. The bound field of this combo will be the softwareID and that is what will be stored in the relation table even though the combo shows the name.

 

[_Nickel_]

Yeah that's how far I got myself by now by looking at your sample databases. What I can't figure out how I can get the SoftwareID field to display the SoftwareName instead of the ID. This is probably quite simple but I just can't figure it out
Thanks for your help!

 

[Tay]

So are you using a combo as Pat suggested? If so check the properties of the combo. As Pat said you need the bound column to be the ID.
In Properties, there should be one called Column Count, and this should say 2 (as you need both ID and Name), and your bound column will probably be column 1. So, where you have the Property Column Width (I expect it says 0; 2.54), change them round so that the bound column (column 1) is 0, and Column 0 (Name) is whatever width you like. HTH.

 

[_Nickel_]

Thanks, its working now. It is pretty simple but I would have never worked that out myself