Populating a subform with relevant records (1 Viewer)

I wonder whether someone might be able to help me with the following, please.


In my Access database, I have two tables that are related to each other. tblReferences holds data on various potential reference information which may relate to my projects.



tblReferences : ReferenceID [primary key], Details


tblProjects : ProjectID [primary key], ReferenceID, ......and some other fields realting to my projects.



I've set up a one-to-many relationship between tblProjects and tbleReferences (ie each Project can have multiple references, one reference, or no references), with referential integrity.


I already have a few hundred records in tblReferences. And I have a couple of hundred project records. I'm trying to now link the relevant references records to the relevent Projects, via a subform, but am having problems. I've tried an unbound combo-box (with the data source being a query including ReferenceID and Details) in the subform, and - based on my selction in the combo-box, tried to populate ReferenceID and Details fields in the subform. With my latest attempts, I'm getting an errror-message telling me that I can't create a subform record in that way because it'll create duplicate records.

Can anyone help with this ?

alan2013 said:

I'm trying to now link the relevant references records to the relevent Projects, via a subform, but am having problems.

Click to expand...

Not sure why you are using a combobox, I've probably misunderstood...

Have a look at the synchronization video on my Nifty Access website here:-

http://www.niftyaccess.com/many-to-many-relationship/

It may lead you to a solution, or possibly more questions...

Sent from my SM-G925F using Tapatalk

your mainform should be bound to tblReference.
combobox is bound to ReferenceID of tblReference (you can choose to make it unbound if you need to).

your subform is bound to tblProjects.
create a Master/Child Link fields on this subform:

Master Link field: ReferenceID
Child Link Field: ReferenceID

you don't need to put the combo in the subform.

There should be no issue with having the main form unbound, I personally do exactly what you describe most of the time. I rarely have the user add main form information and subform information on the same page. However, you have an an issue in either the table structure or your link criteria. If you put a combo box on an unbound main form then you linke criteria is

Thanks to all of you for your responses. Much appreciated. arnelgp and MajP, I'm afraid I didn't see your posts until after I'd tried a few things suggested by Uncle Gizmo. I have further work to do on the method suggested by Uncle Gizmo, but it's showing signs of working just as I'd hoped for. With the benefit of hindsight, the method I'd devised and tried was 'wide of the mark'.
Again, Thank You to you all.
Best wishes

alan2013 said:

tblReferences : ReferenceID [primary key], Details

tblProjects : ProjectID [primary key], ReferenceID, ......and some other fields realting to my projects.

Click to expand...

The above information describes a relationship where one reference might have many projects.

alan2013 said:

I've set up a one-to-many relationship between tblProjects and tbleReferences (ie each Project can have multiple references, one reference, or no references), with referential integrity.

Click to expand...

This verbal description is about a relationship where one project has many references, which is not supportable using the structure described above. If you want one project to have many references you have to do...

tProject
ProjectID (PK)
Details

tReference
ReferenceID (PK)
ProjectID (FK)

Click to expand...

hth
Mark