Combobox returns wrong index

[Teun Ham]

I have a combobox on a Form, which is being populated by a UNION query:

        sSQL = "SELECT [T_MaintenanceType].[MaintenanceTypeID], 'Item ' & [T_MaintenanceType].[MaintenanceTypeDescription] " & _
            "FROM T_MaintenanceType " & _
            "INNER JOIN " & _
            "T_RequiredMaintenance ON T_MaintenanceType.MaintenanceTypeID = T_RequiredMaintenance.MaintenanceTypeID " & _
            "WHERE [T_RequiredMaintenance].[AssetID] = " & iAssetID & " " & _
            "ORDER BY 2 " & _
            "UNION ALL " & _
            "SELECT [T_MaintenanceType].[MaintenanceTypeID], 'Group ' & [T_MaintenanceType].[MaintenanceTypeDescription] " & _
            "FROM T_MaintenanceType " & _
            "INNER JOIN " & _
            "(T_RequiredMaintenanceG INNER JOIN T_Assets ON T_RequiredMaintenanceG.AssetGroupID = T_Assets.AssetGroupID) " & _
            "ON T_MaintenanceType.MaintenanceTypeID = T_RequiredMaintenanceG.MaintenanceTypeID " & _
            "WHERE [T_Assets].[AssetID] = " & iAssetID & " " & _
            "ORDER BY 2"

As you can see by the UNION query, both queries are looking at the same table (T_MaintenanceType). To distinqish the queries, I have added 'Item ' and 'Group ' to the "MaintenanceDescription". The query works, it shows me the result of both queries.

The result is, for example:

Group APK
Group KOMO
Item APK

But! If I select "Item APK" from the combobox, the combobox shows "Group APK"! My guess is that the combobox is using the "MaintenanceTypeID" internaly and is returning the first match it finds. As "Item APK" is truely "APK", the combobox just shows the first "APK" it finds: "Group APK".

How can I prevent this? How can I get the combobox to show "Item APK" when I select it?

 

[Norie]

Teun

I took a look to and the foreign keys were the first thing I noticed.

xenou is right, you need to sit down and figure out what you need.

The way I see it is you have assets that are maintained.

So you have an assets table and a maintenance table, with a 'junction' table linking the two.

The linking table would only have 2 'main' fields - the primary keys (IDs?) from the assets and maintenance table as foreign keys.

That's a really simplified setup from my point of view, but it would probably be enough to get things started.

 

[Teun Ham]

What is the purpose of the RequiredMaintenance (table/ID) and how is it related to /different from MaintenanceID (table)?

The T_RequiredMaintenance is a table where we define which kind of maintenance should be performed on the asset. Like: we need to check the tire-pressure of our cars each month.

The T_Maintenance is the table where the actual maintenance is being stored. Like: at 2011-08-11 we checked the tire-pressure of the BMW. (Maybe I should have called it a T_Inspection table to avoid any confusion )

I hope this is clear now

The T_Maintenance (Inspection) table, which is the linking table, now has two Foreign Keys (AssetID from T_Assets and RequiredMaintenanceID from T_RequiredMaintenance). So that should be OK.

As for the T_RequiredMaintenance...

The thing which is making this complex (at least for me) is the fact that we want to define required maintenance for certain groups (for example all cars should be checked for tire-pressure each month) and we want to define required maintenance for specific assets (for example, only the BMW should have an inspection for the automatic shiftbox).

Should I create two tables for this? (One table for the required maintenance for groups, one table for the required maintenance for specific assets)
Or can I combine them into one table? (One table which holds the required maintenance for both groups and assets)

Right now, I have one table which holds both information. For this the table has two FKs (AssetID and AssetGroupID). Only one of those two will have a value, the other will be Null.

For example:
T_RequiredMaintenance
RequiredMaintenanceID (AutoNumber, PK)
AssetID (FK)
AssetGroupID (FK)
RequireMaintenanceDescription (Text)

1, BMW, Null, Shiftbox
2, Null, Cars, Tire-pressure

Is having one table the correct way? Or should I use two separate tables?

 

[Norie]

Does every asset's required maintenance solely depend on their asset group?

Or does the asset group determine the 'type' of required maintenance an asset requires.

Then based on that and the individual asset you can determine the 'actual' required maintenance.

Does that make any sense or is it way off?

 

[Teun Ham]

Does that make any sense or is it way off?

No, I think you're not way off

If I would speak in OOP, an asset will 'inherit' all the required maintenance of the AssetGroup it belongs to. Besides the 'inherited' required maintenance (defined by the AssetGroup) it can also have its own required maintenance.

So speaking about those cars...
The BMW will inherit all the required maintenance of the AssetGroup "Cars", but the BMW also has its own required automatic shiftbox maintenance.
The Mustang will also inherit all the required maintenance of the AssetGroup "Cars", and will have its own required interior maintenance.

Makes sense???

 

[Norie]

Yes, but how to implement it is the thing I can't quite get.

I was thinking of perhaps 2 tables, one for group required maintenance and one for asset required maintenance.

The latter would have data about any specific maintenance an asset requires that is not part of the group maintenance.

So to get the 'overall' maintenance required we use the asset's group maintenance and it's own maintenance.

 

[Leenaa1]

If you are using a Combobox control and the index it is returning is incorrect, there are a few possible causes for this. First, make sure that the data source for the Combobox is correctly populated with the correct data. If the data source is incorrect, the Combobox will not be able to accurately return the index of the selected item. Additionally, make sure that the Combobox is correctly set up to return the correct index. If the Combobox is set up with a SelectionMode of Single, it will only return the index of the item that is currently selected. If the SelectionMode is set to Multiple, it will return a list of indices of all selected items. Finally, check that the Combobox is correctly configured to update its index when an item is selected. If it is not configured to update its index when an item is selected, it will not return the correct index