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

Error in Validation Rule

P: n/a
A97. A database has a table: tblA which has a single text field, B. It is a
primary field, indexed and no duplicates. It is used as a lookup for table
tblC. A form based on tblA is used to add new records. The Validation Rule:
<>[Tables]![tblA]![b] was made using the expression builder. The rule produces
this error:
The expression [Tables] you entered in the Form control's Validation Rule
doesn't contain the Automation object 'Tables'

If I use simply: <>[b], it rejects everything because as soon as the focus is
moved from the text box to the close form command button, the value in the text
box is entered into the table producing a validation error.

What can I do to allow 'new' records to be entered into the table and to block
all duplicates and to have an error message display to say that the record
already exists?

All help will be greatly appreciated.

Just a wizard prodder
Chuck
--

Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Chuck wrote:
A97. A database has a table: tblA which has a single text field, B.
It is a primary field, indexed and no duplicates. It is used as a
lookup for table tblC. A form based on tblA is used to add new
records. The Validation Rule: <>[Tables]![tblA]![b] was made using
the expression builder. The rule produces this error:
The expression [Tables] you entered in the Form control's Validation
Rule doesn't contain the Automation object 'Tables'

If I use simply: <>[b], it rejects everything because as soon as the
focus is moved from the text box to the close form command button,
the value in the text box is entered into the table producing a
validation error.

What can I do to allow 'new' records to be entered into the table and
to block all duplicates and to have an error message display to say
that the record already exists?

All help will be greatly appreciated.


The fact that [b] is the PK and has a unique index will already prevent
duplicates. You don't need the validation rule at all.

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

P: n/a
On Sun, 29 May 2005 14:11:21 GMT, "Rick Brandt" <ri*********@hotmail.com>
wrote:
Chuck wrote:
A97. A database has a table: tblA which has a single text field, B.
It is a primary field, indexed and no duplicates. It is used as a
lookup for table tblC. A form based on tblA is used to add new
records. The Validation Rule: <>[Tables]![tblA]![b] was made using
the expression builder. The rule produces this error:
The expression [Tables] you entered in the Form control's Validation
Rule doesn't contain the Automation object 'Tables'

If I use simply: <>[b], it rejects everything because as soon as the
focus is moved from the text box to the close form command button,
the value in the text box is entered into the table producing a
validation error.

What can I do to allow 'new' records to be entered into the table and
to block all duplicates and to have an error message display to say
that the record already exists?

All help will be greatly appreciated.


The fact that [b] is the PK and has a unique index will already prevent
duplicates. You don't need the validation rule at all.


Your'e correct. But the data entery person doesn't know this because no
message of any type comes up. All that happens is that when you click 'Enter'
or 'Tab' the text box goes blank. The form stays open because it has its own
close command button. They could assume that the data has been entered, which
may not be all that bad since the data is in fact there. Maybe I'm trying to
gild the lily.

Just a wizard prodder
Chuck
--

Nov 13 '05 #3

P: n/a

"Chuck" <li*****@schoollink.net> wrote in message
news:fj********************************@4ax.com...
duplicates. You don't need the validation rule at all.

Your'e correct. But the data entery person doesn't know this because no
message of any type comes up. All that happens is that when you click 'Enter'
or 'Tab' the text box goes blank. The form stays open because it has its own
close command button. They could assume that the data has been entered, which
may not be all that bad since the data is in fact there. Maybe I'm trying to
gild the lily.


Then you must have changed a setting that is preventing it. Attempting to enter
a duplicate value against a Primary Key or a field with a unique index has
always produced an error message for me.

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

P: n/a
On Sun, 29 May 2005 15:53:12 GMT, "Rick Brandt" <ri*********@hotmail.com>
wrote:

"Chuck" <li*****@schoollink.net> wrote in message
news:fj********************************@4ax.com.. .
duplicates. You don't need the validation rule at all.

Your'e correct. But the data entery person doesn't know this because no
message of any type comes up. All that happens is that when you click 'Enter'
or 'Tab' the text box goes blank. The form stays open because it has its own
close command button. They could assume that the data has been entered, which
may not be all that bad since the data is in fact there. Maybe I'm trying to
gild the lily.


Then you must have changed a setting that is preventing it. Attempting to enter
a duplicate value against a Primary Key or a field with a unique index has
always produced an error message for me.

If I did, I don't know what, when why, or how. I've checked every option,
setting and can't find anything that would let me turn warnings on or off.
I'm open to suggestions.

Just a wizard prodder
Chuck
--

Nov 13 '05 #5

P: n/a
"Chuck" <li*****@schoollink.net> wrote in message
news:nn********************************@4ax.com...
On Sun, 29 May 2005 15:53:12 GMT,
"Rick Brandt" <ri*********@hotmail.com> wrote:
Then you must have changed a setting that is preventing it. Attempting to
enter
a duplicate value against a Primary Key or a field with a unique index has
always produced an error message for me.

If I did, I don't know what, when why, or how. I've checked every option,
setting and can't find anything that would let me turn warnings on or off.
I'm open to suggestions.


Is there a specific reason why you created a custom Close button? That is why
the record is being discarded silently. There has been a bug going back several
versions now wherein using DoCmd.Close to close a form will silently discard a
bad record instead of alerting the user to the problem. If you were to close
with the X in the upper right you would get an error message.

You can keep the current button if you modify it to save the record before the
line that does the close. The line you need to add is...

DoCmd.RunCommand acCmdSaveRecord

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

Nov 13 '05 #6

P: n/a
On Mon, 30 May 2005 13:57:21 GMT, "Rick Brandt" <ri*********@hotmail.com>
wrote:
"Chuck" <li*****@schoollink.net> wrote in message
news:nn********************************@4ax.com.. .
On Sun, 29 May 2005 15:53:12 GMT,
"Rick Brandt" <ri*********@hotmail.com> wrote:
Then you must have changed a setting that is preventing it. Attempting to
enter
a duplicate value against a Primary Key or a field with a unique index has
always produced an error message for me.

If I did, I don't know what, when why, or how. I've checked every option,
setting and can't find anything that would let me turn warnings on or off.
I'm open to suggestions.


Is there a specific reason why you created a custom Close button? That is why
the record is being discarded silently. There has been a bug going back several
versions now wherein using DoCmd.Close to close a form will silently discard a
bad record instead of alerting the user to the problem. If you were to close
with the X in the upper right you would get an error message.

You can keep the current button if you modify it to save the record before the
line that does the close. The line you need to add is...

DoCmd.RunCommand acCmdSaveRecord

Works a charm. Thank You.

Chuck
--
Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.