Customer Flag

[LadyDi]

My manager has just ask that I add a flag to one of the databases that my department uses that will appear as a reminder any time a certain customer name is entered. Normally this would not be a problem for me. However, my manager also mentioned that she wants the flag to appear even if my co-workers misspell the customer name, don't enter the entire name, or use the customer's initials. How would you recommend accomplishing this? I thought about adding a customer name table to my database, but I'm not sure that is the best approach, because we have several hundred thousand customers, and the table would be massive. Any suggestions you have would be greatly appreciated.

 

[namliam]

You cannot possibly account for aehc nad veery typo of aynthing... IMPOSSIBLE
Initials,maybe but even that is going to take doing...
Part names? abriviations ? And that with typo's?

If your going to account for each and every abriviation and/or typo.... You are going to go into hundreds of permutations per name... if not thousands... I would say its very hard to say the least.

 

[speakers_86]

If possible, use a combo box.

 

[missinglinq]

Namliam is right on the money as is Speakers_86 with the solution! Anytime you have to have something spelled exactly right, a combobox is the always the way to go.

 

[LadyDi]

Is it possible to have a combo box with over 100,000 choices? Will it still work properly when it is that size?

 

[speakers_86]

I don't know. For the record though, if you click in a combo box, you can type the name, and it auto-fills for you. I am not sure if you want to use a combo for so many choices though. If you are not dealing with a memo box, then you definitely do not want the end user typing out the name though. Besides, I would think that this should probably be a foreign key (customerID).

Here is an idea for you. Next to the control in question, you can places a button that opens up frmCustomers. In this form the user can search for or navigate to the correct customer, and then close the form. When that form is closed, it can send the correct customerID to the control in question. If that button is pressed again, it now opens frmCustomers where "CustomerID =" & me.controlname. I have done it like this before. It works for me.

 

[shadow9449]

Is it possible to have a combo box with over 100,000 choices? Will it still work properly when it is that size?

One word of advice regarding combo boxes and particularly ones with lots of rows:

It's a good idea to not set the rowsource to a table or query and save the form that way. There are two reasons which I'll present in a moment. The better approach is to set the rowsource to blank and then set the OnEnter method to something like:

Me.MyCombobox.Rowsource = "MyQuery"

The reasons are: i) If you load the form and don't need the combobox, you're not loading all that data for nothing. That's particularly true on forms that have a whole bunch of combo boxes (not uncommon) and you're only going to need 2 or 3 of them, especially with your 100,000 records; and ii) if the data source is updated, your combo box won't reflect the change if the data was loaded with the form. Using my method of loading when the box is entered, you'll always have up to date data.

SHADOW

 

[boblarson]

A combo box will only display 65,536 records. So, if you have more than that you can use cascading combos to narrow the focus down. I've used one to select the first letter of the last name, for instance and then go to the other to make the selection.

 

[LadyDi]

Thank you very much for the advice. I will work with that suggestion, and let you know if I have any questions. Thank you again

 

[LadyDi]

Bob, How do you set up Cascading Combo Boxes? I have not done that before, and it sounds a little simpler than what I was going to try to do before.

 

[GoodNamesTaken]

Beyond what was already suggested, you might consider looking into regular expressions. If you can make the pattern matching specific enough, in theory it should do just what you're looking for.

 

[LadyDi]

I'm afraid, I don't know what you mean. What are "regular expressions"?

 

[GoodNamesTaken]

They're a means of matching patterns in strings. I looked into it and VB does support the use of regular expressions. I'm afraid I don't have enough VB experience to give you much beyond these links -

http://en.wikipedia.org/wiki/Regular_expression
http://www.regular-expressions.info/vb.html

 

[namliam]

Bob, How do you set up Cascading Combo Boxes?

I suggest you use this incredable new function on the forum: Search

You will find some samples in the "references" part of the forum, which you can easily addapt to suite your need(s)

 

[GoodNamesTaken]

Just thought I would add a link to the source that I originally learned regexp's from. The tutorial is written specific to perl, but regexp's should be pretty language unspecific so the theory should be transferable.

http://perldoc.perl.org/perlrequick.html
http://perldoc.perl.org/perlretut.html

Hope this helps, I'd be happy to answer any questions you have on regexp's.