Validate form data

[David Bowling]

I would like to validate data in certain fields on a form using a validation
rule (or any other method) so the error message appears when the field loses
the focus, rather than waiting for the referential integrity of the
relationship to catch the error when attempting to add the record.

For example:
I have a "User" table that that has a one-to-many relationship to a "Scan"
table on the user id field. The user id field will accept any input that
meets the size and data type criteria. However, I want an error message to
appear if the value does not exist in the related table at the time of input,
rather than waiting for all other fields to be populated.

Any ideas are greatly appreciated!

Dave

 

[Marshall Barton]

I would like to validate data in certain fields on a form using a validation
rule (or any other method) so the error message appears when the field loses
the focus, rather than waiting for the referential integrity of the
relationship to catch the error when attempting to add the record.

For example:
I have a "User" table that that has a one-to-many relationship to a "Scan"
table on the user id field. The user id field will accept any input that
meets the size and data type criteria. However, I want an error message to
appear if the value does not exist in the related table at the time of input,
rather than waiting for all other fields to be populated.

You can use the control's BeforeUpdate event to run some
code. The code could use DLookup to search the other table
and do a MsgBox if it's not found. The BeforeUpdate event
provides a Cancel argument that you can set to True to
prevent the focus from moving to another control until a
valid value is entered or the user hits Esc to undo the
entry.

With most UI designs, this is all quite unnecessary as far
as your example goes. By making the scan table's form a
subform of the user table's form, the LinkMaster/Child
properties would natually lead to only valid entries.

 

[David Bowling]

Marshall,

Thank you for your help and the quick response time!
I understand everything you are telling me but I am having trouble with the
syntax of the DLookup function.

My form (called "Scan Form")is based off a query called Q_Scan which has the
Scan table and User table joined with a one-to-many relationship on user id.

[Scan]![S_User] and [User]![Id]

I am trying to validate that S_User exists in the User table. I reviewed
the help on Dlookup and have tried a multitude of scenarios with no success.
Thanks again! Your help is greatly appreciated!

 

[Marshall Barton]

Assuming the ID field is a numeric type, I think I would do
it like this:

If DCount("*", "User", "ID=" & Me.S_User) = 0 Then
MsgBox "Invalid user ID" & vbCrLf & "Try again"
Cancel = True
End If

But if you prefer:

If IsNull(DLookup("ID", "User", "ID=" & Me.S_User)) Then
. . .
--
Marsh
MVP [MS Access]

Thank you for your help and the quick response time!
I understand everything you are telling me but I am having trouble with the
syntax of the DLookup function.

My form (called "Scan Form")is based off a query called Q_Scan which has the
Scan table and User table joined with a one-to-many relationship on user id.

[Scan]![S_User] and [User]![Id]

I am trying to validate that S_User exists in the User table. I reviewed
the help on Dlookup and have tried a multitude of scenarios with no success.

You can use the control's BeforeUpdate event to run some
code. The code could use DLookup to search the other table
and do a MsgBox if it's not found. The BeforeUpdate event
provides a Cancel argument that you can set to True to
prevent the focus from moving to another control until a
valid value is entered or the user hits Esc to undo the
entry.

With most UI designs, this is all quite unnecessary as far
as your example goes. By making the scan table's form a
subform of the user table's form, the LinkMaster/Child
properties would natually lead to only valid entries.

 

[David Bowling]

Marshall,

I used the following syntax as a condition in a macro.
DLookUp("Id","User","Id= [Forms]![Scan Form].[S_User]") Is Null. This works
great!. The macro allows me to open a form (with a timer) to display a user
friendly error message, then close the form, change the focus back to user id
field and clear the invalid data. This way, the fork-lift operators will not
have to get down off their truck to use the mouse to clear an error. This
really makes the interface user friendly!

Thanks again. You rock!

Dave

Assuming the ID field is a numeric type, I think I would do
it like this:

If DCount("*", "User", "ID=" & Me.S_User) = 0 Then
MsgBox "Invalid user ID" & vbCrLf & "Try again"
Cancel = True
End If

But if you prefer:

If IsNull(DLookup("ID", "User", "ID=" & Me.S_User)) Then
. . .
--
Marsh
MVP [MS Access]

Thank you for your help and the quick response time!
I understand everything you are telling me but I am having trouble with the
syntax of the DLookup function.

My form (called "Scan Form")is based off a query called Q_Scan which has the
Scan table and User table joined with a one-to-many relationship on user id.

[Scan]![S_User] and [User]![Id]

I am trying to validate that S_User exists in the User table. I reviewed
the help on Dlookup and have tried a multitude of scenarios with no success.

:
I would like to validate data in certain fields on a form using a validation
rule (or any other method) so the error message appears when the field loses
the focus, rather than waiting for the referential integrity of the
relationship to catch the error when attempting to add the record.

For example:
I have a "User" table that that has a one-to-many relationship to a "Scan"
table on the user id field. The user id field will accept any input that
meets the size and data type criteria. However, I want an error message to
appear if the value does not exist in the related table at the time of input,
rather than waiting for all other fields to be populated.

You can use the control's BeforeUpdate event to run some
code. The code could use DLookup to search the other table
and do a MsgBox if it's not found. The BeforeUpdate event
provides a Cancel argument that you can set to True to
prevent the focus from moving to another control until a
valid value is entered or the user hits Esc to undo the
entry.

With most UI designs, this is all quite unnecessary as far
as your example goes. By making the scan table's form a
subform of the user table's form, the LinkMaster/Child
properties would natually lead to only valid entries.