HELP - going off my rocker??!!

[bdj]

Please Please try to help

I am having real problems trying to create a form with a subform (which will display every entry from a location table as a tick completion list)

There are basically two tables tblStaff and tblLocation and what I am trying to do is for every member of staff I want to show every location from the tblLocation which is a many to many relationship.

To normalise the relationships I have created a third table tblLocationdetails with the Primary key from both tables present ie StaffID and Location.

I have then joined (using an OUTER JOIN) the tblLocation and tblLocationdetails in a query (qryLinkLocation and StaffDetails) which displays all locations together with the relevant tick list that I require.

My problem really is just the final stage where I combine the tblStaff and the query qryLinkLocation and StaffDetails into a form and subform. It doesn’t display all the locations and I cannot for the life of me see why.

It really should be only something very simple but I cannot for the life of me see what I have missed.

I have attached a copy of the database as my explanation above probably leaves a lot to be desired.

Please try and help

Brian Jermain
Scotland

 

[Fizzio]

You are having exactly the problem I described here

Essesntially you are trying to create a recordset of all the possible options which is easy to display by removing the master/child links but this recordset is non-updateable.

I'll post the solution I used onto the end of that post but Doc_Man came up with some other solutions

I wonder if Pat has any thoughts on this?

 

[bdj]

HELP - Going off my Rocker

Yes it is exactly the same type of problem.

It really shouldn't be that difficult to display all records in a subform - even blank ones but I have worked my way through all my Access and VBA manuals to no effect.

Brian Jermain

 

[Fizzio]

Displaying the records is not a problem. The problem is making the records updateable due to the nature of the join you need to use to return the full recordset. I tried all ways with all types of joins and queries but to no avail. The fix I posted works well enough even though in theory it could create redundant records. If you come across a better way, I'd be grateful if you could share it.

Good luck!

 

[bdj]

Thanks for your help

If I crack the problem I will certainly give you an e-mail

Thanks again

Brian