469,271 Members | 997 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

Trying to create an optional greyed out field...

Hi everyone,

total newbie to all of this so please bear with me and help if you can!

I'm creating a database for a charity using Access 2002 SP3, I have a form called "Member Data". Within the form I have two fields:

"Employed?" and "Employment Details".

"Employed?" is either Yes or No and is pulled from a table called Employed.EmployedDetail.

What I would like to happen is the "Employment Details" field on the form is greyed out, untill "Employed?" is set to Yes. If "Employed?" is set as No, then the box stays greyed out.

I've put No in Enabled under "Employment Details".

I've right-clicked on the field "Employed?", gone to Build Event > Code Builder in the Design View and entered this code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Employed_BeforeUpdate()
  2.  
  3. If Employed.[Employed Detail] = "Yes" Then
  4. Employment Details.Visible = True
  5.  
  6. If Employed.[Employed Detail] = "No" Then
  7. Employment Details.Visible = False
  8.  
  9. End Sub
This isn't working, but I know it should be something like this, if anyone can help me with this code then I would really appreciate it!

Many thanks,
Tony
Jan 31 '07 #1
14 6224
ADezii
8,800 Expert 8TB
Hi everyone,

total newbie to all of this so please bear with me and help if you can!

I'm creating a database for a charity using Access 2002 SP3, I have a form called "Member Data". Within the form I have two fields:

"Employed?" and "Employment Details".

"Employed?" is either Yes or No and is pulled from a table called Employed.EmployedDetail.

What I would like to happen is the "Employment Details" field on the form is greyed out, untill "Employed?" is set to Yes. If "Employed?" is set as No, then the box stays greyed out.

I've put No in Enabled under "Employment Details".

I've right-clicked on the field "Employed?", gone to Build Event > Code Builder in the Design View and entered this code:

Private Sub Employed_BeforeUpdate()

If Employed.[Employed Detail] = "Yes" Then
Employment Details.Visible = True

If Employed.[Employed Detail] = "No" Then
Employment Details.Visible = False

End Sub


This isn't working, but I know it should be something like this, if anyone can help me with this code then I would really appreciate it!

Many thanks,
Tony
You would need code in 2 places to accomplish what you want, In the AfterUpdate() Event of the Employment Field (chkEmployed), and in the Current() Event of the Form, place this same code. (Only the AfterUpdate() Event is shown). Hope this helps:
Expand|Select|Wrap|Line Numbers
  1. Private Sub chkEmployed_AfterUpdate()
  2.   If Me![chkEmployed] = True Then
  3.     Me![txtEmploymentDetails].BackColor = QBColor(15)    'White
  4.   Else
  5.     Me![txtEmploymentDetails].BackColor = RGB(128, 128, 128)      'Ugly Grey
  6.   End If
  7. End Sub
Jan 31 '07 #2
Rabbit
12,516 Expert Mod 8TB
Or alternatively:

Expand|Select|Wrap|Line Numbers
  1. Private Sub chkEmployed_AfterUpdate()
  2.   If Me![chkEmployed] = True Then
  3.     Me![txtEmploymentDetails].Enabled = True
  4.   Else
  5.     Me![txtEmploymentDetails].Enabled= False
  6.     Me![txtEmploymentDetails]= ""
  7.   End If
  8. End Sub
  9.  
If you don't want them to be able to edit the field and to clear the field if they have selected "No".
Feb 1 '07 #3
ADezii
8,800 Expert 8TB
Or alternatively:

Expand|Select|Wrap|Line Numbers
  1. Private Sub chkEmployed_AfterUpdate()
  2.   If Me![chkEmployed] = True Then
  3.     Me![txtEmploymentDetails].Enabled = True
  4.   Else
  5.     Me![txtEmploymentDetails].Enabled= False
  6.     Me![txtEmploymentDetails]= ""
  7.   End If
  8. End Sub
  9.  
If you don't want them to be able to edit the field and to clear the field if they have selected "No".
Good point Rabbit!
Feb 1 '07 #4
NeoPa
32,171 Expert Mod 16PB
Good spot Rabbit.
I would tend to use the boolean fields a little differently myself :
Expand|Select|Wrap|Line Numbers
  1. Private Sub chkEmployed_AfterUpdate()
  2.     Me![txtEmploymentDetails].Enabled = Me![chkEmployed]
  3.     If Not Me![chkEmployed] Then Me![txtEmploymentDetails] = ""
  4. End Sub
'And not forgetting...
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_OnCurrent()
  2.     Me![txtEmploymentDetails].Enabled = Me![chkEmployed]
  3.     If Not Me![chkEmployed] Then Me![txtEmploymentDetails] = ""
  4. End Sub
Feb 1 '07 #5
Hi everyone,

many thanks for submitting code that should get this working, just another quick question:

Where EXACTLY do I need to put the two bits of code?

I told you I was a newbie!!

Thanks for your help,
Tony
Feb 5 '07 #6
Rabbit
12,516 Expert Mod 8TB
If you go to the properties of the text box, click on the tab called Events. On there is where you will find the After Update Event. Click on the button with 3 dots and select Code Builder, that's where you enter the code.

You also want to put it in the Form's On Current Event. Same thing as the text box but you look at the properties of the form.
Feb 5 '07 #7
Hi everyone,

thanks for all your help on this, I've entered information as detailed here and it does work although I've found a small snag:

If Employed? is already on No, and you change it to Yes, then the code changes the Employment Details field to active so you can type something in - great!

BUT, if you then change the Employed? field back to No, then the Employment Details field stays active and does not grey out.

Ideally I need the Employment Details field to go back to grey if Employed? is changed.

Can anyone provide any further help with this?!

Many thanks,

Tony
Feb 12 '07 #8
NeoPa
32,171 Expert Mod 16PB
Can you post the code you're actually using.
I suspect you're not using the latest code I posted (post #5 - which should handle the situation you describe).
Feb 12 '07 #9
NDayave
92
I would suggest using Rabbit's code in post #3 just with a few adjustments:

Expand|Select|Wrap|Line Numbers
  1. Private Sub chkEmployed_Click()
  2.   If chkEmployed = True Then
  3.     txtEmploymentDetails.Enabled = True
  4.   Else
  5.     txtEmploymentDetails.Enabled = False
  6.     txtEmploymentDetails = ""
  7.   End If
  8. chkEmployed.Requery
  9. End Sub
  10.  
change the AfterUpdate to Click,
get rid of the Me! bits
and add chkEmployed.requery to the end

its always worked fine for me in these situations, but in access 2000, im not sure if this makes a difference or not though

NDayave
Feb 12 '07 #10
NeoPa
32,171 Expert Mod 16PB
I'm not sure that going off at a tangent is a very good idea here. I appreciate the impulse to help, but from the code you posted, I'm not sure you've fully grasped the question as asked.
Again, I really don't want to sound critical or put you off from posting. Please feel free to contribute of your experience any time.

Tony, I think it would help all involved if you could post what you're currently using, to tie in with the problems you reported with the code in post #8.
Feb 12 '07 #11
Hi NeoPa & everyone,

thanks for your posts - I appreciate your help. The code I am using on the form is as follows:

Private Sub Form_OnCurrent()
Me![Employment Details].Enabled = Me![Employed?]
If Not Me![Employed?] Then Me![Employment Details] = ""
End Sub

Private Sub Employed__AfterUpdate()
Me![Employment Details].Enabled = Me![Employed?]
If Not Me![Employed?] Then Me![Employment Details] = ""
End Sub
I've changed the names slightly so they are consistent with the names I have in the DB.

The "Employment Details" field is greyed out all the time, and if you change the "Employed" field to either Yes or No, it will activate the greyed out "Employment Details" field. What I would like to happen is:

- "Employment Details" field is greyed out for starters
- If "Employed" is set to No, "Employment Details" stays greyed out
- If "Employed" is set to Yes, "Employment Details" becomes active
- If "Employed" is set back to No, "Employment Details" becomes greyed again

This must be a unique check for each record, as some people will be employed, and some won't so the query needs to check each record for the Yes/No parameter.

I hope I have explained myself clearly, and I hope you can help!

Once again, many thanks for your initial help and code, it is very much appreciated.

Regards,
Tony
Feb 13 '07 #12
NeoPa
32,171 Expert Mod 16PB
The "Employment Details" field is greyed out all the time, and if you change the "Employed" field to either Yes or No, it will activate the greyed out "Employment Details" field.
Tony, This bit is quite unclear. It seems to contradict itself.
Can you explain what is actually happening?
Can you confirm you are actually showing all this on a continuous form (this could be very important info)?
Just to clarify, I do understand what you're after and the code submitted should do that for you, but I'm not sure of the details when working on continuous forms - hence the questions.
Feb 13 '07 #13
Hi NeoPa,

I've checked on the Form properties and the default view is "Single Form". I can't change it to a Continuous Form as my current form contains several sub-forms.

The "Employment Details" code works exactly how I explained it:

1. Regardless of if the "Employed?" field is originally set as Yes or No, the "Employment Details" field is always greyed out
2. When you change the "Employed?" field to either Yes or No, the "Employment Details" field becomes active for all records.

Let me know if you need any more information and again, thanks for your help.

Tony
Feb 14 '07 #14
NeoPa
32,171 Expert Mod 16PB
1. Regardless of if the "Employed?" field is originally set as Yes or No, the "Employment Details" field is always greyed out
Does always mean it always starts out that way? If so, what does 'starts out' mean in this context? (When you change to a new record or when you open the form?)
2. When you change the "Employed?" field to either Yes or No, the "Employment Details" field becomes active for all records.
Again, does this mean that changing to another record, where the "Employed?" field has not been touched, still leaves the "Employment Details" field active, even when it shouldn't be.

Your code is is :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_OnCurrent()
  2. 'Breakpoint the following line!
  3. Me![Employment Details].Enabled = Me![Employed?]
  4. If Not Me![Employed?] Then Me![Employment Details] = ""
  5. End Sub
  6.  
  7. Private Sub Employed__AfterUpdate()
  8. 'Breakpoint the following line!
  9. Me![Employment Details].Enabled = Me![Employed?]
  10. If Not Me![Employed?] Then Me![Employment Details] = ""
  11. End Sub
Breakpoint both indicated lines and trace through the code when you do your testing (Use Shift-F8 to progress through the code). Look for the values set when the code has passed the line.
Feb 14 '07 #15

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by blindsey | last post: by
8 posts views Thread by EdB | last post: by
15 posts views Thread by lxyone | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.