MS Access open up field when particular option is selected.

[jonathon28]

Hi,

 

I am in the process of creating an asset management databse in access 2010 for all our IT and electronic items.

 

What I am trying to do but don't know how to do is:

 

When I tick the box to retire an item I want another field to pop up on the form asking for a reason for retirement e.g. broken, not economical to repair. failed PAT etc.

 

Also, I am trying to integrate software licencing into the database so I can query a peice of software e.g. Crazy talk and know exactly which machines and how many licences are being used. I have created a table with the software names and serial numbers.

 

Any help would GREATLY be appreciated.

 

Thanks in advance. Jon

[LosOjos]

How comfortable are you with VBA?

[jonathon28]

How comfortable are you with VBA?

 

Sorry. No idea at all!

[LosOjos]

Sorry. No idea at all!

 

Ok, well I can give you an idea how it could be done simply with VBA and if you like the sound of it, I'll get you the actual code and tell you where to stick it (politely )

 

You could have the text box on the form with it's default value set to disabled. This would mean it would still be visible, but greyed out and with no user edits possible. Then, when the box is ticked to retire the item, you could have a simple VBA routine that enables the text box so that information could be entered...

 

Not as clean as having it magically appear, but much simpler to achieve...

 

EDIT: alternatively you could hide it rather than disable it, but this would appear as whitespace until it appears again, so just disabling it would look tidier in my opinion...

[jonathon28]

That sounds good to me. Let's give it a go.

[LosOjos]

[i'm not sure what version of Access you're using so I'll use generic terms for menus, you may need to Google to find out how to get to them but they will be there regardless of which version you're using]

 

OK, first thing you need to do is create your field as you would any other and add it in to your form wherever you'd like it.

 

Enter Form Design mode.

 

Click on the text box that you just created (the text box is the data entry part of the field you added) and look at it's properties. You should see on the "Data" tab an option called "Enabled". Set it to "No".

 

At the top of the "Other" tab in the text boxes properties, you should see a field called "Name" - make a note of this.

 

Next, click on the check box that retires items (make sure you click the actual check box, not the label associated with it) and look in it's properties panel, under the "Event" tab, for an option called "On Click". Change this to "[Event Procedure]", then click on the button next to it labelled "..."

 

This will open up the VBA editor, and will create a Sub Routine for you called "Check0_Click()" (NOTE: that's assuming the check box was called "Check0" - your may vary slightly).

 

Assuming the name of the text box earlier was "Text1" and your check box was called "Check0" (which they probably won't be, so you'll need to alter the code to reflect this) your sub routine should look like this:

Private Sub Check0_Click()
Text1.Enabled = Check0.Value
End Sub

 

You can close the VBA editor now and save your form, then return to "Form View" to test it.

 

That should achieve what you want, so long as you make sure you replace the "Check0" and "Text1" with the relevant names in your form.

Edited October 11, 2011 by LosOjos

[jonathon28]

[

Next, click on the check box that retires items (make sure you click the actual check box, not the label associated with it) and look in it's properties panel, under the "Event" tab, for an option called "On Change". Change this to "[Event Procedure]", then click on the button next to it labelled "..."

 

.

 

I cant find the 'on change' to change it to event procedure....?

[jonathon28]

I have an 'after update' option?

[LosOjos]

I cant find the 'on change' to change it to event procedure....?

 

My fault! Typed up the post before I tested it and forgot to alter that: you are actually looking for "On Click", and "[Event Procedure]" will be an option in the drop down box associated with it

[jonathon28]

Sorry, I can't find an 'on click' option either for the checkbox. see attached screenshot.

[LosOjos]

Sorry, I can't find an 'on click' option either for the checkbox. see attached screenshot. [ATTACH=CONFIG]11592[/ATTACH]

 

In the "properties" panel on the right hand side of the screen, change to the "Event" tab; do you see it there?

[jonathon28]

no i just get 'After Update'.

[LosOjos]

no i just get 'After Update'.

 

OK well that should work actually, click the drop down box and choose "Event Procedure". If it doesn't say that and instead gives you choices like "Macro Builder" and "Code Builder" - choose Code Builder.

 

Your code should look like this (although I haven't changed the name of the text box, you'll still have to do that):

 

Private Sub Check110_AfterUpdate()
Text1.Enabled = Check110.Value
End Sub