Trying to create an optional greyed out field...

[TonyMacaroni]

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

[ADezii]

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:

Private Sub chkEmployed_AfterUpdate()
  If Me![chkEmployed] = True Then
    Me![txtEmploymentDetails].BackColor = QBColor(15)    'White
  Else
    Me![txtEmploymentDetails].BackColor = RGB(128, 128, 128)      'Ugly Grey
  End If
End Sub

[Rabbit]

Or alternatively:

Private Sub chkEmployed_AfterUpdate()
  If Me![chkEmployed] = True Then
    Me![txtEmploymentDetails].Enabled = True
  Else
    Me![txtEmploymentDetails].Enabled= False
    Me![txtEmploymentDetails]= ""
  End If
End Sub
 

If you don't want them to be able to edit the field and to clear the field if they have selected "No".

[ADezii]

Or alternatively:

Private Sub chkEmployed_AfterUpdate()
  If Me![chkEmployed] = True Then
    Me![txtEmploymentDetails].Enabled = True
  Else
    Me![txtEmploymentDetails].Enabled= False
    Me![txtEmploymentDetails]= ""
  End If
End Sub
 

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!

[NeoPa]

Good spot Rabbit.
I would tend to use the boolean fields a little differently myself :

Private Sub chkEmployed_AfterUpdate()
    Me![txtEmploymentDetails].Enabled = Me![chkEmployed]
    If Not Me![chkEmployed] Then Me![txtEmploymentDetails] = ""
End Sub

'And not forgetting...

Private Sub Form_OnCurrent()
    Me![txtEmploymentDetails].Enabled = Me![chkEmployed]
    If Not Me![chkEmployed] Then Me![txtEmploymentDetails] = ""
End Sub

[TonyMacaroni]

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

[Rabbit]

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.

[TonyMacaroni]

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

[NeoPa]

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).

[NDayave]

I would suggest using Rabbit's code in post #3 just with a few adjustments:

Private Sub chkEmployed_Click()
  If chkEmployed = True Then
    txtEmploymentDetails.Enabled = True
  Else
    txtEmploymentDetails.Enabled = False
    txtEmploymentDetails = ""
  End If
chkEmployed.Requery
End Sub
 

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

[NeoPa]

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.

[TonyMacaroni]

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

[NeoPa]

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.

[TonyMacaroni]

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

[NeoPa]

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 :

Private Sub Form_OnCurrent()
'Breakpoint the following line!
Me![Employment Details].Enabled = Me![Employed?]
If Not Me![Employed?] Then Me![Employment Details] = ""
End Sub
 
Private Sub Employed__AfterUpdate()
'Breakpoint the following line!
Me![Employment Details].Enabled = Me![Employed?]
If Not Me![Employed?] Then Me![Employment Details] = ""
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.