Creating a conditional relationship with a subform

[KingKen]

I have a Subform that store support logs for the work done on each asset. the master form is linked to the asset Table and transfers the asset ID to the sub form. This is the objective that i am trying to achieve when I am Logging Hardware related problems. When I am logging Software support task in the same subform however, I do not want the hardware asset ID entered into the table. to accomplish this i think I have to break the Asset ID link between the two forms then log the event. Is this a realistic approach - if so how can I achieve it - or is it too complicated? If so then what is a better approach?

[Stewart Ross]

Hi. Relationships cannot be broken in the way you have suggested - either there is a relationship between two tables or there isn't. You will need to consider carefully which foreign keys are carried into the many-side table (represented by your subform), and what these represent.

I am not clear at present what your tables really do - if your asset may be hardware or software and you use an asset number as part of a compound key in your support log you will be unable to leave that out of the support log entry.

There is no problem about using an optional relationship to represent hardware and software items - given a suitably-defined set of relationships a left-join set in the relationships window will allow you to record support calls where there is no hardware-related element involved. It suggests that you would need two such optional (left-joined) relationships in your many-side table - one for the hardware item, and one for the software item, each of which may be left null if not required.

Left-joins are very useful for such optional relationships (those which are one to 0 or more, and not just one to many).

It may not be well-known that the relationships window allows you to set such outer join relationships directly when setting up the relationships and referential integrity for your tables.

If you need more detailed assistance with this it would help us if you posted the relevant metadata for the master and detail tables - the field names, types and details of PK/FK relationships.

As a final thought, if I were looking at assets as entities I would say that the type of the asset - hardware, software or any other classification - is a property of the asset, not of its support log entry. The type of the asset would be a non-optional property. Perhaps you could clarify the structure of your tables to help us to help you set up a better model of your data.

-Stewart

[KingKen]

Thanks for the insights.

I have a table that stores all the Hardware assets in the organisation in which the assignment of these assets to users are made.

Now i created a Support Log table where i will store all the support tasks that is performed by technical staff. This table has a many to one relationship with the Hardware asset table.

Support tasks sometimes has nothing to do with the hardware assets but is directly the fault of some software eg locked account of problems with formula in Excel. All of the support tasks are entered into the same table.

I do want to trace the history of each hardware asset and problems that user might over a certain period. To accomplish this I created a form linked to Hardware Asset Table with a sub form linked to Support Logs table. If the problem is hardware the user searches for the specific hardware in its table. When it is found the ID is auto transferred to the sub form and relevant data is auto loaded in their fields.

When it is a software problem I do not want the hardware asset ID to be loaded in fact i do not even want the user to see the hardware asset controls in the form. My train of thought is to put an option group on the form that would allow the user to select hardware or software. If he selects software then the sub form gives a blank asset ID field and hides the controls in the mother form while allowing entry of data in the sub form.

Is this achievable? Is it the best way to go?
If this does not clarify then I'll post the meta data

[Stewart Ross]

You can certainly have an optional relationship for the hardware asset number in your support log - leaving it null if the problem is a software one, and showing different form options as you have suggested - but what is it you will then be recording for the software support call in your support log?

Assuming that by hardware asset you mean the physical PC issued to a specific user, if you do not record the asset ID it is unclear what you will be logging. Do you instead record the user's ID along with the date and time for your support log? There has to be some such ID you are recording, as otherwise the support log cannot be linked back to specific users of the software for reporting purposes.

-Stewart

[KingKen]

Yes you are right. I am logging the user ID in both hardware and software assets so as to tie each event to a specific user. The trouble for me now is coding the option button to accomplish this task.

So here is where i need help

[tdw]

Probably a dumb idea, but could you give software support a dummy asset ID number, like zero?

[KingKen]

To give software asset the ID zero and leave things as they are I would have to make an entry in the Hardware asset table with the ID zero. This is impossible since The ID field property in that table is set to autonumber.
As it stands right now i cannot enter anything manually in the Hardware asset field on the subform since it is linked to the parent form. that is , even though the text box property is not set to Locked, it behaves as though it is.

[DonRayner]

Why not just create a "Software Only" entry in your hardware asset table and use that asset when you are entering data for your software fixes.

[KingKen]

I am trying to think this through... I guess i would then have to set a button that auto enters that Id into the form so as to avoide having the user search for that record every time he needs to log a support task. Hmmmm!

Does any one else have any other ideas on this topic?

I want to keep things as simple as possible for the user. Bear in mind also that i have reports running on my Hardware assets table. So adding stuff there that don't belongs is not my first choice, least it shows up unwanted.