473,320 Members | 2,083 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Verify Data Entry agaisnt a table

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
8 2183
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
"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

"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

<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
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
"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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
by: heromull | last post by:
We have an asp.net app with about 200 data entry forms. Customers may enter data into any number of forms. Each form's data is persisted in a corresponding sql table. When data entry is...
2
by: Iain Miller | last post by:
Struggling a bit here & would be grateful for any help. I have a table which has a list of people in it. Each person has a unique ID automatically allocated by Access but also belongs to one of 5...
4
by: MX1 | last post by:
Hi all, I've setup a table with one field that will hold percent values. The type is number and the format is percent on the field. When I do data entry directly into the field, I have to put...
1
by: Alex.Wisnoski | last post by:
I have a data entry form with a combo box to look up an entrant's name. If the name is already in the table then it pulls up the record and that part of the form works fine. If the name isn't in...
0
by: tamilan71 | last post by:
Can anyone tell me a simple method of creating an application in Access 2003 that uses "Double data entry"? For me double data entry is defined as the ability to have data entered twice. The first...
2
by: filbennett | last post by:
Hi Everyone, I'm generally unfamiliar with Access form design, but have programmed Cold Fusion applications for a couple of years. I'd like to build a data entry form in Access that allows the...
20
by: hippomedon | last post by:
Hello everyone, I'm looking for some advice on whether I should break the normalization rule. Normally, I would not consider it, but this seems to be a special case. I have created an...
2
by: manivelk | last post by:
Hi, i want to know how to display the table control at runtime data entry type.. If any one knows plz help me.. Thanks in advance.. Regards, Manivel. K
1
by: admin.offshoredataentry | last post by:
Data Entry Outsourcing provides time bound, cost effective and qualitative Data Entry also provides numeric data entry, textual data entry, image data entry, data format, data conversion and also...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.