Problems with Form (and/or) Relations

[yaviens]

Hi, I’m creating with Access 2010 a toolbox to help doing engineers projects. The toolbox let the user create loops with elements and his specifications (e.g.: transmitter + PLC + Relay + Junction box)[Each element has his own table with his own specifications]. After creating the loops, when I want to show the loops created I have some problems.



-)The first problem is, that if the loop don’t have all the possible elements Access shows a message box telling there is a problem finding the missing elements.(It is not mandatory for a loop to have all the elements, E.G.: Loop1= Transmitter + PLC + Relay [In this case the program will tell me it doesn’t find the Junction Box, the isolator and the rest of the missing elements])

-)Also it is suppose that only by choosing the loop you want to see, all the fields should be filled automatically. But it doesn’t work at all like this.

(From each element table there is a field with his ID, and also a field called “LoopID” related with another table called “LoopID” where I write the loop names. All this IDs (elements ID + loop ID) go later to a table called “ElementsID” where it is show like this the composition of the loop [LoopID | Element1ID | Element2ID |….].

I let my program here. The form where I want to see the created loops is called “ShowLoops”, and from the Table “ElementsID”, is from where I take the elements ID of each element table. I think my problem is in the relation between tables but I’m not sure I try a lot of things but I’m stuck and I can’t find the problem.

https://www.dropbox.com/s/g5vbd4nrij...atabase5.accdb

[June7]

The form's RecordSource is a query with INNER JOINs. This requires a related record in every table for records to show. Use RIGHT JOIN ("Include all records from ElementsID and only those from ... that match"):

SELECT [Transmitter Spc].*, Relay.*, [PLC Specifications].*, [Junction Box Specifications].*, [Isolator Specifications].*, [Converter Specifications].*, ElementsID.*
FROM [Isolator Specifications] RIGHT JOIN ([Converter Specifications] RIGHT JOIN ([Transmitter Spc] RIGHT JOIN (Relay RIGHT JOIN ([PLC Specifications] RIGHT JOIN ([Junction Box Specifications] RIGHT JOIN ElementsID ON [Junction Box Specifications].ID = ElementsID.[JB ID]) ON [PLC Specifications].ID = ElementsID.[PLC ID]) ON Relay.ID = ElementsID.[Relay ID]) ON [Transmitter Spc].ID = ElementsID.[Transmitter ID]) ON [Converter Specifications].ID = ElementsID.ConverterID) ON [Isolator Specifications].ID = ElementsID.IsolatorID;

Remove LoopID from each of the element tables.

If same name fields must be in each of the element tables (Power, Provider, SIL, Type) then make them unique, such as: ConvType, ConvSIL, IsoType, IsoSIL, etc. Will be less confusing in query that joins all the tables.

[yaviens]

Thank you very much for your help!!

I tried what you told me. I have erase all the relations between the elements and the table LoopID, then I created a query with the SQL code you wrote here and then I created a similar form like "Showloops". But I still have the same problem. When I’m in the form like "Showloops" it is supposed that when I select one of the loops from the Combo Box of “LoopID” all the elements that are part of this selected loop must have their fields filled automatically.

[June7]

Are you trying to use this form to do data entry into all the tables? Cannot do that. A form can be used for data entry/edit of only one table.

In this case a single form bound to ElementsID table with comboboxes to select JBID, PLCID, RelayID, TransmitterID. Displaying data associated with those ID's can be done with the suggested query. Entry of records into those source tables would be separate process.

As is, If I choose LoopID L004 in the combobox, the fields are blank because there is no related data in the records for this query. Need to use comboboxes to select JBID, PLCID, RelayID, TransmitterID. Also, neither the LoopID nor Loops tables are included in this query so their info is not available.

[yaviens]

What I want to do in this form, is only show the information of different tables in one form(ElementsID table and the tables of each element with his specifications are in the form), and the user by selecting the loop he want to see everything change to show the information related to the loop. I don't know if there is an other way to do this.

[June7]

Controls used to input filter criteria must be UNBOUND, otherwise changes data in record. If you want to use the control to enter/edit data in record then it would be BOUND.

You haven't established relationships in Relationship Builder so I am not sure how these tables relate. Why do Loops and ElementsID tables have the same fields (Converter, Isolator, JB, PLC, Relay, Transmitter)? Why do the 2 records in ElementsID both have the same LoopID?

I simply changed the query in your existing form from INNER to RIGHT joins and all data displays.