Validation for Control Box on Form

[elevins]

I am trying to use a data validation for a control text box [ItemCodeEnter]. When users are entering data I would like this control box to check the table.field [Inventory.ItemCode] before allowing them continue. The idea is for them to type text but only be able to type the values that already are there. Does anyone have suggestions?

 

[kjv1611]

For a combobox, you can use the Limit to List property, assuming you have the combo box set to this same table/field you are wanting to compare to.

If for a text box, you might could use the DLOOKUP function, like this:

Private Sub ItemCodeEnter_KeyDown(KeyCode As Integer, Shift As Integer)
  If KeyCode = 9 Or KeyCode = 13 Then
    'Use Dlookup - If the textbox value returns a hit, then..
    If DLookup(... see help file for examples) Then
      'Continue on as normal, or whatever you want to do..
    Else
      MsgBox "Value not recognized, please re-enter."
      ItemCodeEnter.Undo
   End If
End Sub

You may can use either the KeyDown or KeyPress events for the same effect.

 

[missinglinq]

In the Before_Update event of the form textbox use DCount() or DLookup to determine if the entered value existd in your table. If not, popup a message box with instructions for your user. An alternative would be to replace the textbox with a combobox.

The Missinglinq

There's ALWAYS more than one way to skin a cat!

 

[kjv1611]

Yeah, the _BeforeUpdate event would be much more efficient than the KeyDown or KeyPress event - duh, me.