remove a temporary variable

[Jen0dorf]

Hi

I have the following code which works perfectly to display a message box when a query returns no results

Private Sub btn_Search_Click()

    TempVars.Add "varYearOfBanns", Me.txtYearOfBanns.Value
    If DCount("*", "qry_BannsYearOfSearch") > 0 Then
    DoCmd.Close acForm, "frm_BannsYearSearch"
    DoCmd.OpenForm "frm_BannsYearOfBannsSearchResults", acNormal, "", "", , acNormal
 Else: 
MsgBox "There are no Banns Records for this Year"
    End If
   
End Sub

I wanted to remove the tempVar if there were no results so I modified the VBA to be:

Private Sub btn_Search_Click()

    TempVars.Add "varYearOfBanns", Me.txtYearOfBanns.Value
    If DCount("*", "qry_BannsYearOfSearch") > 0 Then
    DoCmd.Close acForm, "frm_BannsYearSearch"
    DoCmd.OpenForm "frm_BannsYearOfBannsSearchResults", acNormal, "", "", , acNormal
 Else: 
tempvars.remove "varYearOfBanns"
MsgBox "There are no Banns Records for this Year"
    End If
   
End Sub

Is this code correct to remove the temp var because when I use it the database hangs

thanks

Ian

[Micron]

Sequence doesn't look right. Check if there are records first and if not, don't add? Or do you need it for the query?
I think the syntax is TempVars.Remove("varName"). Not sure about the brackets.
Probably I should have left this one for those who actually use these....

[Jen0dorf]

Sequence doesn't look right. Check if there are records first and if not, don't add? Or do you need it for the query?
I think the syntax is TempVars.Remove("varName"). Not sure about the brackets.
Probably I should have left this one for those who actually use these....

Hi

perhaps as usual I wasn't clear as to my ultimate aim.

I have tables containing 1000s of records

I have created ( with help) searches to find matching names, years etc using temporary variables and the searches work well. One of the issues I experienced was solved by removing temporary variables when no longer required. So on a Form I used the on close event to remove the temporary variable.

The search system worked well until no record was returned using this query

SELECT tbl_Parish.Parish, tbl_Church.Church, tbl_Banns.FicheRef, tbl_Banns.PageNo, tbl_Banns.EntryNo, tbl_Banns.DateOfBanns, tbl_Banns.YearOfBanns, tbl_Banns.GroomForenames, tbl_Banns.GroomSurname, tbl_Banns.GroomOccupation, tbl_Banns.GroomAbode, tbl_Banns.Minister, tbl_Banns.BrideForenames, tbl_Banns.BrideSurname, tbl_Banns.BrideAbode, tbl_Banns.BrideCondition, tbl_Banns.BrideOccupation, tbl_Banns.BannsId
FROM tbl_Parish INNER JOIN (tbl_Church INNER JOIN tbl_Banns ON tbl_Church.ChurchID = tbl_Banns.ChurchId_fk) ON tbl_Parish.ParishID = tbl_Church.ParishID_fk
WHERE (((tbl_Parish.Parish)=[TempVars]![varParish]) AND ((tbl_Church.Church)=[TempVars]![varChurch]) AND ((tbl_Banns.YearOfBanns) Like [TempVars]![varYearOfBanns] & "*"))
ORDER BY tbl_Banns.GroomSurname;

So I added the "else" as above to show the message box if no records were found using the query.

THis again worked perfectly so I finally wanted to add the command to remove the temporary variable if no records were found. Hence I added

tempvars.remove "varYearOfBanns"

Is there a better way to do this or is my logic wrong?

thanks

Ian

[davegri]

  Private Sub btn_Search_Click()   
   TempVars.Add "varYearOfBanns", Me.txtYearOfBanns.Value
   If DCount("*", "qry_BannsYearOfSearch") > 0 Then
          DoCmd.Close acForm, "frm_BannsYearSearch"
          DoCmd.OpenForm "frm_BannsYearOfBannsSearchResults", acNormal, "", "", , acNormal
   Else: 
          MsgBox "There are no Banns Records for this Year"
   End If
    tempvars.remove "varYearOfBanns"   
End Sub

Perhaps this? Don't see much difference except that the tempvar will be removed in any case.
Just a note: Be very careful with tempvar variable name spelling. Option Explicit has absolutely no effect with spelling errors of tempvar names.

Also, FWIW, you don't have to "Add" a tempvar to set its initial value. You can simply just declare it and set its value out of thin air:

Tempvars!varYearOfBanns = Me.txtYearOfBanns.Value

[Micron]

wanted to add the command to remove the temporary variable if no records were found.
Is there a better way to do this or is my logic wrong?

I have never used tempvars and don't intend to. If I get the scenario, you have a control .txtYearOfBanns on a form and you want to pass its value to a query upon button click.
I would either have Forms!frm_BannsYearSearch.txtYearOfBanns in the YearOfBanns query field for example, and run the query and show the message box if the test for the record count returned zero. The drawback being that the form has to be open and the referenced controls must contain values...
OR
If I want to run a sql statement in code, I'd either write it so that it uses the aforementioned form control reference or shorten it if that meant I had many iterations of Forms!frmFormName.MyControlName. To avoid that, I'd assign the control values to variables such as

Dim strBannYear As String
strBannYear = Me.txtYearOfBanns
Note that you do not need to specify .txtYearOfBanns.Value Value is the default property of a text box, so Me.txtYearOfBanns will suffice.

The relevant portion of the sql would look like
...AND ((tbl_Banns.YearOfBanns) Like " & strBannYear & "'*')"
Note the inner single quotes around * as you cannot concatenate sql in vba the way it's written for use in a query. Whether or not either of these methods is better is a matter of opinion. Mine is that TempVars adds a layer I don't need and don't want to worry about destroying right after I use one so that I don't end up accidentally using the wrong value somewhere else.

[Jen0dorf]

Many thanks for the advice

cheers

Ian