Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-13-2019, 04:23 AM   #1
murray83
sega collector
 
Join Date: Mar 2017
Location: Daventry, UK
Posts: 444
Thanks: 106
Thanked 1 Time in 1 Post
murray83 is on a distinguished road
only allow correct data input

hi one and all

i have a question i would like answering if possible.

i have a form on a database (which when opens looks like Enter 0 ) when the user of said database inputs a supplier number as shown in the picture called Enter 1 and its correct as it shows the name in the box which has a dlookup based on the inputted info and lets you carry on unlocking the following text boxes

my question is can i have an if/dlookup which if the user inputs a random string of 6 numbers ( that's the length of the supplier codes ) it doesn't work, because at the moment it does as you can see in the picture called Enter 2 random number and still unlocks next boxes for input

was thinking of maybe an if when the dlookup is = to "" but have searched here and had a quick scout of google to no avail just talks about dlookup to check if record exist before adding and im not adding just want to check the supplier is correct

or would it be much, much simpler to change the text box to a combo and then just restrict to what is in the list

cheers for reading and answers on a postcard

ta
Attached Images
File Type: jpg enter0.jpg (20.5 KB, 19 views)
File Type: jpg enter1.jpg (20.6 KB, 16 views)
File Type: jpg enter2.jpg (19.0 KB, 17 views)

murray83 is offline   Reply With Quote
Old 06-13-2019, 04:28 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,780
Thanks: 57
Thanked 1,267 Times in 1,248 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: only allow correct data input

Hi. You can do either approach, but I also think using a dropdown would be simpler and you won’t have to use DLookup().
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
murray83 (06-13-2019)
Old 06-13-2019, 05:02 AM   #3
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,551
Thanks: 92
Thanked 1,682 Times in 1,560 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: only allow correct data input

Oh, if ONLY we had a function that would prevent users from inputting bad data. At the university computer lab we used to fantasize about machine-correcting input and even knew the name of the instruction to use for bad input... XOI (Execute Operator Immediately). But I digress...

The philosophy that relates to this question is CHOICE. Don't give your users more choices than they need. I'm with theDBguy... offer a list of choices rather than take free-form input. The fewer options a user has, the fewer mistaken entries they can make. And that always works to your advantage in the long run even if it takes a little extra time to set up and might even require diddling with the size, shape, look, and feel of your form.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 06-13-2019, 06:06 AM   #4
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,065
Thanks: 10
Thanked 218 Times in 206 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: only allow correct data input

IMHO, depends on how many choices. 1,000? That's too many for a combo. Cascading combos might take care of that. If your lookup isn't working, it probably isn't filtered correctly, or isn't at all, or the result isn't handled correctly.
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 06-13-2019, 06:15 AM   #5
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,232
Thanks: 92
Thanked 2,025 Times in 1,972 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: only allow correct data input

I agree with the other responders ---avoid manual user entry where/if possible (typos,malcontents...). Provide options for selection where possible. If you have to isolate vast possibilities into categories/classifications/types or whatever, my view is it is probably worth that analysis and design effort, rather than allowing bad data to be entered. But as always, the devil is in the details (volumes, training, users, criteria...)
Good luck.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 06-13-2019, 06:37 AM   #6
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,551
Thanks: 92
Thanked 1,682 Times in 1,560 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: only allow correct data input

While I agree with Micron, it should be possible for you to put an index on the name of the supplier, bind the value of the combo to the supplier's ID, but show the supplier name and allow the combo to fast-index if you input the first couple of letters of that name. Then, though you might have a pot-load of suppliers, you can still pick one pretty quickly.

Another thing that is possible is to note in the textbox LostFocus event whether the value is meaningful and to take special action if not. What that special action would be? Up to you. What I did in a similar situation was change the background of the textbox to Yellow (and the border to Red) and set a flag that would stop the user from saving the record until the error was corrected. But that might or might not work for your situation.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 06-13-2019, 06:47 AM   #7
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,343
Thanks: 432
Thanked 794 Times in 769 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: only allow correct data input

Quote:
Originally Posted by Micron View Post
IMHO, depends on how many choices. 1,000? That's too many for a combo. Cascading combos might take care of that. If your lookup isn't working, it probably isn't filtered correctly, or isn't at all, or the result isn't handled correctly.
I have 4002 crew names in my DB and I used a combo.?
As I typed I would get to the correct entry or get the 'Not in List' message.?
Whilst I was using Surname,Initials, sure the same would work for supplier number.?

__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
The Following User Says Thank You to Gasman For This Useful Post:
murray83 (06-14-2019)
Old 06-13-2019, 07:05 AM   #8
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,065
Thanks: 10
Thanked 218 Times in 206 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: only allow correct data input

FAYT isn't really the same thing is it? As you type, you modify the row source which means the list is no longer 4000 items. I was referring to a list where the row source provides that many list items.
Micron is offline   Reply With Quote
Old 06-13-2019, 07:21 AM   #9
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,343
Thanks: 432
Thanked 794 Times in 769 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: only allow correct data input

Quote:
Originally Posted by Micron View Post
FAYT isn't really the same thing is it? As you type, you modify the row source which means the list is no longer 4000 items. I was referring to a list where the row source provides that many list items.
I wasn't doing anything as sophisticated as that.?
The combo itself did all the work? I *thought* that was just a feature of Access.?

The source is just
Code:
SELECT Crew.ID, Trim([surname] & " " & [initials]) AS Name
FROM Crew
ORDER BY Trim([surname] & " " & [initials]);
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 06-13-2019, 09:12 AM   #10
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,551
Thanks: 92
Thanked 1,682 Times in 1,560 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: only allow correct data input

Find-as-you-type IS a feature of Access (in the sense of being a feature of the combobox which is a feature of Access).

I don't think I've ever dealt with more than about 1500 entries in a combo but up to that point it was working just fine.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 06-13-2019, 10:20 AM   #11
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,343
Thanks: 432
Thanked 794 Times in 769 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: only allow correct data input

Quote:
Originally Posted by The_Doc_Man View Post
Find-as-you-type IS a feature of Access (in the sense of being a feature of the combobox which is a feature of Access).

I don't think I've ever dealt with more than about 1500 entries in a combo but up to that point it was working just fine.
Doc,
I was creating a database to replicate the Gazette that we had issued twice a year for my 'small' shipping company. This can be found at http://www.bibby-gazette.co.uk/

In an attempt to do it properly, I had a table for Crew, Dates. Ship & Rank.
I used a Links table to join all the data.
When I started this, I had no idea that there would be anywhere near that amount of crew who served in the company during the Gazette issue. I had to key all the data in.

Even with that many it works fine, however it is a single user unsplit DB, so I was just commenting on this fact. I actually had to go and look to see how many crew were in there. I never realised it was that many, the number just grew and grew.

Fortunately I had a fair bit of assistance from this site when I was creating it.

Attached Images
File Type: png bibby ERD.PNG (20.9 KB, 56 views)
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 06-13-2019, 12:31 PM   #12
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,065
Thanks: 10
Thanked 218 Times in 206 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: only allow correct data input

Please, let's not divert the thread into a debate about features. I didn't use the word "feature". Whether it's automatic because it's a "feature" of Access, or whether you code it for a combo, or whether you code it for a textbox, it's not the same thing as an unfiltered combo list, which was my message. In fact, the OP doesn't even have a combo IIRC.
Micron is offline   Reply With Quote
Old 06-13-2019, 01:53 PM   #13
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,551
Thanks: 92
Thanked 1,682 Times in 1,560 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: only allow correct data input

True, he doesn't - but we were suggesting he SHOULD, and were discussing that point.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 06-14-2019, 01:13 AM   #14
murray83
sega collector
 
Join Date: Mar 2017
Location: Daventry, UK
Posts: 444
Thanks: 106
Thanked 1 Time in 1 Post
murray83 is on a distinguished road
Re: only allow correct data input

cheers for the plethora of options but in the end, went for a nice easy combo and linked to source in the table, bosh
murray83 is offline   Reply With Quote
Old 06-14-2019, 04:35 AM   #15
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,551
Thanks: 92
Thanked 1,682 Times in 1,560 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: only allow correct data input

Simple and straight-forward. Often that is the best method, murray83.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
murray83 (06-17-2019)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Return correct On Change data from cbo to txt box Zydeceltico Forms 3 10-17-2018 05:33 AM
Query result not correct in Access but correct in Excel Onesimus Queries 0 06-19-2011 09:28 PM
Report not showing correct data... accessnewbie89 Reports 6 05-26-2011 03:43 AM
data in tables not correct maxmangion Forms 2 11-29-2004 02:29 AM
problem pulling correct data sullivan Reports 1 12-20-2002 08:42 PM




All times are GMT -8. The time now is 02:02 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World