Verify Data Entry agaisnt a table

**pietlinden@hotmail.com** · Nov 16 '06, 06:55 PM

Re: Verify Data Entry agaisnt a table

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];

**Fred Zuckerman** · Nov 16 '06, 06:55 PM

Re: Verify Data Entry agaisnt a table

"SBC News Groups" <me@you.netwrot e in message
news:Vr27h.1115 9$9v5.5348@news svr29.news.prod igy.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","tblSo meTable","Bidde r=" & Me.Bidder)) Then
Msgbox "Sorry, That Entry Does Not Exist In tblSomeTable"
Me.Undo
Cancel = True
Endif

Fred Zuckerman

**SBC News Groups** · Nov 16 '06, 07:15 PM

Re: Verify Data Entry agaisnt a table

"Fred Zuckerman" <ZuckermanF@sbc global.netwrote in message
news:lN27h.6806 $Sw1.3616@newss vr13.news.prodi gy.com...

"SBC News Groups" <me@you.netwrot e in message
news:Vr27h.1115 9$9v5.5348@news svr29.news.prod igy.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","tblSo meTable","Bidde r=" & 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

**SBC News Groups** · Nov 16 '06, 07:15 PM

Re: Verify Data Entry agaisnt a table

<pietlinden@hot mail.comwrote in message
news:1163704532 .383995.169690@ b28g2000cwb.goo glegroups.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","tblSo meTable","Bidde r=" & 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

**Tim Marshall** · Nov 16 '06, 08:45 PM

Re: Verify Data Entry agaisnt a table

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

**Bob Quintal** · Nov 17 '06, 12:15 AM

Re: Verify Data Entry agaisnt a table

"SBC News Groups" <me@you.netwrot e in
news:D137h.1165 8$yl4.3741@news svr12.news.prod igy.com:

>
"Fred Zuckerman" <ZuckermanF@sbc global.netwrote in message
news:lN27h.6806 $Sw1.3616@newss vr13.news.prodi gy.com...

>"SBC News Groups" <me@you.netwrot e in message
>news:Vr27h.111 59$9v5.5348@new ssvr29.news.pro digy.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","tblSo meTable","Bidde r=" &
>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

**Wayne Gillespie** · Nov 17 '06, 04:25 AM

Re: Verify Data Entry agaisnt a table

On Thu, 16 Nov 2006 19:35:57 GMT, "SBC News Groups" <me@you.netwrot e:

>
><pietlinden@ho tmail.comwrote in message
>news:116370453 2.383995.169690 @b28g2000cwb.go oglegroups.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.Dro pDown). 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

**SBC News Groups** · Nov 21 '06, 08:35 PM

Re: Verify Data Entry agaisnt a table

Thanks that did what I was looking for.

Sorry for the delay...

Mike

"Bob Quintal" <rquintal@sPAmp atico.cawrote in message
news:Xns987DC6E 349E5EBQuintal@ 66.150.105.47.. .

"SBC News Groups" <me@you.netwrot e in
news:D137h.1165 8$yl4.3741@news svr12.news.prod igy.com:
>

>>
>"Fred Zuckerman" <ZuckermanF@sbc global.netwrote in message
>news:lN27h.680 6$Sw1.3616@news svr13.news.prod igy.com...

>>"SBC News Groups" <me@you.netwrot e in message
>>news:Vr27h.11 159$9v5.5348@ne wssvr29.news.pr odigy.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","tblSo meTable","Bidde r=" &
>>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
>

Verify Data Entry agaisnt a table

Verify Data Entry agaisnt a table

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment