Creating a data entry form from two tables with a many to many relationship

[LeighW]

Hi,

Another thing which I can't get my head around really.

Basically I'm trying to create a link so that when I create a new record within one of the tables I can select values from the other table which that record is related to within a form.

i.e.
I have two tables, tbl_Evidence and tbl_Reference linked by a junction table (tbl_EvidenceRef) which has enforced referential integrity. If I add a new record within tbl_Reference on the form how could I go about choosing the pieces of evidence from tbl_Evidence that relate to the new reference and vice-versa?

I want this to happen in a user-friendly way on a form

Leigh

[Paulo357]

Try DLookUp to pull what you want from the second Form

[NeoPa]

How about a subform Leigh? That should give you everything you need.

[LeighW]

Thanks for the replies.

Paulo357:
How does DLookUp work? I've seen it used but wasn't sure where you actually insert the code (unless just using the lookup wizard is sufficient)?

On that note I tried creating a lookup data type using the wizard selecting source: 'Evidence' from the junction table, tbl_EvidenceRef as a new field (Lookup_Evidence) in tbl_IdRefs. When using this in the form as a list box it only shown one piece of evidence per reference rather than the multiple pieces of evidence it should have. Changing the list box to multiselect (extended) didn't let me choose the others either.

NeoPa:
Subforms I've tried to use but still not sure how to create a list/combo of the evidence I want to select from when creating a new record as it's a many to many relationship.

Also very tired at the moment after the long weekend so I'm sorry if these are stupid questions

[NeoPa]

Maybe I'm misleading myself with the assumption that evidence will only ever pertain to one item (It's hard to know what a reference record might relate to so I can't conceive of what you're attempting to describe). Perhaps if you could describe how the items relate to each other I might find it easier to think about it.

[Paulo357]

There are many examples on MS Web site, that you can educate yourself from.. here is a starting point

=DLookUp("[LastName]", "Employees", _
      "[EmployeeID] = Form![EmployeeID]")
 

Generally you wont have much luck using the Wizard as it never compiles the code correctly or picks up the quotes when fields need it

[LeighW]

The evidence can have more than one reference though it is usually only one. I can't go into detail of any in the database unfortunately but say for example a river passes two sites and they each have a recording of around the same pollutants. Each site has a seperate reference but leads to the same evidence.

References however like a report from the Environmental Agency for example could relate to numerous pieces of evidence.

At present I have two forms and three tables as it's a many-to-many relationship. tbl_Evidence has all the "Evidence" in text form with related "PKEviNo" (Primary Key, Evidence Number as autonumber). tbl_IdRefEvidenceJoin is the junction table and is joined to tbl_Evidence via field "PK_EviNo" to "fldEviNo". tbl_IdRefEvidenceJoin also has the field "fldIdRefNo" which joins to tbl_IdRefs via "PK_IdRefNo". "PK_IdRefNo" is also an autonumber field which is linked to each Identified Reference "RefTitle" within tbl_IdRefs.

I've since populated the junction table with the "fldEviNo" that link to "fldIdRefNo". I am not sure whether I'm supposed to also enter the "Evidence" and "RefTitle" fields into the junction table.

The two forms, frm_Evidence and frm_IdRefs will be viewable to the masses. The only problem I'm having is when pressing add new record I want a list or combo box to become visible (which I believe is easy enough to do) perhaps in the form footer, which shows text fields "Evidence" or "RefTitle" in a bound multiselect box which you can click to choose which pieces of evidence relate to which reference and vice versa. I'd also like the join to then save so when you go to the new record it will still show the evidence/references it relates to.

Sorry for long winded answer. I hope what I say is doable. It seems like you can do everything in access in some way or another.

[NeoPa]

LeighW:
Sorry for long winded answer. I hope what I say is doable. It seems like you can do everything in access in some way or another.

No need for apologies. You simply provided exactly what I requested you to. Also, you're right about Access. It pretty well can. Within reason of course. I've never seen a program make a cup of tea, but never say never right?

Mostly, getting Access to do what you want just takes a bit of thinking to determine exactly what it is you do want. You'd be surprised how much of the solution comes down to just that. There are other issues too, of course. Designing it once you have the blueprints, but that first step is the most important by far.

I've never used a many-to-many form setup before, but I would consider it sensible to have a linkage set up with referential integrity as a basic requirement. That would mean that the process of entering data would need to be done on a two-pass basis. Enter the underlying data first (Separate forms for each table) and, as a separate process, a linkage form that enables you to create records from two ComboBoxes linked to the PKs of each table.

If a more seamless approach is necessary, then the linkage form could have an option to link to a new record (Each ComboBox could provide that entry) and when either of those is selected the code could open the related data-entry form to allow the operator to enter a new record and then create a link to that new record in the linkage form.

Does that all make sense?

[LeighW]

That it does. I'm just in the process of redoing the junction table as I added too much unneccesary duplicate data. I'm hoping to get this right this time. Many-to-many relationships wreck the brain!

I'll report back with my progress shortly.

[LeighW]

I believe I've done it. Just got to design it into the form.

Thanks very much NeoPa. A serious weight off my mind. That's two ruddles now!

I found a detailed instruction on how to do it below if anyone is interested but never would of thought of looking there without the help!
** SNIP **

[NeoPa]

Sorry Leigh. We don't allow links to competing forum sites :-( I've removed it for you. It looks like it was a good article though ;-)

Otherwise, I'm glad I was able to help, and I might just have to award myself those two pints of County from the fridge :-D