Problem using SQL code for autocomplete functionality!

Question

Post reply

Problem using SQL code for autocomplete functionality!

christy2931

Old Hand

Points: 395

March 18, 2004 at 6:35 am

#84538

Hi there,

I'm trying to use SQL code in VB to do an autocomplete function on a form, and I'm having difficulty with something. I have an Access table that has a bunch of fields, 8 of which are text fields that contain the surnames of the 8 people involved in a transaction. So each record in the table contains 8 different surname fields. When a user is adding a new record, I have an autocomplete where each letter they type should bring up previous surnames with the same letter sequence. Make sense?

This is the SQL code I'm using on the keypress event of the textbox ...

-----------------------------------------------------

sSQL = "SELECT DISTINCT P1N1_Surname, P1N2_Surname, P1N3_Surname, P1N4_Surname, P2N1_Surname, P2N2_Surname, P2N3_Surname, P2N4_Surname "

sSQL = sSQL & "FROM tblDocInfo WHERE "

sSQL = sSQL & "P1N1_Surname LIKE '" & UCase(cmbP1N1_SurName.Text & "*") & "' OR "

sSQL = sSQL & "P1N2_Surname LIKE '" & UCase(cmbP1N1_SurName.Text & "*") & "' OR "

sSQL = sSQL & "P1N3_Surname LIKE '" & UCase(cmbP1N1_SurName.Text & "*") & "' OR "

sSQL = sSQL & "P1N4_Surname LIKE '" & UCase(cmbP1N1_SurName.Text & "*") & "' OR "

sSQL = sSQL & "P2N1_Surname LIKE '" & UCase(cmbP1N1_SurName.Text & "*") & "' OR "

sSQL = sSQL & "P2N2_Surname LIKE '" & UCase(cmbP1N1_SurName.Text & "*") & "' OR "

sSQL = sSQL & "P2N3_Surname LIKE '" & UCase(cmbP1N1_SurName.Text & "*") & "' OR "

sSQL = sSQL & "P2N4_Surname LIKE '" & UCase(cmbP1N1_SurName.Text & "*") & "'"

-----------------------------------------------------

So basically, I'm selecting everything in either of those 8 fields that matches the letter entered by the user - so if the person enters "s" everything that begins with S appears in the combobox below it, then if they enter "e" it's narrowed down to everything that begins with SE that appears. Like an autocomplete function, so the user doesn't have to type the same names over and over again.

The person doing the coding before me has it working but it was pulling out every record in the database and looping through them all to find any matches. VERY slow. 🙂 I want to use SQL to make it faster.

So here's the rest of my code...

-----------------------------------------------

datSurNames.RecordSource = sSQL

datSurNames.Refresh

If Not datSurNames.Recordset.EOF Then datSurNames.Recordset.MoveFirst

Do While datSurNames.Recordset.EOF = False

cmbP1N1_SurName.AddItem datSurNames.Recordset("XXXXXXXXX")

datSurNames.Recordset.MoveNext

Loop

------------------------------------------------------

My problem is that because I am pulling out all records where ONE of the surname fields matches what the person is entering, how do I know what field to add to the combobox match list (where it now says "XXXX"). If I was only pulling matches out of one field, I would put the results of that field into the combobox because I would know that was obviously the field that matched. But if I select the records that match on ONE fielf, how will I tell it to only put the matches in?

Am I making any sense? haha

Hopefully sombody can help me!

Thanks!

Christy

🙂

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 1

Christy;

Best solution would be to normalize the data and put the surnames fields in a (sub-)table. Then you only have to search the subtable.

But if a database redesign is not feasible or desirable, use a UNION query:

sSQL = "SELECT DISTINCT P1N1_Surname as surname "
sSQL = sSQL & "FROM tblDocInfo WHERE "
sSQL = sSQL & "P1N1_Surname LIKE '" & UCase(cmbP1N1_SurName.Text & "*") "
sSQL = sSQL & "UNION "
sSQL = "SELECT DISTINCT P1N2_Surname as surname "
sSQL = sSQL & "FROM tblDocInfo WHERE "
sSQL = sSQL & "P1N2_Surname LIKE '" & UCase(cmbP1N1_SurName.Text & "*") "
sSQL = sSQL & "UNION "
sSQL = "SELECT DISTINCT P1N3_Surname as surname "
sSQL = sSQL & "FROM tblDocInfo WHERE "
sSQL = sSQL & "P1N3_Surname LIKE '" & UCase(cmbP1N1_SurName.Text & "*") "
sSQL = sSQL & "UNION "
sSQL = "SELECT DISTINCT P1N4_Surname as surname "
sSQL = sSQL & "FROM tblDocInfo WHERE "
sSQL = sSQL & "P1N4_Surname LIKE '" & UCase(cmbP1N1_SurName.Text & "*") "
sSQL = sSQL & "UNION "
sSQL = "SELECT DISTINCT P2N1_Surname as surname "
sSQL = sSQL & "FROM tblDocInfo WHERE "
sSQL = sSQL & "P2N1_Surname LIKE '" & UCase(cmbP1N1_SurName.Text & "*") "
sSQL = sSQL & "UNION "
sSQL = "SELECT DISTINCT P2N2_Surname as surname "
sSQL = sSQL & "FROM tblDocInfo WHERE "
sSQL = sSQL & "P2N2_Surname LIKE '" & UCase(cmbP1N1_SurName.Text & "*") "
sSQL = sSQL & "UNION "
sSQL = "SELECT DISTINCT P2N3_Surname as surname "
sSQL = sSQL & "FROM tblDocInfo WHERE "
sSQL = sSQL & "P2N3_Surname LIKE '" & UCase(cmbP1N1_SurName.Text & "*") "
sSQL = sSQL & "UNION "
sSQL = "SELECT DISTINCT P2N4_Surname as surname "
sSQL = sSQL & "FROM tblDocInfo WHERE "
sSQL = sSQL & "P24_Surname LIKE '" & UCase(cmbP1N1_SurName.Text & "*") "

Recognize that you may get some "duplicate" records, when "Smith" is in P1N1_Surname in one record, and also in P2N3_Surname in another record.

Mark

Mark Shepherd SSC Enthusiast Points: 152 More actions · Answer 2

For the message above Make the Union a UNION ALL and the duplicate records will be removed.

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 3

Thanks, Mark. Forgot about that. That's what I get for posting quickly on a late Fri afternoon.

And thanks for one Mark picking up another.

Mark