Soundex search not repeating on fail

[Jen0dorf]

Hi



Ongoing project issue

I have a table containing baptism information that includes a colum that contains "soundex" data relating to the Surname field.

I then have a form that opens based on the query

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, tbl_Baptism.ForenameSDX
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 (((tbl_Baptism.SurnameSDX)=Soundex([Enter a Surname])))
ORDER BY tbl_Baptism.FullDateOfBaptism;

this works perfectly if I open the form I see a window asking for a surname I type in dean and all the relevant entries appear.

However if I type in yyzp then the resulting form - frm_baptismSurnameSoundexResultsAll- appears blank for obvious reasons. But if I then repeat the search for say Dean no results appear and it seems the procedure is locked out. If I close the database and re open and then insert Dean then pages of results appear.

It seems that as long as the soundex finds a result all works but if it doesn't it all goes wrong.

Can anyone suggest how to fault find?

thanks

Ian

[RuralGuy]

How are you opening the form? What process are you using?

[Jen0dorf]

Hi

not sure what you mean but I'll upload a copy of the database once I've shrunk it down a bit

cheers

Ian

[Micron]

This sounds like what's called a parameter prompt.

I open the form I see a window asking for a surname

But if I then repeat the search for say Dean

You'd have to explain how you can do this once the prompt is dealt with and the form is already open. You probably should be opening the form from a command button somewhere, whose OnLoad event gets a count of the resulting records. If 0, cancel the load, present a message box like "No records for entered name" then close the form so the user can try again.

[Jen0dorf]

This sounds like what's called a parameter prompt.
You'd have to explain how you can do this once the prompt is dealt with and the form is already open. You probably should be opening the form from a command button somewhere, whose OnLoad event gets a count of the resulting records. If 0, cancel the load, present a message box like "No records for entered name" then close the form so the user can try again.

Thanks for the idea I'll work on that and see how I get on

cheers

Ian

[Jen0dorf]

Hi

I have a form named "frm_baptismsallParishes"

On that form I have a button which calls the form

"frm_BaptismSoundexSurnameSearchAll"

using the code

Private Sub btn_baptismSurnameSoundex_Click()
DoCmd.Close
DoCmd.OpenForm FormName:="frm_BaptismSoundexSurnameSearchAll"
End Sub

THat form contains a button which calls the form

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

The form "frm_BaptismSurnameSoundexSearchResultsAll" source is the query

"qry_BaptismsSurnameSoundexAll"

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 (((tbl_Baptism.SurnameSDX)=Soundex([Enter a Surname])))
ORDER BY tbl_Baptism.FullDateOfBaptism;

(The soundex field value is created when the data is input using an update query.)

This sequence when run works perfectly until the query displays a null result then the database has to be closed and restarted.

So as per the suggestion I added an onload event to the form

"frm_BaptismSurnameSoundexSearchResultsAll"

'------------------------------------------------------------
' Form_Load
'
'------------------------------------------------------------
Private Sub Form_Load()
On Error GoTo Form_Load_Err

    If (DCount("*", "qry_BaptismsSurnameSoundexAll") > 0) Then
        DoCmd.OpenForm "frm_BaptismSurnameSoundexSearchResultsAll", acNormal, "", "", , acNormal
    Else
        Beep
        MsgBox "No results found please try another spelling", vbOKOnly, ""
    End If


Form_Load_Exit:
    Exit Sub

Form_Load_Err:
    MsgBox Error$
    Resume Form_Load_Exit

End Sub

( I used the macro builder and then coverted it to VBA)

Initialy I just wanted to show a message box if there were no results from the query, If there were results then I wanted it to finish loading the form. In the end I want the message box to include a button to return to "frm_BaptismSoundexSurnameSearchAll" so that the enduser can start another search.


However I get an error error 2001


Is my logic wrong?

THanks

Ian

[RuralGuy]

I think you should find a way to avoid those parameter queries. There are other more consistent methods that work all of the time.

[Jen0dorf]

Hi

at the risk of seeming stupid ;-) I've done a search on google for alternatives to parameter queries in Access and nothing comes up.

Would appreciate a link to any helpfull information

cheers

Ian

[RuralGuy]

You could, for example, have a form that displays *all* of the records in the table and have another form that does a search for the Soundex value and if located open the 1st form using a WHERE clause argument. http://www.baldyweb.com/wherecondition.htm

[Jen0dorf]

Hi

thanks

that sounds like a plan for next week;-)

cheers

Ian

[RuralGuy]

Cool. Stay in touch and good luck with the project.