Issue with Shifting Source/Destination Fields in Form

[kwolfel]

I have a database that has an equipment list form and a cable list form, both tied to tables. The cable list has source and destination fields that pull their information from the equipment list.



I'm running into an issue where any time a new piece of equipment is added to the equipment list, it impacts the cable list. It's as if the autonumber ID gets reassigned each time a new piece of equipment is added and it changes the equipment name by however many pieces of equipment have been added.

For example, if I add one piece of equipment in the equipment list table via the table list form, the equipment that is in the cable list source/destination fields of the equipment list form will shift by 1. I do have the forms sorted on load so that everything is in order for the user. Could this be messing it up? Does anyone have any thoughts?

[orange]

Please post a jpg of your tables and relationships.

[ItsMe]

Are your forms bound to tables or queries?

[kwolfel]

The forms themselves are bound to tables - tbl_Equipment and tbl_CableList. I do use a query to combine the equipment name and equipment description into one field and then pull from this query to provide the source and destination fields in the Cable List. I have pasted some snapshots below of the two tables, the "EquipmentCombined" query, and the relationship between the Cable List and Equipment Query to fill the source/destination fields. I also just realized that the source/destination fields in the Cable List are no longer filled, which could be part of this issue.

[kwolfel]

I have added a column to my tbl_Equipment for the combined name and have tied the table to the fields in the form, instead of a query. However, this did not appear to help.

When a value is added to tbl_Equipment, it still appears to shift the values in the source/destination field in the CableList. I did notice that it only shifts some values though. The snapshot above that shows the query behind the source/destination fields is still valid but has just replaced the qry_EquipmentCombined with tbl_Equipment. As you can see, I do sort the unit and equipment name in ascending order. I'm not sure if this has to do with the only partial shifting. For example, I added a piece of equipment that started with an "E." Everything prior to this "E" equipment remained untouched. However, everything that came after the "E" equipment had shifted.

I am wondering if by sorting these two fields by ascending, if that is causing the shift every time a new piece of equipment is added. Has anyone run into this issue before? I'd like to keep these sorted as we have two different units and it is less confusing when everything appears in order in the drop down menu.

[ItsMe]

I am sorry to say that what you have here is very confusing to me. It seems that you have spent a lot of time and energy into getting the results you need. Unfortunately, the more you build onto this structure the farther away it is going to take you from your goals.

Consider going back to your two first tables. You need to start there and make sure you have Foreign keys inside one of the two. The exception to this is if you have a many to many issue between the two tables.

Until you have a good grasp on a One to Many, you will continue to struggle.

When I look at your two tables I see they each have their own Unique identifiers. This is good. Identify the table that will have one record represented many times in the other table. Grab that unique identigier (PK) and place that value in the other table. Place the PK value in the FK fields where the record (row) relates to the first record from the other table.

Once you have that you can go to the Querry builder and add both of your tables to a new query. Create a connection (relationship) between the two and adjust the properties so you see "All" of the records in the one of the tables and only the matching records in the other.

Play around with the query. I believe this is where you need to spend your time. I believe this will give you the foundation you need to start towards your goals.

[June7]

Sorting is not the issue.

How did data ever get saved into SourceEquip and DestinationEquip fields? The ID field of tblEquipment should be saved as foreign key yet the ID field is not included in combobox RowSource. Also, ID field is autonumber type so the SourceEquip and DestinationEquip fields must be number type. For joining in queries, PK/FK fields must be same type.

Don't need the intermediate query to build the RowSource. Can draw directly from table.

SELECT ID, EquipmentName & ";" & EquipmentDetails As EquipComb, Unit FROM tblEquipment ORDER BY Unit, EquipmentName;

Other combobox properties:
BoundColumn: 1
ColumnCount: 3
ColumnWidths: 0";2.0",0.15"

What are the fields SourceComponent and DestinationComponent for?

Multiple similar name fields indicates non-normalized structure. What are the RouteX fields for? Will each always have data? Will there never be need for more than 5?

I see other fields that look like text type and should probably be number: Rev, Qty, CableSize, OD, Area. TotalArea is a calculated value and should not be saved in table, certainly not as a text field. Note that numbers saved as text will not sort properly because alpha sort rules apply: 10 will sort before 2 unless placeholder zeros are used, like: 01, 02, 03 ... 10.

Don't understand Qty field. A single CableNo can have multiple cables (Ex: test3, C2U105)?