delete tempVar before creating new or overwrite

[Jen0dorf]

Hi

I have a form with two text fields and a button used to create two tempVars used in a form based on a query using the tempVars

code is below.

TempVars.Add "varForename", Me.txtForename.Value
    TempVars.Add "varSurName", Me.txtsurname.Value
    DoCmd.Close acForm, "frm_BaptismForenameandSurnameSearchAll"
    DoCmd.OpenForm "frm_BaptismforenameSurnameSearchResultsAll", acNormal, "", "", , acNormal

THis calls the other form using this 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
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.ChildsName) Like [TempVars]![varForename] & "*") AND ((tbl_Baptism.Surname) Like [TempVars]![varSurname] & "*"))
ORDER BY tbl_Baptism.FullDateOfBaptism;

Apologies for being long winded.

THis all worked perfectly the first time the second time you put in a forename and surname the second forms opens with no records listed it's as if the tempVars are not eing recognised. If I then close the database and reopen it all works perfectly.

On the form

frm_BaptismforenameSurnameSearchResultsAll

I have two buttons

Home - taked you back to a main menu and Search Again which takes you back to the page where you input the values for var Surname and varForename.

The Search again Button has the code

Private Sub btn_NewSearch_Click()
DoCmd.Close
DoCmd.OpenForm FormName:="frm_BaptismForenameandSurnameSearch"
End Sub[/CODE]

My thought is to add some code to delete the two variable? I thought they would be overwritten with any new values

Would as usual appreciate any thoughts

thanks

Ian

[andy49]

It is a good practice to remove TempVar object variables when you are finished using them.

tempvars.removeall removes them all

[Jen0dorf]

THanks

I'll have a look later - on baby sitting duties at moment for World Book Day- looking after a Gruffalo :-)