only allow correct data input

[murray83]

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

 

[theDBguy]

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]

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.

 

[Deleted member 147267]

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]

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]

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]

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.?

 

[Deleted member 147267]

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]

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

SELECT Crew.ID, Trim([surname] & " " & [initials]) AS Name
FROM Crew
ORDER BY Trim([surname] & " " & [initials]);

 

[The_Doc_Man]

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]

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.

 

[Deleted member 147267]

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]

True, he doesn't - but we were suggesting he SHOULD, and were discussing that point.

 

[murray83]

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]

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

 

[MajP]

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.