+ Post New Thread
Results 1 to 13 of 13
Office Software Thread, MS Access open up field when particular option is selected. in Technical; Hi, I am in the process of creating an asset management databse in access 2010 for all our IT and ...
  1. #1

    Join Date
    Oct 2010
    Location
    Birmingham
    Posts
    94
    Thank Post
    11
    Thanked 13 Times in 12 Posts
    Rep Power
    11

    MS Access open up field when particular option is selected.

    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

  2. #2

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,578
    Thank Post
    1,473
    Thanked 1,225 Times in 834 Posts
    Rep Power
    753
    How comfortable are you with VBA?

  3. #3

    Join Date
    Oct 2010
    Location
    Birmingham
    Posts
    94
    Thank Post
    11
    Thanked 13 Times in 12 Posts
    Rep Power
    11
    Quote Originally Posted by LosOjos View Post
    How comfortable are you with VBA?
    Sorry. No idea at all!

  4. #4

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,578
    Thank Post
    1,473
    Thanked 1,225 Times in 834 Posts
    Rep Power
    753
    Quote Originally Posted by jonathon28 View Post
    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...

  5. #5

    Join Date
    Oct 2010
    Location
    Birmingham
    Posts
    94
    Thank Post
    11
    Thanked 13 Times in 12 Posts
    Rep Power
    11
    That sounds good to me. Let's give it a go.

  6. #6

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,578
    Thank Post
    1,473
    Thanked 1,225 Times in 834 Posts
    Rep Power
    753
    [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:
    Code:
    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.
    Last edited by LosOjos; 11th October 2011 at 03:39 PM.

  7. #7

    Join Date
    Oct 2010
    Location
    Birmingham
    Posts
    94
    Thank Post
    11
    Thanked 13 Times in 12 Posts
    Rep Power
    11
    Quote Originally Posted by LosOjos View Post
    [
    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....?

  8. #8

    Join Date
    Oct 2010
    Location
    Birmingham
    Posts
    94
    Thank Post
    11
    Thanked 13 Times in 12 Posts
    Rep Power
    11
    I have an 'after update' option?

  9. #9

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,578
    Thank Post
    1,473
    Thanked 1,225 Times in 834 Posts
    Rep Power
    753
    Quote Originally Posted by jonathon28 View Post
    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

  10. #10

    Join Date
    Oct 2010
    Location
    Birmingham
    Posts
    94
    Thank Post
    11
    Thanked 13 Times in 12 Posts
    Rep Power
    11
    Sorry, I can't find an 'on click' option either for the checkbox. see attached screenshot. accessdb.png

  11. #11

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,578
    Thank Post
    1,473
    Thanked 1,225 Times in 834 Posts
    Rep Power
    753
    Quote Originally Posted by jonathon28 View Post
    Sorry, I can't find an 'on click' option either for the checkbox. see attached screenshot. accessdb.png
    In the "properties" panel on the right hand side of the screen, change to the "Event" tab; do you see it there?

  12. #12

    Join Date
    Oct 2010
    Location
    Birmingham
    Posts
    94
    Thank Post
    11
    Thanked 13 Times in 12 Posts
    Rep Power
    11
    no i just get 'After Update'.

  13. #13

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,578
    Thank Post
    1,473
    Thanked 1,225 Times in 834 Posts
    Rep Power
    753
    Quote Originally Posted by jonathon28 View Post
    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):

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

SHARE:
+ Post New Thread

Similar Threads

  1. MS Access files
    By timbo343 in forum Windows
    Replies: 11
    Last Post: 18th April 2007, 02:58 PM
  2. MS Access to E-mail Updates?
    By PsychoTech83 in forum How do you do....it?
    Replies: 0
    Last Post: 9th February 2007, 10:29 AM
  3. MS Access
    By wesleyw in forum How do you do....it?
    Replies: 1
    Last Post: 9th November 2006, 09:50 AM
  4. Profile to allow just MS Word and MS Access
    By mattpant in forum Windows
    Replies: 17
    Last Post: 12th October 2006, 03:06 PM
  5. ms access request for help
    By rama1712 in forum Windows
    Replies: 1
    Last Post: 6th October 2006, 01:01 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •