Text box Entry Based On Drop Down Selection

[nyamrembo]

Hi, I am trying to figure how write code that will do the following:
I have a drop down combo box and a corresponding text field. What I would like to do is to make sure that the text field is not left blank only if a certain selection is made. For example, assume my combo box has the following items:
Apples
Oranges
Mangoes
Bananas
etc
and so, lets say that the user selects Mangoes from the list, I want him/her to enter a corresponding value in the text box for mangoes, which cannot be left blank because the value is required. But if the user selects Apples, then he/she does not have to enter a value in the text box. I just want to make sure that only required values are entered in the text box. Which means that text box can be left blank if the corresponding combo selection does not require a value in the text box. I hope I am making sense. Please let me know if you are confused?
Thanks,

 

[MajP]

something like

Private Sub Form_BeforeUpdate(Cancel As Integer)
Select Case Me.cmboFruits
Case "Mangoes", "Oranges", "Bananas"
If Trim(Me.textBox1 & " ") = "" Then
MsgBox "Must enter a value in field2"
DoCmd.CancelEvent
End If
End Select
End Sub

 

[dhookom]

I would probably not "hard-code" values into VBA. I expect there is a table of unique records for various fruit. I would add a yes/no column like [RequiresCorrespondingValue]. This field could be added to the combo box Row Source so code in the after update or on current could show or hide the text box for the corresponding value.

   Me.txtCorrespondingValue.Visible = Me.cboFruit.Column(1)
   'other code that moves focus or sets required

Duane
Hook'D on Access
MS Access MVP

 

[nyamrembo]

Yes there is a table of unique records for various fruits, but the table is a linked table and I cannot access it because I don't have permissions and it's also linked to 3 other databases. But the number of fruits from the combo box that require a corresponding entry on the text box are not very many. My question is, aside from the fact that the combo values may change at some point, what is the disadvantage of hard-coding the these few selected fruits?

 

[dhookom]

The disadvantage is that when the values change you would have to change the code. You should maintain data in tables not values in code.

If you can't change the linked table, create a new table of only the fruit that require the corresponding value. Join this in to the Row Source with a join that includes all records from the linked fruit table.

Duane
Hook'D on Access
MS Access MVP

 

[nyamrembo]

Thank you guys...will see what manager prefers.
Have a great weekend!