creating a form that changes what fields are entered based on a dropdown box

[rick]

Hi,

Not sure how to explain my problem but ill give it a crack.
I'm looking into making a maintenance database for my business that tracks our customers assets that we maintain. We maintain several different types of machines and each machine has different properties that i would like to document.
If i make a table in access called "assets" and then in the entry form have a dropdown box to select asset type can i then allow the user to populate only the fields that are required for that particular asset type. for example:
two typical pieces of equipment we maintain are "compressors" and "air recievers" for the compressor i would like to document say motor size and for the air reciever i would like to document the design pressure. neither the motor power or design pressure are relevant to the other asset type.



What is the best way to go about this to make it easy for a user to select the asset type and then it only prompts the user to fill out the data related to each asset? Should i have multiple tables each designated to each asset or should i have some sort of dynamic form that changes the inputs and only allows the user to enter in the relative fields leaving out the irrelevant ones.
Access is still pretty new to me i have a pretty good knowledge of formulas from excel and have applied them to other access databases we run at work however the databases we use at work are quite basic and dont use form entry, and the larger our database is getting and being accessed by several staff now, the more mistakes are being made. I would like to make it as user friendly as possible to prevent these mistakes from being made. also to improve our efficiancy and ability to track previous maintenance works on each customers different assets.
I have looked at the vehicle maintenance template and it sort of gives me something to go off however the more information i can make the database hold the more valuable and time saving it will be for us in the future.
Cheers in advance

[Dave14867]

Rick,

The simple answer is yes you can, you can use the "AfterUpdate" event to show or hide any other field or fields that you want to.

Dave

[rpeare]

How many different properties do you have over your equipment?
Do any of the properties overlap each other? For instance are there any properties you want to track in a compressor and an air receiver that are the same (for instance hours of operation or engine time)?
I can think of a couple of ways to do this one is more complex but probably a better design, another way is easier but not a great design.

The easier way is to create a table having all the possible tests for all pieces of equipment then on your data entry form have a function to check each field to see if it's relevant to the equipment type. I am enclosing a simple database as an example on the simple way, but the same mechanism can be used regardless of setup, to make controls visible or invisible to the user.

rick.zip

[rick]

Thanks for that in all there are probably 20 properties across the board that we would like to keep track of. Some properties do overlap eg serial number purchase date etc. as you said I was thinking of having the one table with all the possible properties for everything but if it's going to be too complicated to have to check if each property is relevant to that piece of equipment, I could perhaps make three or four forms that the users open that relate to the type of equipment. as it stands at the moment there are only 5 maybe 6 different equipment types that i would like to cover so it might be quicker than writing the arguments for each equipment type

Thanks heaps for your help.

Cheers
Rick

[ArviLaanemets]

I have used 2 different approaches myself.

1 As adviced in previous posts, the equipment table has a field for every possible property. Equipment form has controls for every property, and controls are made visible/invisible depending on equipment type/class. This can be done using Current event of form, or having the equipment form as subform in unbound form with combo to select equipment class (Equipment subform is linked to combo in unbound form, visibility of controls in equipment form is triggered whenever a new equipment type is selected.);

2. For properties present for all (or most of) types/classes the equipment table has proper fields , but it will also have some number of 'blank' numeric, text, date etc. fields. There will be also a table with field for equipment type, field for name of 'blank' field, and field for name displayed as label for control in form. When last one is empty, the control (and it's label) associated with this 'blank' field will be hidden for this equipment type/class. The rest of it will be like p.1, but visibility of controls linked to 'blank' properties and their labels are read from this additional table. (With this approach, designing reports/queries will need a bit more of work, and it is usable when field lengths, the number of decimals, etc. don't vary too much.)