When user selects "Others" from Combo Box list

[phool4fool]

I've another issue regarding Combo Box

I've tblEmployees & in that table I'm Resignation reason lookup table (tblReason) in which I've mentioned few reasons & there is also an "Others" option. So, during data entry, I want the user to select Resignation reason from the combo box list & if user select "Others", a Text box should appear so that user can enter the reason.
After that, I want to do a query to find out which Employees resigned for what reason. In that query result, I want to see the user typed text (reasons) for the Employees who selected "Other"
Whether in the form of " Others (reason typed by user) "
or just the text (reason) typed by the user.

I hope you got what I want to ask.
I really need that kind of result.
please guide me.

 

[Millah]

For the first part, create a hidden "other Reason" text box. Create some code in the after update method of the combo box that checks to see if the value is other, if so unhide the other reason text box.

For the query use iif... IIF(nz(CannedReason, '') = '', FreeFormReason, CannedReason) As Reason

 

[phool4fool]

For the first part, create a hidden "other Reason" text box. Create some code in the after update method of the combo box that checks to see if the value is other, if so unhide the other reason text box.

For the query use iif... IIF(nz(CannedReason, '') = '', FreeFormReason, CannedReason) As Reason

Thanks for your kind reply.
Actually, i'm an expert so didn't get more clearly what you have guided. If you don't mind, take a look at my attached db & guide me accordingly.
please

 

[missinglinq]

In your Form fsubStudent, create a Textbox for your explanation of 'Other,' name it Other_Explanation and Bind it to the appropriate Field in your underlying Table.

Now use these two bits of code in that Form.

To immediately make the new Textbox Visible when 'Other' is selected

Private Sub LeavingReason_ID_AfterUpdate()
If Me.LeavingReason_ID = 8 Then
 Other_Explanation.Visible = True
Else
 Other_Explanation.Visible = False
 Me.Other_Explanation = Null
End If
End Sub

To appropriately format the visibility when moving from Record to Record

Private Sub Form_Current()
If Me.LeavingReason_ID = 8 Then
 Other_Explanation.Visible = True
Else
 Other_Explanation.Visible = False
End If
End Sub

Notice that rather than testing to see if

LeavingReason_ID = "Other"

we're testing to see if

LeavingReason_ID = 8.

That's because while you see 'Other' when you make the selection from the Combobox named LeavingReason_ID, the Combobox is actually Bound to the ID Field itself, and the ID for 'Other' is 8.

Also notice the extra line

Me.Other_Explanation = Null

in the AfterUpdate event of the Combobox. This bit of code is used in case the end user selects 'Other,' enters an explanation, then realizes that he/she has made a mistake. This line simply resets the Textbox to Null, removing, if you will, the wrongly entered explanation.

Linq ;0)>

 

[phool4fool]

In your Form fsubStudent, create a Textbox for your explanation of 'Other,' name it Other_Explanation and Bind it to the appropriate Field in your underlying Table.

Now use these two bits of code in that Form.

To immediately make the new Textbox Visible when 'Other' is selected

Private Sub LeavingReason_ID_AfterUpdate()
If Me.LeavingReason_ID = 8 Then
 Other_Explanation.Visible = True
Else
 Other_Explanation.Visible = False
 Me.Other_Explanation = Null
End If
End Sub

To appropriately format the visibility when moving from Record to Record

Private Sub Form_Current()
If Me.LeavingReason_ID = 8 Then
 Other_Explanation.Visible = True
Else
 Other_Explanation.Visible = False
End If
End Sub

Notice that rather than testing to see if

LeavingReason_ID = "Other"

we're testing to see if

LeavingReason_ID = 8.

That's because while you see 'Other' when you make the selection from the Combobox named LeavingReason_ID, the Combobox is actually Bound to the ID Field itself, and the ID for 'Other' is 8.

Also notice the extra line

Me.Other_Explanation = Null

in the AfterUpdate event of the Combobox. This bit of code is used in case the end user selects 'Other,' enters an explanation, then realizes that he/she has made a mistake. This line simply resets the Textbox to Null, removing, if you will, the wrongly entered explanation.

Linq ;0)>

Thanks a lot...!!!
Please find the attached db... I've made changes but it gives the same value in the "Other_Explanation" for each student who selects student who select "Other" from the combo box list, and If I change, it changes for every changes who selected Others. I want to have unique reason for every student.
And the other problem is that, In Query, "qryReason" it only shows "Others", What I want to get in query is that, when user selects Others, in Query; it should display the reason instead of just "Others" because each one has different input when he/she select others and type in "other_explanation" text box"
Please guide me how can I achieve that.

 

[phool4fool]

For the first part, create a hidden "other Reason" text box. Create some code in the after update method of the combo box that checks to see if the value is other, if so unhide the other reason text box.

For the query use iif... IIF(nz(CannedReason, '') = '', FreeFormReason, CannedReason) As Reason

I tried that but it gives the following error:
"The expression you entered contains invalid syntax"
please explain by viewing my db
I'll be very thankful

 

[missinglinq]

In your Form fsubStudent, create a Textbox for your explanation of 'Other,' name it Other_Explanation and Bind it to the appropriate Field in your underlying Table.

You missed the part in Red! The Other_Explanation Textbox has to be Bound to a Field in the underlying Table!

As you've just found out, the Value for a Unbound Control in the Current Record is the Value for that Control on all Records!

Linq ;0)>

 

[phool4fool]

Red[/B]! The Other_Explanation Textbox has to be Bound to a Field in the underlying Table!

I'm sorry but I didn't understand how can I get to it.
Text field is already bound to LeavingReason_ID
So, now what I've to do, please guide me in more detail
I'm feelings sorry but I'll be very thankful if you can guide more....

 

[missinglinq]

Your Combobox is Bound to the Field named LeavingReason_ID. You have to create a new, separate Field in your underlying Table to store the data, if any, from the Other_Explanation Textbox.

If you don't store the Other_Explanation data in a Table, how could there be different explanations for different employees?

1. Add a new Field to your Table
2. Name it Explanation
3. If your Form is based on a Query, add the new Field to your Query
4. In Form Design View, select the Textbox
5. Go to Properties - Data - Control Source
6. Select the Explanation Field
7. Save and Close your Form

If your Form is based directly on a Table, rather than on a Query, ignore # 3 above.

Linq ;0)>

 

[phool4fool]

Thanks... i'm going to try it right now... If I found any issue, I'll ask for ur help again
Meanwhile I've another problem; posted here.
http://www.access-programmers.co.uk/forums/showthread.php?t=219975
please guide me to fix that issue too.
I'll be very thankful.
You are helping me a lot...!!!

 

[phool4fool]

Your Combobox is Bound to the Field named LeavingReason_ID. You have to create a new, separate Field in your underlying Table to store the data, if any, from the Other_Explanation Textbox.

If you don't store the Other_Explanation data in a Table, how could there be different explanations for different employees?

1. Add a new Field to your Table
2. Name it Explanation
3. If your Form is based on a Query, add the new Field to your Query
4. In Form Design View, select the Textbox
5. Go to Properties - Data - Control Source
6. Select the Explanation Field
7. Save and Close your Form

If your Form is based directly on a Table, rather than on a Query, ignore # 3 above.

Linq ;0)>

I can't explain in words that how much I'm feeling thankful to you.
I've done that but 1 more thing:
I'm using a Query to find who left for what reason.
In query I've three field:
Student ID - Reason - Explanation
1 - Reason 1 - NULL
2 - Others - User defined reason

That's what I'm getting in the output of the query
Now, I want to get the following result:
When I do a query, if the reason is Others & Explanation has been entered. It should display the Explanation instead of Others or it should display Others (Explanation in braces) or " explanation in quotes "
I've joined the two field in query but the problem is that it is fine when the reason is Others. But if the reason is other than "Others" , it looks like this:

Field 4 = Main Reason
Output after joining:
Reason 1 ()
Others (User defined reason)

So, how can I handle that????

I just want:
Reason 1
Others (User defined reason) or just User defined reason without the word Others which is getting from the field "Reason"

 

[phool4fool]

Thanks Missinglinq.........I'm really thankful
I've used IIF function to get the desired result.
Thank you soooooooooooooooooooooooooo much...!!!!
Can you please also guide me on the following issue...
http://www.access-programmers.co.uk/forums/showthread.php?t=219975
because no one else responded ... I'm sure you can help me as you are doing right from my joining
God Bless you...!!!