Query in VB to Validate data

[fred]

I'm trying to validate data entered into a form field by using a
Query. I've discovered that the validation rule is pretty useless if
you need anything other than very basic validation.

I've tried using the following After Update Code:

Dim strSQL

strSQL = "SELECT tblProducts.ProductSRP FROM tblProducts"
strSQL = & "Where (((tblProducts.ProductID)=[Forms]![frmPeople]![Cases].[Form]![ProductID]));

if strSQL > Me.CompensationAmountTextBox.Value Then

MsgBox "The amount you entered is invalid"

Me.CompensationAmountTextBox.SetFocus
The idea is that this code validates the data entered and if invalid,
displays the msg box and sends the focus back to the field that
requires a valid entry.

I've also tried to get this to work by creating a Query and
referencing the Query from a Macro or with the expression builder,
with no success.
Can anyone suggest a way to do this?

Thanks,

Fred

[Wayne Morgan]

Let's try the BeforeUpdate event (of the control, not the form) instead and
we'll have to use a query function instead of the query.

LookupValue = DLookup("[ProductSRP]", "tblProducts", "[ProductID]=" &
Me!Cases.Form!ProductID
If LookupValue > Me.CompensationAmountTextBox.Value Then
MsgBox "The amount you entered is invalid", vbOkOnly + vbInformation,
"Data Error"
Cancel = True
Me.CompensationAmountTextBox.Undo
End If
--
Wayne Morgan
Microsoft Access MVP
"fred" <fr**@besttechsolution.com> wrote in message
news:cc**************************@posting.google.c om...

I'm trying to validate data entered into a form field by using a
Query. I've discovered that the validation rule is pretty useless if
you need anything other than very basic validation.

I've tried using the following After Update Code:

Dim strSQL

strSQL = "SELECT tblProducts.ProductSRP FROM tblProducts"
strSQL = & "Where (((tblProducts.ProductID)=[Forms]![frmPeople]![Cases].[Form]![ProductID]));
if strSQL > Me.CompensationAmountTextBox.Value Then

MsgBox "The amount you entered is invalid"

Me.CompensationAmountTextBox.SetFocus
The idea is that this code validates the data entered and if invalid,
displays the msg box and sends the focus back to the field that
requires a valid entry.

I've also tried to get this to work by creating a Query and
referencing the Query from a Macro or with the expression builder,
with no success.
Can anyone suggest a way to do this?

Thanks,

Fred

[Rick Brandt]

"fred" <fr**@besttechsolution.com> wrote in message
news:cc**************************@posting.google.c om...

I'm trying to validate data entered into a form field by using a
Query. I've discovered that the validation rule is pretty useless if
you need anything other than very basic validation.

I've tried using the following After Update Code:

Dim strSQL

strSQL = "SELECT tblProducts.ProductSRP FROM tblProducts"
strSQL = & "Where (((tblProducts.ProductID)=[Forms]![frmPeople]![Cases].[Form]![ProductID]));
if strSQL > Me.CompensationAmountTextBox.Value Then

Whoa... you're testing if a STRING is greater-than the value of a TextBox?

In the above, strSQL is equal to the SQL string "SELECT..." not the result of
executing that SQL. You need to use a Recordset based on that SQL or just use a
DLookup() function. Either of those will return the value of ProductSRP from
the table. Then you can compare that to the TextBox value.

You should also be doing this in BeforeUpdate, not AfterUpdate. As the name
suggest the AfterUpdate is too late. BeforeUpdate can be cancelled to prevent
the invalid entry from ever being committed.

Actually, if you used a DLookup() there is no reason a simple Validation Rule
shouldn't work here. The rule would be something like...

<DLookup("ProductSRP", "tblProducts", "ProductID = " & ProductID & "")

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Lyle Fairfield]

fr**@besttechsolution.com (fred) wrote in
news:cc**************************@posting.google.c om:

I'm trying to validate data entered into a form field by using a
Query. I've discovered that the validation rule is pretty useless if
you need anything other than very basic validation.

I've tried using the following After Update Code:

Dim strSQL

strSQL = "SELECT tblProducts.ProductSRP FROM tblProducts"
strSQL = & "Where (((tblProducts.ProductID)=[Forms]![frmPeople]![Cases]. [Form]![ProductID]));
if strSQL > Me.CompensationAmountTextBox.Value Then

MsgBox "The amount you entered is invalid"

Me.CompensationAmountTextBox.SetFocus
The idea is that this code validates the data entered and if invalid,
displays the msg box and sends the focus back to the field that
requires a valid entry.

I've also tried to get this to work by creating a Query and
referencing the Query from a Macro or with the expression builder,
with no success.
Can anyone suggest a way to do this?

Thanks,

Fred

You might consider how the > operator works on strings.

You might consider what:
strSQL = & "Where (((tblProducts.ProductID)=[Forms]![frmPeople]![Cases].
[Form]![ProductID]));
does to the value stored perviously at strSQL

and you might tell us what you are trying to do, as it's not so clear.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[fred]

Thanks for the quick suggestions!

I was able to add this to a validation rule. For some reason I could
not get the syntax correct using a before update event in VB.

Thanks much for the help!

"Rick Brandt" <ri*********@hotmail.com> wrote in message news:<bs************@ID-98015.news.uni-berlin.de>...

"fred" <fr**@besttechsolution.com> wrote in message
news:cc**************************@posting.google.c om...

I'm trying to validate data entered into a form field by using a
Query. I've discovered that the validation rule is pretty useless if
you need anything other than very basic validation.

I've tried using the following After Update Code:

Dim strSQL

strSQL = "SELECT tblProducts.ProductSRP FROM tblProducts"
strSQL = & "Where

(((tblProducts.ProductID)=[Forms]![frmPeople]![Cases].[Form]![ProductID]));

if strSQL > Me.CompensationAmountTextBox.Value Then

Whoa... you're testing if a STRING is greater-than the value of a TextBox?

In the above, strSQL is equal to the SQL string "SELECT..." not the result of
executing that SQL. You need to use a Recordset based on that SQL or just use a
DLookup() function. Either of those will return the value of ProductSRP from
the table. Then you can compare that to the TextBox value.

You should also be doing this in BeforeUpdate, not AfterUpdate. As the name
suggest the AfterUpdate is too late. BeforeUpdate can be cancelled to prevent
the invalid entry from ever being committed.

Actually, if you used a DLookup() there is no reason a simple Validation Rule
shouldn't work here. The rule would be something like...

<DLookup("ProductSRP", "tblProducts", "ProductID = " & ProductID & "")