FIELD VALIDATION Question

[kealaz]

In my form [frmWHEREUSE] there are four fields

PART_NO
NAME
USED_ON
QTY_USED

Before accepting (saving) record, I would like to check that the number in
one of the fields [USED_ON] is an exact value match to a number in our
Drawing Log [TABLE: tblDWGLOG FIELD: PART_NO]. How can I do this? Maybe
an After Update of the field, or upon focus of the next field [QYT_USED]?
Not sure what would be best. Please help with the code I would use. If the
number does not match, I do not want the record to be saved, but instead an
error message should appear.

Thank you so much for your generous time and assistance.

 

[tina]

you can write validation code into the either the control's or form's
BeforeUpdate event, as

If DCount(1, "tblDWGLOG","PART_NO = '" _
& Me!PART_NO & "'") < 1 Then
Cancel = True
Msgbox "Invalid part number. Please re-enter."
Me!PART_NO.Undo
End If

the above code assumes that the part number field in tblDWGLOG is a Text
data type. if it's a Number data type, change the syntax to

PART_NO = " & Me!PART_NO)

or you can control the data entry up front by binding the PART_NO field to a
combobox control in the form, and set the control's RowSource to tblDWGLOG.
if you're not familiar with combobox controls, add one to your form in
Design view and then read up on the following properties in Help, so you'll
understand how to use the control correctly and to best advantage:

RowSource
ColumnCount
ColumnWidths
BoundColumn
LimitToList

hth

 

[John W. Vinson]

In my form [frmWHEREUSE] there are four fields

PART_NO
NAME
USED_ON
QTY_USED

Before accepting (saving) record, I would like to check that the number in
one of the fields [USED_ON] is an exact value match to a number in our
Drawing Log [TABLE: tblDWGLOG FIELD: PART_NO]. How can I do this? Maybe
an After Update of the field, or upon focus of the next field [QYT_USED]?
Not sure what would be best. Please help with the code I would use. If the
number does not match, I do not want the record to be saved, but instead an
error message should appear.

Thank you so much for your generous time and assistance.

Rather than letting the user enter any arbitrary value in USED_ON and then
slapping their hand if they make a mistake, perhaps you should give the user a
Combo Box based on tblDWGLOG, so that they can *choose* a valid part number.
Set the combo box's Limit to List property to Yes and they won't be able to
enter an invalid value.

 

[kealaz]

Tina and John,

Thank you very much. These suggestions were very helpful. It's working
great now!

 

[tina]

you're welcome