Solved Using Variables in OpenForm WHERE condition

[mikenyby]

Hello all,

I posted this in a comment on another thread, but have realized the issue is not completely related to the original post, so I thought I'd start a new one.

I'm working on a multi-field search form and I'm using variables for each search field to prevent my lines of code from exponentially multiplying. Here's a relevant snippet of code from the form:

Public Sub BuildSQL()
    Dim FieldWhere1 As String
 
    If Me.cboItemField1 = "TITLE" Then
        Set FieldWhere1 = "ItemName Like '*" & [Forms]![frmAdvancedSearch]![txtItemSearch1] & "*' "
            ElseIf Me.cboItemField1 = "YEAR" Then
                  FieldWhere1 = "ItemYearOfProvenance Like '*" & [Forms]![frmAdvancedSearch]![txtItemSearch1] & "*' "
            ElseIf Me.cboItemField1 = "LOCATION" Then
                  FieldWhere1 = "ItemLocation = [Forms]![frmAdvancedSearch]![cboLocationSearch1]"
            ElseIf Me.cboItemField1 = "DESCRIPTION" Then
                  FieldWhere1 = "ItemDescription Like '*" & [Forms]![frmAdvancedSearch]![txtItemSearch1] & "*' "
            ElseIf Me.cboItemField1 = "STATEMENT OF RESPONSIBILITY" Then
                  FieldWhere1 = "ItemStatementOfResponsibility Like '*" & [Forms]![frmAdvancedSearch]![txtItemSearch1] & "*' "
    End If

End Sub

Private Sub cmdItemSearch_Click()

    DoCmd.OpenForm "frmAdvancedSearchSelectItem", acNormal, , "' & FieldWhere1 & '", acFormPropertySettings, acWindowNormal
 
End Sub

This code opens frmAdvancedSearchSelectItem displaying all records in the Items table, not just the ones matching the string in txtItemSearch1. I've played around with a bunch of variations on this code and none of them work. Most other variations give me a "Variable not defined" error. I clearly don't understand the necessary syntax for using a variable for a WHERE condition. Any ideas?

 

[pbaldy]

FieldWhere1 is declared within the BuildSQL sub, thus only available within it. Declare it above the sub under

Option Compare Database
Option Explicit

 

[pbaldy]

Oh, and the syntax would be:

DoCmd.OpenForm "frmAdvancedSearchSelectItem", acNormal, , FieldWhere1 , ...

 

[mikenyby]

Oh, and the syntax would be:

DoCmd.OpenForm "frmAdvancedSearchSelectItem", acNormal, , FieldWhere1 , ...

Thanks @pbaldy! I knew I had some syntax issues with the quotes around the variable. I've done exactly as you've instructed but it still opens frmAdvancedSearchSelectItem with all records displayed. Here's the code now:

Option Compare Database
Option Explicit
  Dim FieldWhere1 As String
  
 
Public Sub BuildSQL()
   If Me.cboItemField1 = "TITLE" Then
         FieldWhere1 = "[ItemName] Like '*" & [Forms]![frmAdvancedSearch]![txtItemSearch1] & "*' "
            ElseIf Me.cboItemField1 = "YEAR" Then
                  FieldWhere1 = "ItemYearOfProvenance Like '*" & [Forms]![frmAdvancedSearch]![txtItemSearch1] & "*' "
            ElseIf Me.cboItemField1 = "LOCATION" Then
                  FieldWhere1 = "ItemLocation = [Forms]![frmAdvancedSearch]![cboLocationSearch1]"
            ElseIf Me.cboItemField1 = "DESCRIPTION" Then
                  FieldWhere1 = "ItemDescription Like '*" & [Forms]![frmAdvancedSearch]![txtItemSearch1] & "*' "
            ElseIf Me.cboItemField1 = "STATEMENT OF RESPONSIBILITY" Then
                  FieldWhere1 = "ItemStatementOfResponsibility Like '*" & [Forms]![frmAdvancedSearch]![txtItemSearch1] & "*' "
    End If
End Sub
    


Private Sub cmdItemSearch_Click()

    DoCmd.OpenForm "frmAdvancedSearchSelectItem", acNormal, , FieldWhere1, acFormPropertySettings, acWindowNormal
    DoCmd.Close acForm, "frmAdvancedSearch", acSavePrompt
    DoCmd.OpenForm "frmAdvancedSearch", acNormal, , , acFormPropertySettings, acWindowNormal
    Forms!frmAdvancedSearchSelectItem.SetFocus
End Sub

 

[Josef P.]

You would have to call BuildSQL first.

Private Sub cmdItemSearch_Click()

    BuildSQL

    DoCmd.OpenForm "frmAdvancedSearchSelectItem", acNormal, , FieldWhere1, acFormPropertySettings, acWindowNormal
    DoCmd.Close acForm, "frmAdvancedSearch", acSavePrompt
    DoCmd.OpenForm "frmAdvancedSearch", acNormal, , , acFormPropertySettings, acWindowNormal
    Forms!frmAdvancedSearchSelectItem.SetFocus

End Sub

However, I would do without the module variable and use a function instead.

Public Function BuildSQL() as String

    Dim FieldWhere1 As String ' Is that a good variable name?

    If Me.cboItemField1 = "TITLE" Then
        Set FieldWhere1 = "ItemName Like '*" & [Forms]![frmAdvancedSearch]![txtItemSearch1] & "*' "
            ElseIf Me.cboItemField1 = "YEAR" Then
                  FieldWhere1 = "ItemYearOfProvenance Like '*" & [Forms]![frmAdvancedSearch]![txtItemSearch1] & "*' " '<--- Filter with '*2023*' ?
            ElseIf Me.cboItemField1 = "LOCATION" Then
                  FieldWhere1 = "ItemLocation = [Forms]![frmAdvancedSearch]![cboLocationSearch1]" ' <--  without "... = " & ...?
            ElseIf Me.cboItemField1 = "DESCRIPTION" Then
                  FieldWhere1 = "ItemDescription Like '*" & [Forms]![frmAdvancedSearch]![txtItemSearch1] & "*' "
            ElseIf Me.cboItemField1 = "STATEMENT OF RESPONSIBILITY" Then
                  FieldWhere1 = "ItemStatementOfResponsibility Like '*" & [Forms]![frmAdvancedSearch]![txtItemSearch1] & "*' "
    End If

    BuildSQL = FieldWhere1

End function

Private Sub cmdItemSearch_Click()

    Dim WhereCondition As String
    WhereCondition = BuildSQL  ' why not called "BuildFilterString" or "BuildWhereCondition"
  
    if len(WhereCondition) = 0 then
        ????
    end if

    DoCmd.OpenForm "frmAdvancedSearchSelectItem", acNormal, , WhereCondition, acFormPropertySettings, acWindowNormal

End Sub

 

[mikenyby]

That worked! Point taken on the variable names. I've adjusted my plan a few times to make this work and the variable name was a relic from an earlier conception. One question: What is the point of

if len(WhereCondition) = 0 then
        ????
    end if

I excluded it from my code and no issues so far. What is this statement meant to do?

Here is the code as I now have it:

Public Function BuildWhereCondition1() As String
    Dim WhereCondition1 As String
   
   If Me.cboItemField1 = "TITLE" Then
         WhereCondition1 = "[ItemName] Like '*" & [Forms]![frmAdvancedSearch]![txtItemSearch1] & "*' "
            ElseIf Me.cboItemField1 = "YEAR" Then
                  WhereCondition1 = "ItemYearOfProvenance Like '*" & [Forms]![frmAdvancedSearch]![txtItemSearch1] & "*' "
            ElseIf Me.cboItemField1 = "LOCATION" Then
                  WhereCondition1 = "ItemLocation = [Forms]![frmAdvancedSearch]![cboLocationSearch1]"
            ElseIf Me.cboItemField1 = "DESCRIPTION" Then
                  WhereCondition1 = "ItemDescription Like '*" & [Forms]![frmAdvancedSearch]![txtItemSearch1] & "*' "
            ElseIf Me.cboItemField1 = "STATEMENT OF RESPONSIBILITY" Then
                  WhereCondition1 = "ItemStatementOfResponsibility Like '*" & [Forms]![frmAdvancedSearch]![txtItemSearch1] & "*' "
    End If
    BuildWhereCondition1 = WhereCondition1
End Function
   


Private Sub cmdItemSearch_Click()
    Dim WhereCondition1 As String
    WhereCondition1 = BuildWhereCondition1
   
    DoCmd.OpenForm "frmAdvancedSearchSelectItem", acNormal, , WhereCondition1, acFormPropertySettings, acWindowNormal
    DoCmd.Close acForm, "frmAdvancedSearch", acSavePrompt
    DoCmd.OpenForm "frmAdvancedSearch", acNormal, , , acFormPropertySettings, acWindowNormal
    Forms!frmAdvancedSearchSelectItem.SetFocus
End Sub

 

[Pat Hartman]

I see that you've decided to stick with your ElseIF rather than to use the cleaner and better structure of the Case statement.

You've been around long enough to know that starting new threads on the same topic just wastes the time of the folks trying to help.

 

[mikenyby]

I see that you've decided to stick with your ElseIF rather than to use the cleaner and better structure of the Case statement.

You've been around long enough to know that starting new threads on the same topic just wastes the time of the folks trying to help.

Hi @Pat Hartman, I certainly was not aware of the etiquette around starting new threads, please accept my apology. The members of this forum, including yourself, have been an incredible source of help for me in learning how to do a job that I'm frankly not qualified for. I certainly don't want to offend anyone. In this situation, I figured a new thread was the best thing to do as the topic at hand had strayed so far from the original post.

Regarding ElseIf, you have expressed your dislike for it, but haven't really gone into why you dislike it other than that it makes the code harder to read, especially if not comprehensibly indented. While I certainly agree that Case statements are a more elegant solution in a situation that would require dozens of nested ElseIfs, but in this case where there are only a few ElseIf statements--with each one's purpose rather clear in the short line of code attached-- the structure I have used appears to be a simpler solution to me. I am not aware of a functional advantage to using Case statements instead, but if there is one that makes sense in this context, I'd be more than happy to adjust the code I'm using.

I would also like to address why I am using "like" in the year field. My yearofprovenance field is a text field, not an integer field. This is because many of our archival resources are handwritten registered that span a number of years and thus are entered 1928-1932 or whatever. Using "like" in this instance also allows the user to search by decade, which is a common request because many information seekers don't know the exact year or years that persons were at this institution, but do know the decade.

 

[The_Doc_Man]

I am not aware of a functional advantage to using Case statements

When you have an IF/THEN/ELSEIF... ladder in code, the number of ELSEIF's is what makes the advantage or disadvantage. Both CASE and ELSEIF are tools in your tool chest of ways to decide something.

CASE statements make better sense the more alternatives you have. One or two ELSEIF's in an IF ladder might not be egregious - but it is a matter of "what seems right for you." The number of "right times to use it" depend on your comfort level and on how clearly you can read it when you put it aside for another problem and have to come back six months later because that section is acting up again. If you forgot the logic associated with the complex IF ladder, you just did yourself some dirt.

There is actually one functional difference, though. In a SELECT CASE construct, you evaluate the deciding value ONCE (in the SELECT sub-clause) and compare the same value for as many CASE sub-clauses as you have. For an ELSIF, the deciding value is re-evaluated for each IF or ELSEIF sub-clause. So for a long IF/ELSIF ladder, you actually perform more computations. Maybe they occur too quickly to make a difference most of the time - but you are working harder for IF/ELSEIF than you would work for SELECT CASE.

 

[mikenyby]

When you have an IF/THEN/ELSEIF... ladder in code, the number of ELSEIF's is what makes the advantage or disadvantage. Both CASE and ELSEIF are tools in your tool chest of ways to decide something.

CASE statements make better sense the more alternatives you have. One or two ELSEIF's in an IF ladder might not be egregious - but it is a matter of "what seems right for you." The number of "right times to use it" depend on your comfort level and on how clearly you can read it when you put it aside for another problem and have to come back six months later because that section is acting up again. If you forgot the logic associated with the complex IF ladder, you just did yourself some dirt.

There is actually one functional difference, though. In a SELECT CASE construct, you evaluate the deciding value ONCE (in the SELECT sub-clause) and compare the same value for as many CASE sub-clauses as you have. For an ELSIF, the deciding value is re-evaluated for each IF or ELSEIF sub-clause. So for a long IF/ELSIF ladder, you actually perform more computations. Maybe they occur too quickly to make a difference most of the time - but you are working harder for IF/ELSEIF than you would work for SELECT CASE.

Thank you @The_Doc_Man ! That makes a lot sense. Based on your explanation, I don't think CASE statements are really necessary with only a few ELSEIFs here, but after I finish this DB my institution wants me to make another one for another department and I'll almost certainly use CASE statements for the advanced search functions. However, all this is outside my job description so it's only going to happen if they approve my business case for a new title and more salary!

 

[CJ_London]

you don't actually need your WhereCondition1 variable in either function or sub

you can just use

if Me.cboItemField1 = "TITLE" Then
         BuildWhereCondition1 = "[ItemName] Like '*" & [Forms]![frmAdvancedSearch]![txtItemSearch1] & "*' "
ELSEIF.....

and

DoCmd.OpenForm "frmAdvancedSearchSelectItem", acNormal, , BuildWhereCondition1, acFormPropertySettings, acWindowNormal

and the function does not need to be public since you are using it within the same form (and presumably wouldn't call it from another form)

 

[mikenyby]

you don't actually need your WhereCondition1 variable in either function or sub

you can just use

if Me.cboItemField1 = "TITLE" Then
         BuildWhereCondition1 = "[ItemName] Like '*" & [Forms]![frmAdvancedSearch]![txtItemSearch1] & "*' "
ELSEIF.....

and

DoCmd.OpenForm "frmAdvancedSearchSelectItem", acNormal, , BuildWhereCondition1, acFormPropertySettings, acWindowNormal

Thank you for this, I'll revisit the code and see if I can make it more elegant.

and the function does not need to be public since you are using it within the same form (and presumably wouldn't call it from another form)

That actually clears something up for me. I was misinformed to think public/private meant whether or not that function or sub could be called from another sub on the same form. What you say makes more sense.

 

[Pat Hartman]

While I certainly agree that Case statements are a more elegant solution

I don't do "elegant". I do clear, easy to read, easy to modify. ElseIf's do not fit into any of those three categories, let alone all of them. "Elegant" is something else entirely and not actually something to strive for. There is a case for ElseIf's (even though I still don't use them) when the If's are referencing different variables. When you have a string of If's referencing the SAME variable, there is no better, easier to understand and modify solution than a Case statement. The ElseIf is a completely unnecessary construct and is left over from the early days of third generation languages before the Case statement was added.

If you are going to start a new thread on the same or a close derivative, mark the original solved and add a link to the new question. Also add a link in the new question.

 

[mikenyby]

If you are going to start a new thread on the same or a close derivative, mark the original solved and add a link to the new question. Also add a link in the new question.

Thank you, I'll be sure to do so in the future.