Another Easy Access Questions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AJ - 20 Jan 2008 18:06 GMT

Again, I am new to access so thank you all in advance for your help. Your
information and guidance is great.

I have an input form where I have a user input a store#. I then run a VBA
(onUpdate) to validate that the store exists in the table. I do this by
opening a recordset and then do an "if recordset.eof and recordset.bof then
msgbox "does not exist".

Here are my questions:
1. Is there a way to make the user only input numeric values? All the stores
are numeric and the recordset fails if a character is entered? Or maybe check
to see if it is a character and do not open recordset?

2. Is access quicker (better performance) if I use a dlookup instead of
recordset? If so, how do I then perform a "if else" depending on whether a
record is found. I think I tried this originally but the dlookup would fail
and I could not get passed it. An example would be great.

Thanks a ton!!!

Reply to this Message

Bob Quintal - 20 Jan 2008 18:02 GMT

> Again, I am new to access so thank you all in advance for your
> help. Your information and guidance is great.
[quoted text clipped - 17 lines]
>
> Thanks a ton!!!

1: use an input mask, or create an AfterUpdate event for the store
number that sets a flag based on the isnumeric() function to run the
Dlookup or recordset,

2: there's no noticeable difference. I prefer the DLookup() function
as it's less code to be analysed when upgrading an old applicaion.
and is 'self-documenting' that you are looking up some data.

Example Code

if Isnumeric(Me.[Store#]) then
if isnull(Dlookup("[store#]","Stores", _
"[store#] = & me![store#]) then
msgbox "No Store with this Number"
else
....
end if
end if

3: Have you considered changing your store number textbox to a much
more user-friendly combobox that limits the entry to the list of
store numbers?

Signature

Bob Quintal

PA is y I've altered my email address.

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

Reply to this Message

Jeff Boyce - 20 Jan 2008 18:28 GMT

AJ

There may be an easier way, one that doesn't require users to memorize
store#s, doesn't require a opening a recordset, and doesn't require a
DLookup.

Use a combobox that lists all valid stores (use a query against a "stores"
table). This way, the user simply finds the right store. If this is on an
"input form", you can bind the combobox to the underlying field that will be
holding the storeID.

Signature

Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

> Again, I am new to access so thank you all in advance for your help. Your
> information and guidance is great.
[quoted text clipped - 15 lines]
>
> Thanks a ton!!!

Reply to this Message

AJ - 20 Jan 2008 18:48 GMT

Thanks Jeff, but I thought about that and discussed it with the user and
since there are 20,000+ stores we decided that was not a good option. They
will be running this for specific stores and will know them.

> AJ
>
[quoted text clipped - 30 lines]
> >
> > Thanks a ton!!!

Reply to this Message

fredg - 20 Jan 2008 19:36 GMT

> Thanks Jeff, but I thought about that and discussed it with the user and
> since there are 20,000+ stores we decided that was not a good option. They
[quoted text clipped - 34 lines]
>>>
>>> Thanks a ton!!!

Jeff's reply is still a better method.
If your user wishes only certain stores to be available for user to
select from, then simply filter the rowsource query to return just
those stores (or create a table with just those store numbers), or if
there are just a very few, set the rowsource type to a Value list, and
simply enter the values directly into the rowsource.

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

Linq Adams - 20 Jan 2008 19:44 GMT

Another approach would simply be to not allow non-digits to be entered in the
textbox. This code will only allow 0-9 plus a few "navigating keys" in case
the user has to move back or forth to correct an error:

Private Sub YourTextBox_KeyDown(KeyCode As Integer, Shift As Integer)

Select Case KeyCode

Case 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 96, 97, 98, 99, 100, 101, 102,
103, 104, 105

Case vbKeyDelete, vbKeyBack, vbKeyReturn, vbKeyRight, vbKeyLeft

Case Else

KeyCode = 0
MsgBox "You Must Enter Digits Only, Dummy!"

End Select

End Sub

The messagebox is optional, of course, depending on how much you want to tick
off your end users! Without it, nothing is entered unless it's a digit.

Linq

Signature

There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Reply to this Message