only allow correct data input (1 Viewer)

murray83

Games Collector
Local time
Today, 10:34
Joined
Mar 31, 2017
Messages
728
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
 

Attachments

  • enter0.jpg
    enter0.jpg
    20.5 KB · Views: 70
  • enter1.jpg
    enter1.jpg
    20.6 KB · Views: 67
  • enter2.jpg
    enter2.jpg
    19 KB · Views: 67

theDBguy

I’m here to help
Staff member
Local time
Today, 03:34
Joined
Oct 29, 2018
Messages
21,357
Hi. You can do either approach, but I also think using a dropdown would be simpler and you won’t have to use DLookup().
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:34
Joined
Feb 28, 2001
Messages
26,996
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.
 

Micron

AWF VIP
Local time
Today, 06:34
Joined
Oct 20, 2018
Messages
3,476
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.
 

jdraw

Super Moderator
Staff member
Local time
Today, 06:34
Joined
Jan 23, 2006
Messages
15,361
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.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:34
Joined
Feb 28, 2001
Messages
26,996
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.
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:34
Joined
Sep 21, 2011
Messages
14,038
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.?
 

Micron

AWF VIP
Local time
Today, 06:34
Joined
Oct 20, 2018
Messages
3,476
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.
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:34
Joined
Sep 21, 2011
Messages
14,038
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.?:confused:

The source is just
Code:
SELECT Crew.ID, Trim([surname] & " " & [initials]) AS Name
FROM Crew
ORDER BY Trim([surname] & " " & [initials]);
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:34
Joined
Feb 28, 2001
Messages
26,996
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.
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:34
Joined
Sep 21, 2011
Messages
14,038
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. :D

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. :D

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

 

Attachments

  • bibby ERD.PNG
    bibby ERD.PNG
    20.9 KB · Views: 208

Micron

AWF VIP
Local time
Today, 06:34
Joined
Oct 20, 2018
Messages
3,476
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.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:34
Joined
Feb 28, 2001
Messages
26,996
True, he doesn't - but we were suggesting he SHOULD, and were discussing that point.
 

murray83

Games Collector
Local time
Today, 10:34
Joined
Mar 31, 2017
Messages
728
cheers for the plethora of options but in the end, went for a nice easy combo and linked to source in the table, bosh
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:34
Joined
Feb 28, 2001
Messages
26,996
Simple and straight-forward. Often that is the best method, murray83.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:34
Joined
May 21, 2018
Messages
8,463
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).

This maybe semantics but FAYT is not considered a built in feature by most people. The feature you are speaking of is autoexpand which is pretty limited.
You can use the AutoExpand property to specify whether Microsoft Access automatically fills the text box portion of a combo box with a value from the combo box list that matches the characters that you enter as you type in the combo box. This lets you quickly enter an existing value in a combo box without displaying the list box portion of the combo box. Read/write Boolean.


FAYT is the ability to reduce the list as you type. This can be very powerful. I have an extremely robust class module that turns any combo into an FAYT. You can see the difference in the demo. The first is a traditional combo, second is an fAYT but searches from the front only. If you type "John" it reduces the list to anything beginning with John. The third is FAYT anywhere in the list so if you type in "John" you return records with John anywhere in the first or last name.

In the example try searching for Johnny Hayes and see the difference. In the third box you can search for hayes as well as johnny.

These lists are 8k and with the FAYT you can find anything in seconds.
 

Attachments

  • Fayt First Last.accdb
    1.9 MB · Views: 80

Users who are viewing this thread

Top Bottom