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

Verify Data Entry agaisnt a table

P: n/a
I have a field on a form that when a user enters a number, I want to verify
that the number is already in another table.

For example:
If a user enters 100 in the control Bidder, I want to check a table to see
if that number has been entered. If it has not, then I want to warn the user
and clear the data. If the number does exist in the table then nothing need
to happen.

The form is NOT based on the table that I want to verify agaisnt.

How would I do this?

Mike
Nov 16 '06 #1
Share this Question
Share on Google+
8 Replies


P: n/a
Any reason a combobox based on that field wouldn't work? just set the
Limit To List property to true, and use a valid SQL statement for the
control source. Something like

SELECT [FieldName]
FROM [MyTable]
ORDER BY [FieldName];

Nov 16 '06 #2

P: n/a
"SBC News Groups" <me@you.netwrote in message
news:Vr******************@newssvr29.news.prodigy.n et...
I have a field on a form that when a user enters a number, I want to
verify
that the number is already in another table.

For example:
If a user enters 100 in the control Bidder, I want to check a table to see
if that number has been entered. If it has not, then I want to warn the
user
and clear the data. If the number does exist in the table then nothing
need
to happen.

The form is NOT based on the table that I want to verify agaisnt.

How would I do this?

Mike
Use the BeforeUpdate event of the control.
In that event you could have something like:

(caution, aircode)
If IsNull(DLookUp("Bidder","tblSomeTable","Bidder=" & Me.Bidder)) Then
Msgbox "Sorry, That Entry Does Not Exist In tblSomeTable"
Me.Undo
Cancel = True
Endif

Fred Zuckerman
Nov 16 '06 #3

P: n/a

"Fred Zuckerman" <Zu********@sbcglobal.netwrote in message
news:lN*****************@newssvr13.news.prodigy.co m...
"SBC News Groups" <me@you.netwrote in message
news:Vr******************@newssvr29.news.prodigy.n et...
>I have a field on a form that when a user enters a number, I want to
verify
>that the number is already in another table.

For example:
If a user enters 100 in the control Bidder, I want to check a table to
see
if that number has been entered. If it has not, then I want to warn the
user
>and clear the data. If the number does exist in the table then nothing
need
>to happen.

The form is NOT based on the table that I want to verify agaisnt.

How would I do this?

Mike

Use the BeforeUpdate event of the control.
In that event you could have something like:

(caution, aircode)
If IsNull(DLookUp("Bidder","tblSomeTable","Bidder=" & Me.Bidder)) Then
Msgbox "Sorry, That Entry Does Not Exist In tblSomeTable"
Me.Undo
Cancel = True
Endif

Fred Zuckerman

Hi Fred,

Thanks for the code. I only have one hitch, I don't want to squash the whole
record, just the bidder number field. Using the Me.Undo kills the whole
record. If the number is not in the table, I want the message and then black
the control and set it to focus.

Mike
Nov 16 '06 #4

P: n/a

<pi********@hotmail.comwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...
Any reason a combobox based on that field wouldn't work? just set the
Limit To List property to true, and use a valid SQL statement for the
control source. Something like

SELECT [FieldName]
FROM [MyTable]
ORDER BY [FieldName];
A combo box would be to slow when I have over a hundred entries to choose
from. It is easier and quicker to have a user enter a number. Fred sent a
replay using the following:

(caution, aircode)
If IsNull(DLookUp("Bidder","tblSomeTable","Bidder=" & Me.Bidder)) Then
Msgbox "Sorry, That Entry Does Not Exist In tblSomeTable"
Me.Undo
Cancel = True
Endif

The only trouble is that it killing the whole record and all I want to do is
pop a message box, blank the one control called bidder_number and then set
it to be the focus so the user can re-enter a number.

If I can get the Me.Undo to work differently then it will be ok.

Mike
Nov 16 '06 #5

P: n/a
SBC News Groups wrote:
A combo box would be to slow when I have over a hundred entries to choose
from. It is easier and quicker to have a user enter a number.
If you have very slow computers, perhaps. In my apps with combo boxes
and limit to list = YES, I try to limit entries to 1000 and performance
is fine. A hundred is nothing.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me
Nov 16 '06 #6

P: n/a
"SBC News Groups" <me@you.netwrote in
news:D1******************@newssvr12.news.prodigy.c om:
>
"Fred Zuckerman" <Zu********@sbcglobal.netwrote in message
news:lN*****************@newssvr13.news.prodigy.co m...
>"SBC News Groups" <me@you.netwrote in message
news:Vr******************@newssvr29.news.prodigy. net...
>>I have a field on a form that when a user enters a number, I
want to
verify
>>that the number is already in another table.

For example:
If a user enters 100 in the control Bidder, I want to check
a table to see
if that number has been entered. If it has not, then I want
to warn the
user
>>and clear the data. If the number does exist in the table
then nothing
need
>>to happen.

The form is NOT based on the table that I want to verify
agaisnt.

How would I do this?

Mike

Use the BeforeUpdate event of the control.
In that event you could have something like:

(caution, aircode)
If IsNull(DLookUp("Bidder","tblSomeTable","Bidder=" &
Me.Bidder)) Then
Msgbox "Sorry, That Entry Does Not Exist In tblSomeTable"
Me.Undo
Cancel = True
Endif

Fred Zuckerman


Hi Fred,

Thanks for the code. I only have one hitch, I don't want to
squash the whole record, just the bidder number field. Using
the Me.Undo kills the whole record. If the number is not in
the table, I want the message and then black the control and
set it to focus.

Mike

Read the help on undo to see that me.controlname.undo is what
you want.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Nov 17 '06 #7

P: n/a
On Thu, 16 Nov 2006 19:35:57 GMT, "SBC News Groups" <me@you.netwrote:
>
<pi********@hotmail.comwrote in message
news:11**********************@b28g2000cwb.googleg roups.com...
>Any reason a combobox based on that field wouldn't work? just set the
Limit To List property to true, and use a valid SQL statement for the
control source. Something like

SELECT [FieldName]
FROM [MyTable]
ORDER BY [FieldName];

A combo box would be to slow when I have over a hundred entries to choose
from. It is easier and quicker to have a user enter a number. Fred sent a
replay using the following:
A user can enter the number in a combo in exactly the same manner as a text box.
They do not need to use the mouse, so there is no reduction in speed.

I generally use the KeyPress event of the combo to drop down the list when the
first character is typed (Me.MyCombo.DropDown). This often gives the user a
visual cue that the number they are typing is not in the list and allows them to
correct the number before the control is updated.

You also have the advantage of being able to use the NotInList event (if Limit
To List is set to True) to seemlessly handle the missing record situation in
many cases.

Wayne Gillespie
Gosford NSW Australia
Nov 17 '06 #8

P: n/a
Thanks that did what I was looking for.

Sorry for the delay...

Mike

"Bob Quintal" <rq******@sPAmpatico.cawrote in message
news:Xn**********************@66.150.105.47...
"SBC News Groups" <me@you.netwrote in
news:D1******************@newssvr12.news.prodigy.c om:
>>
"Fred Zuckerman" <Zu********@sbcglobal.netwrote in message
news:lN*****************@newssvr13.news.prodigy.c om...
>>"SBC News Groups" <me@you.netwrote in message
news:Vr******************@newssvr29.news.prodigy .net...
I have a field on a form that when a user enters a number, I
want to
verify
that the number is already in another table.

For example:
If a user enters 100 in the control Bidder, I want to check
a table to see
if that number has been entered. If it has not, then I want
to warn the
user
and clear the data. If the number does exist in the table
then nothing
need
to happen.

The form is NOT based on the table that I want to verify
agaisnt.

How would I do this?

Mike

Use the BeforeUpdate event of the control.
In that event you could have something like:

(caution, aircode)
If IsNull(DLookUp("Bidder","tblSomeTable","Bidder=" &
Me.Bidder)) Then
Msgbox "Sorry, That Entry Does Not Exist In tblSomeTable"
Me.Undo
Cancel = True
Endif

Fred Zuckerman


Hi Fred,

Thanks for the code. I only have one hitch, I don't want to
squash the whole record, just the bidder number field. Using
the Me.Undo kills the whole record. If the number is not in
the table, I want the message and then black the control and
set it to focus.

Mike

Read the help on undo to see that me.controlname.undo is what
you want.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Nov 21 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.