Field validation based on the field values from other table

[Guest]

I'd like to validate a field on a form with the field values from other table.
For example : name field on a form
Validation list is the name column on another tables.
Are there any ways to do this kind of validation

 

[Guest]

On the before update event of the field, you can use Dlookup to retrieve the
value from the second table, and compare it to the value entered to the field
in the form

If Me.[TextBoxName] = DLookUp("[FieldName]","[TableName]","[Criteria]") Then
' Equal to the value in the table
Else
' Not Equal to the value in the table
End If

In the [Criteria] you should specify which record you want to retrieve from
the table

 

[Guest]

Hm... But I only want to check if the name user type exists in the name
table. That means the criteria is not only a single value but a range of
values (about 400 entries). Do you mind to advise how I may do it? Also, how
I may ask the user to retype the value if the input value is not in the list.
Thank you for your kindness.

On the before update event of the field, you can use Dlookup to retrieve the
value from the second table, and compare it to the value entered to the field
in the form

If Me.[TextBoxName] = DLookUp("[FieldName]","[TableName]","[Criteria]") Then
' Equal to the value in the table
Else
' Not Equal to the value in the table
End If

In the [Criteria] you should specify which record you want to retrieve from
the table

--
\\// Live Long and Prosper \\//

I'd like to validate a field on a form with the field values from other table.
For example : name field on a form
Validation list is the name column on another tables.
Are there any ways to do this kind of validation

 

[Guest]

In that case, on the before update event of the textbox in the form, write
the code

If IsNull(DLookUp("[Name Field]", "[TableName]","[Name Field] = '" &
Me.[TextBoxName] & "'")) Then
MsgBox "Name does not exist"
cancel = True ' wont let the user continue
End If

--
\\// Live Long and Prosper \\//

Hm... But I only want to check if the name user type exists in the name
table. That means the criteria is not only a single value but a range of
values (about 400 entries). Do you mind to advise how I may do it? Also, how
I may ask the user to retype the value if the input value is not in the list.
Thank you for your kindness.

On the before update event of the field, you can use Dlookup to retrieve the
value from the second table, and compare it to the value entered to the field
in the form

If Me.[TextBoxName] = DLookUp("[FieldName]","[TableName]","[Criteria]") Then
' Equal to the value in the table
Else
' Not Equal to the value in the table
End If

In the [Criteria] you should specify which record you want to retrieve from
the table

--
\\// Live Long and Prosper \\//

I'd like to validate a field on a form with the field values from other table.
For example : name field on a form
Validation list is the name column on another tables.
Are there any ways to do this kind of validation

 

[Guest]

Ofer,
Thanks a lot for helping a beginner like me.

In that case, on the before update event of the textbox in the form, write
the code

If IsNull(DLookUp("[Name Field]", "[TableName]","[Name Field] = '" &
Me.[TextBoxName] & "'")) Then
MsgBox "Name does not exist"
cancel = True ' wont let the user continue
End If

--
\\// Live Long and Prosper \\//

Hm... But I only want to check if the name user type exists in the name
table. That means the criteria is not only a single value but a range of
values (about 400 entries). Do you mind to advise how I may do it? Also, how
I may ask the user to retype the value if the input value is not in the list.
Thank you for your kindness.

On the before update event of the field, you can use Dlookup to retrieve the
value from the second table, and compare it to the value entered to the field
in the form

If Me.[TextBoxName] = DLookUp("[FieldName]","[TableName]","[Criteria]") Then
' Equal to the value in the table
Else
' Not Equal to the value in the table
End If

In the [Criteria] you should specify which record you want to retrieve from
the table

--
\\// Live Long and Prosper \\//


:

I'd like to validate a field on a form with the field values from other table.
For example : name field on a form
Validation list is the name column on another tables.
Are there any ways to do this kind of validation

 

[Guest]

However, I got another question on how to prevent the user to continue to
other fields after entering incorrect names. I'd like to stop users from
entering other information before entering correct information on the name
field.

In that case, on the before update event of the textbox in the form, write
the code

If IsNull(DLookUp("[Name Field]", "[TableName]","[Name Field] = '" &
Me.[TextBoxName] & "'")) Then
MsgBox "Name does not exist"
cancel = True ' wont let the user continue
End If

--
\\// Live Long and Prosper \\//

Hm... But I only want to check if the name user type exists in the name
table. That means the criteria is not only a single value but a range of
values (about 400 entries). Do you mind to advise how I may do it? Also, how
I may ask the user to retype the value if the input value is not in the list.
Thank you for your kindness.

On the before update event of the field, you can use Dlookup to retrieve the
value from the second table, and compare it to the value entered to the field
in the form

If Me.[TextBoxName] = DLookUp("[FieldName]","[TableName]","[Criteria]") Then
' Equal to the value in the table
Else
' Not Equal to the value in the table
End If

In the [Criteria] you should specify which record you want to retrieve from
the table

--
\\// Live Long and Prosper \\//


:

I'd like to validate a field on a form with the field values from other table.
For example : name field on a form
Validation list is the name column on another tables.
Are there any ways to do this kind of validation

 

[Guest]

Your welcome, goodluck and have a great weekend

--
\\// Live Long and Prosper \\//

Ofer,
Thanks a lot for helping a beginner like me.

In that case, on the before update event of the textbox in the form, write
the code

If IsNull(DLookUp("[Name Field]", "[TableName]","[Name Field] = '" &
Me.[TextBoxName] & "'")) Then
MsgBox "Name does not exist"
cancel = True ' wont let the user continue
End If

--
\\// Live Long and Prosper \\//

Hm... But I only want to check if the name user type exists in the name
table. That means the criteria is not only a single value but a range of
values (about 400 entries). Do you mind to advise how I may do it? Also, how
I may ask the user to retype the value if the input value is not in the list.
Thank you for your kindness.

:

On the before update event of the field, you can use Dlookup to retrieve the
value from the second table, and compare it to the value entered to the field
in the form

If Me.[TextBoxName] = DLookUp("[FieldName]","[TableName]","[Criteria]") Then
' Equal to the value in the table
Else
' Not Equal to the value in the table
End If

In the [Criteria] you should specify which record you want to retrieve from
the table

--
\\// Live Long and Prosper \\//


:

I'd like to validate a field on a form with the field values from other table.
For example : name field on a form
Validation list is the name column on another tables.
Are there any ways to do this kind of validation

 

[Guest]

This is why you get the cancel = True, that won't let the user continue to
the next field

--
\\// Live Long and Prosper \\//

However, I got another question on how to prevent the user to continue to
other fields after entering incorrect names. I'd like to stop users from
entering other information before entering correct information on the name
field.

In that case, on the before update event of the textbox in the form, write
the code

If IsNull(DLookUp("[Name Field]", "[TableName]","[Name Field] = '" &
Me.[TextBoxName] & "'")) Then
MsgBox "Name does not exist"
cancel = True ' wont let the user continue
End If

--
\\// Live Long and Prosper \\//

Hm... But I only want to check if the name user type exists in the name
table. That means the criteria is not only a single value but a range of
values (about 400 entries). Do you mind to advise how I may do it? Also, how
I may ask the user to retype the value if the input value is not in the list.
Thank you for your kindness.

:

On the before update event of the field, you can use Dlookup to retrieve the
value from the second table, and compare it to the value entered to the field
in the form

If Me.[TextBoxName] = DLookUp("[FieldName]","[TableName]","[Criteria]") Then
' Equal to the value in the table
Else
' Not Equal to the value in the table
End If

In the [Criteria] you should specify which record you want to retrieve from
the table

--
\\// Live Long and Prosper \\//


:

I'd like to validate a field on a form with the field values from other table.
For example : name field on a form
Validation list is the name column on another tables.
Are there any ways to do this kind of validation

 

[Guest]

Ofer ,
Yes, I know it from your comment. However, it doesn't work on my form.
Are there any other ways in doing so. Thank you for your kindness again.

This is why you get the cancel = True, that won't let the user continue to
the next field

--
\\// Live Long and Prosper \\//

However, I got another question on how to prevent the user to continue to
other fields after entering incorrect names. I'd like to stop users from
entering other information before entering correct information on the name
field.

In that case, on the before update event of the textbox in the form, write
the code

If IsNull(DLookUp("[Name Field]", "[TableName]","[Name Field] = '" &
Me.[TextBoxName] & "'")) Then
MsgBox "Name does not exist"
cancel = True ' wont let the user continue
End If

--
\\// Live Long and Prosper \\//


:

Hm... But I only want to check if the name user type exists in the name
table. That means the criteria is not only a single value but a range of
values (about 400 entries). Do you mind to advise how I may do it? Also, how
I may ask the user to retype the value if the input value is not in the list.
Thank you for your kindness.

:

On the before update event of the field, you can use Dlookup to retrieve the
value from the second table, and compare it to the value entered to the field
in the form

If Me.[TextBoxName] = DLookUp("[FieldName]","[TableName]","[Criteria]") Then
' Equal to the value in the table
Else
' Not Equal to the value in the table
End If

In the [Criteria] you should specify which record you want to retrieve from
the table

--
\\// Live Long and Prosper \\//


:

I'd like to validate a field on a form with the field values from other table.
For example : name field on a form
Validation list is the name column on another tables.
Are there any ways to do this kind of validation

 

[Guest]

Ofer,
Sorry to mislead you. It works while I mistyped the before update event to
after update one. Have a nice weekend.

Ofer ,
Yes, I know it from your comment. However, it doesn't work on my form.
Are there any other ways in doing so. Thank you for your kindness again.

This is why you get the cancel = True, that won't let the user continue to
the next field

--
\\// Live Long and Prosper \\//

However, I got another question on how to prevent the user to continue to
other fields after entering incorrect names. I'd like to stop users from
entering other information before entering correct information on the name
field.

:

In that case, on the before update event of the textbox in the form, write
the code

If IsNull(DLookUp("[Name Field]", "[TableName]","[Name Field] = '" &
Me.[TextBoxName] & "'")) Then
MsgBox "Name does not exist"
cancel = True ' wont let the user continue
End If

--
\\// Live Long and Prosper \\//


:

Hm... But I only want to check if the name user type exists in the name
table. That means the criteria is not only a single value but a range of
values (about 400 entries). Do you mind to advise how I may do it? Also, how
I may ask the user to retype the value if the input value is not in the list.
Thank you for your kindness.

:

On the before update event of the field, you can use Dlookup to retrieve the
value from the second table, and compare it to the value entered to the field
in the form

If Me.[TextBoxName] = DLookUp("[FieldName]","[TableName]","[Criteria]") Then
' Equal to the value in the table
Else
' Not Equal to the value in the table
End If

In the [Criteria] you should specify which record you want to retrieve from
the table

--
\\// Live Long and Prosper \\//


:

I'd like to validate a field on a form with the field values from other table.
For example : name field on a form
Validation list is the name column on another tables.
Are there any ways to do this kind of validation