Soundex search not working on repeat

[Jen0dorf]

Hi



Am using a soundex search sequence from http://allenbrowne.com/vba-Soundex.html


I have a button on a form that carries out the following

Private Sub btn_Search_Click()
DoCmd.Close
DoCmd.OpenForm FormName:="frm_BaptismSurnameSoundexSearchResults"
End Sub

the form is based on the query

qry_BaptismsSurnameSoundexAll

which is

SELECT tbl_Parish.Parish, tbl_Church.Church, tbl_Baptism.FicheNo, tbl_Baptism.BirthDate, tbl_Baptism.DateOfBaptism, tbl_Baptism.YearOfBaptism, tbl_Baptism.FullDateOfBaptism, tbl_Baptism.ChildsName, tbl_Baptism.Surname, tbl_Baptism.Sex, tbl_Baptism.Parents, tbl_Baptism.Abode, tbl_Baptism.Occupation, tbl_Baptism.RefNo, tbl_Baptism.PageNo, tbl_Baptism.EntryNo, tbl_Baptism.Minister, tbl_Baptism.Notes, tbl_Baptism.BaptismID
FROM tbl_Parish INNER JOIN (tbl_Church INNER JOIN tbl_Baptism ON tbl_Church.ChurchID = tbl_Baptism.ChurchID_fk) ON tbl_Parish.ParishID = tbl_Church.ParishID_fk
WHERE (((Soundex([Surname]))=Soundex([Enter a Surname])))
ORDER BY tbl_Baptism.FullDateOfBaptism;

THis works perfectly the first time it is used. However if used a second time the results form just shows no data ie the displayed form is blank. The only way to get it to work again is to shut the database and re open it.

Is the "soundex" being added as a variable? should I add an event on form close to clear such a variable?

thanks

Ian

[orange]

Ian,
I think I would do a 1 time function/process to add a field to your table representing "Person" (whatever you called it) and populate it with the value from the Soundex function on Surname. I think it would streamline your search.

Perhaps you can post a copy of the db for readers to review/test the form involved.

[Jen0dorf]

Hi

thanks for the reply, just got back to this after a few months illness so the brain is working slowly!

I did a search for one time function/process in google but failed to find any information.

CAn you point me to a tutorial?

After the weekend I'll strip the database down and upload a copy

thanks again

Ian

[orange]

Ian,

Sorry to hear you have been ill, welcome back.
By one time function I mean:

-add a field to your table, and name it so it is meaningful -- say SurnameSDX.
-write a query to pass your surnames through the Soundex algorithm, and store the result in SurnameSDX field.

That way you will have all surnames with their Soundex values.

You can then in your query say

.............where SurnameSDX = Soundex([Enter a Surname])

Good luck.

[Jen0dorf]

Hi

thanks for the reply I'll work on that next week - haven't got a clue where to start but like everything it's a learning curve. And of course I can re post!!!

As to illness I'm much luckier than most - The Big C got me 12 years ago and although still getting remedial treatment I'm still here ;-)

I just have to take drugs periodically which have side effects.

Thanks for the assist

Ian

[Jen0dorf]

Hi

I've attached a cut down version of the database I'm having issues with.

THis from the start was a learning curve every step of the way ( with lots of help)

I've deleted 90% of the records in the baptisms table and all reverences to marriage, banns and burials.

The issue I have is that if you open frm_BaptismSoundexSurnameSearchAll and input a value such as dean results are shown.

If you step back and search again sometimes no results or formatting is shown just a blank results form.

To confuse me even further On this cut down database it seems to work every time so is the size of table an issue?

cheers

Ian

[RuralGuy]

I think it would be advisable posting a db that exhibits the issue. I'm not sure how someone can troubleshoot it otherwise but maybe!