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: 421
Thanks: 94
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: 3,063
Thanks: 36
Thanked 730 Times in 713 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
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 offline   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: 13,951
Thanks: 80
Thanked 1,567 Times in 1,455 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
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 731
Thanks: 3
Thanked 148 Times in 142 Posts
Micron will become famous soon enough Micron will become famous soon enough
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: 11,942
Thanks: 74
Thanked 1,970 Times in 1,918 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
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.
jdraw is online now   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: 13,951
Thanks: 80
Thanked 1,567 Times in 1,455 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
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,672
Thanks: 398
Thanked 637 Times in 618 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
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


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
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 731
Thanks: 3
Thanked 148 Times in 142 Posts
Micron will become famous soon enough Micron will become famous soon enough
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
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,672
Thanks: 398
Thanked 637 Times in 618 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
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


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: 13,951
Thanks: 80
Thanked 1,567 Times in 1,455 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
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,672
Thanks: 398
Thanked 637 Times in 618 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
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


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
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 731
Thanks: 3
Thanked 148 Times in 142 Posts
Micron will become famous soon enough Micron will become famous soon enough
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: 13,951
Thanks: 80
Thanked 1,567 Times in 1,455 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: 421
Thanks: 94
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: 13,951
Thanks: 80
Thanked 1,567 Times in 1,455 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 05:16 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