UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Filter Combo Box (like *xyz*) Based On On What Typed, Access 2010    
 
   
jimbofoxman
post Dec 5 2018, 03:08 PM
Post#1



Posts: 368
Joined: 4-April 08



We have issues where people will start typing a customers name and can't find it (misspelled, one spouses name and not the other, etc.) so they enter a new customer. So I have a lot of SIMILAR double entries like;

Bob Smith
Bob & Cindy Smith
Cindy Smith
Cindy & Bob Smith
Robert Smith
Cindi Smith

With all the same address information, phone number, etc.

So what I am trying to do is have a combo box that filters both the NAME Field and ADDRESS Field so maybe we can combat double entry (I know I'll never catch it all)

Here is the row source for the combo box;
CODE
SELECT tblCustomer.Customer, tblCustomer.CustomerID, tblCustomer.Address1, tblCustomer.City
FROM tblCustomer
ORDER BY tblCustomer.Customer;


I read during a search where someone suggested adding the following to the criteria on the row source. Set the Rowsource ON FORM LOAD and also set the Rowsource ON CHANGE for the combo box.
CODE
Like *Me.ComboBoxName.Text*


But when I try that, say for the Customer Name, then the combo box is blank..........because it's Bound to a field.

Does anyone know if this is possible? I would basically like to have it search Customer Name or Address when typing something and then store the ID in the underlying table for the form.

Basically the form is for generating Proposals. Select a SalesRep from a list, Customer from a list, Proposal Date and many other fields.

The issue I also see, which may just say the heck with doing this, is that when customer isn't in the list code asks if they want to add the customer and goes to another form to fill out. I could see where they search for something, don't find it and it generates a new customer even though they didn't mean too.

Is that Clear as mud?
Go to the top of the page
 
GroverParkGeorge
post Dec 5 2018, 05:34 PM
Post#2


UA Admin
Posts: 34,077
Joined: 20-June 02
From: Newcastle, WA


Not an uncommon problem with data entry environments.

I think your plan should work (compare names and addresses), but it's going to have its own limitations.

For example, if you type "Cindy Smith" into this searching text box, you'll only match records where that full string exists, even with leading and trailing wild cards: "*Cindy Smith*".

I wonder if you actually have one name field and not FirstName and LastName fields? That would be a second problem.

I would probably look at fuzzy matching algorithms as well. Also consider

So, with that said, though, you might want to approach this from the wildcard matching perspective anyway, but be forewarned of the potential complication.

I like to use TempVars for this job because I can create a stored query using the TempVars as parameters and not have to dynamically concatenate the SQL.

In VBA, you can set the value of the two TempVars:

TempVars.Add Name:="ClientName", Value:= Me.txtClientName
TempVars.Add Name:="ClientAddress", Value:= Me.txtClientAddress
TempVars.Add Name:="ClientCity", Value:= Me.txtClientCity

The select query used to search for potentially matching records would look like this:

SQL
SELECT tblCustomer.Customer, tblCustomer.CustomerID, tblCustomer.Address1, tblCustomer.City
FROM tblCustomer WHERE tblCustomer.Customer Like "*" & TempVars!ClientName & "*" OR tblCustomer.Address1 Like "*" & TempVars!ClientAddress & "*" OR tblCustomer.City Like "*" & TempVars!ClientCity& "*"
ORDER BY tblCustomer.Customer;


Keep in mind, though, that even this is not going to work flawlessly because of the fact that the strings passed to the TempVars are not necessarily going to match between the wildcards.

Give this a try.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
moke123
post Dec 5 2018, 07:17 PM
Post#3



Posts: 1,329
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



What happens if Bob Smiths' wife doesn't use his last name and goes by Cindy Jones?
I would be inclined to enter them separately and perhaps add a field or junction table for the relationship.

A couple other good matching algorithms are soundex and levenschtein distance.
Go to the top of the page
 
GroverParkGeorge
post Dec 5 2018, 07:56 PM
Post#4


UA Admin
Posts: 34,077
Joined: 20-June 02
From: Newcastle, WA


Here's a demo that searches by several fields.

It can search by "OR" matching or "AND" matching.

It uses TempVars and a couple of saved queries to do the filtering/searching.


Attached File  SearchingTextBoxes.zip ( 58.09K )Number of downloads: 7


It does not address the problems previously noted where you can't really control what has been entered previously in one or more fields in records. But that's a matter for either human inspection of results, or a more elaborate pattern matching as has been suggested.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
jimbofoxman
post Dec 6 2018, 10:06 AM
Post#5



Posts: 368
Joined: 4-April 08



Just responding to say thank you. Need to read through it more yet. But I wanted to answer a question....

In our customer table we have the following name fields;

DisplayName (So something like Smith, Bob)
FirstName (Bob)
LastName (Smith)

If it were a business it may be something like;
DisplayName (Ted's Furnace Business)
FirstName (Ted's Furnace Business)
LastName (would be left blank)

DisplayName is a hold over from way back, before I even started here and someone else with minimal Access experience was making the initial database. We still use display name because we use the LastName first when searching, or adding another proposal for the same customer. We are always referring to Last Names when scheduling jobs and what not. People know when adding a new customer on the form via the aforementioned combobox they type "Smith, Bob".

Is that a good way to do it, probably not. Like I mentioned it's a hold over. Maybe taking some new approach on the combobox filtering will let us do away with that.

We have several instances where we get the wife not taking the last name. I was trying to find an example of how we do that, but can't find one right now.

Any way, I'm just trying to find a way of combating the double entries somehow. I know it will never be perfect.............we have 20ish people that can potentially enter data.

Thanks again, I will go over the info provided and see where it gets me.
Go to the top of the page
 
GroverParkGeorge
post Dec 6 2018, 10:33 AM
Post#6


UA Admin
Posts: 34,077
Joined: 20-June 02
From: Newcastle, WA


That's not uncommon for a long-standing application that has serve its purpose well despite some shortcomings.

The only thing I would say here is that you can add an additional criteria in the searching queries that also looks at the DisplayName field in addition to first and last name fields.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
jimbofoxman
post Dec 6 2018, 12:58 PM
Post#7



Posts: 368
Joined: 4-April 08



I have it working. I remembered a search feature I did on another form that was similar in concept. We use it for finding past proposals so it just opens the form with the correct record rather than putting the ID number in the bound field.

My last question/concern is....

In the previous iteration they would type say "Smith, Bob". If it didn't exist it would run some code in NOT IN LIST. If it existed it was selected. This Combo Box is currently bound to CustomerID on the underlying table. Do I want to leave it bound or say once I select it have it assign the CustomerID from the selection made to the field CustomerID? Occasionally we have proposals that get bid to one person/company and ends up getting changed for whatever reason.

Right now, if I type text in my search criteria and filters the ComboBox. So in the case of needing to change the customer the ComboBox goes blank because the old name isn't in the filtered list anymore. Granted they can select the new name.

I'm more or less thinking of people getting confused..........so that's my only reason for asking about it being bound or not.

Thanks for the help!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    10th December 2018 - 07:02 PM