By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,995 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Query in VB to Validate data

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
"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
Nov 12 '05 #3

P: n/a
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)
Nov 12 '05 #4

P: n/a
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 & "")

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.