Another Easy Access Questions

[Bob Quintal]

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!!!

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?

 

[AJ]

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!!!

 

[Jeff Boyce]

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.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


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

 

[AJ]

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.

 

[fredg]

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.

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.

 

[Linq Adams via AccessMonster.com]

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

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

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com