Form Filter

[RayMilhon]

Is it possible on a form to set the Filter Property for more than 1 field?

For example field1 = "test" and field2 = 3

I've been trying to do this all day and gotten nowhere. Every Sample I find is for 1 field only. I need 2

[June7]

Yes.

Why doesn't your effort work - what happens?

The expression you show should work:

field1 = "test" AND field2 = 3

[RayMilhon]

There is 1 Record that meets the criteria I set but when I set filteron = true it executes the Form_Current Event as expected but the NewRecord Property = True

[orange]

There is a sample by Dick Goldgar in this thread
http://social.msdn.microsoft.com/For...e-afad4383b1f6

Hope it's helpful.

[June7]

There is 1 record that should meet the criteria but isn't? Are you building the condition with VBA? Show the code.

[RayMilhon]

Should have included this in my first post didn't occur to me. sorry about that

The portion in question is in the If rs.eof then else statement. The else portion executes because there is a record in the recordset. In testing I'm using a specific Provider and Member (my PCP and My name just happens that I'm working for the Medical Group my PCP is associated with) So I know the record exists. But as soon as the Me.FilterOn = True Executes it goes to the Form Current Event and the me.newrecord = true

Private Sub cmdmbrfilter_Click()
    Dim strsql As String
    Dim rs As New ADODB.Recordset, strmbr As String
    strmbr = InputBox("Enter Member Last Name", "Member Search")
    If strmbr = "" Then Exit Sub
    strsql = "select memb_keyid from dbo_current_membership where lastnm Like """ & strmbr & "%"";"
    rs.Open strsql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
    If rs.EOF Then
        MsgBox "There is no Current Member with that Last Name", vbCritical, "Member Data Entry Error"
        FilterOn = False
    Else
        blntest = False
        Me.Filter = "stringfromguid([memb_keyid]) = """ & StringFromGUID(rs!memb_keyid) & """ and stringfromguid([pcp_keyid]) = """ & Me.txtPCPName & """ "
        Me.FilterOn = True
    End If
    rs.Close
    Set rs = Nothing
End Sub

Form Current Event

Private Sub Form_Current()
    Dim strmemberid As String
    blnUpdated = False
    If Me.NewRecord Then
        strmemberid = InputBox("Enter Member Name", "Member")
        Me.TXTMember.RowSource = "SELECT dbo_CURRENT_MEMBERSHIP.MEMB_KEYID, dbo_CURRENT_MEMBERSHIP.REV_FULLNAME AS MBRNAME, " & _
            "dbo_CURRENT_MEMBERSHIP.LOB, dbo_CURRENT_MEMBERSHIP.MEMBID, dbo_CURRENT_MEMBERSHIP.BIRTH " & _
            "FROM dbo_CURRENT_MEMBERSHIP " & _
            "WHERE (((dbo_CURRENT_MEMBERSHIP.lastnm) Like """ & strmemberid & "%"")));"
        Me.TXTMember.Requery
    Else
        Me.TXTMember.RowSource = "qry_mbr"
        Me.TXTMember.Requery
    End If
End Sub

[June7]

What is StringFromGUID - a custom function? Why using it? Are memb_keyid and pcp_keyid text or number fields?

Why not simply:

If number type:
"[memb_keyid] = " & rs!memb_keyid & " AND [pcp_keyid] = " & Me.txtPCPName

If text type:
"[memb_keyid] = '" & rs!memb_keyid & "' AND [pcp_keyid] = '" & Me.txtPCPName & "'"

[RayMilhon]

The Data is from a SQL Server Database memb_keyid and prov_keyid are UniqueIdentifier Fields.
In MS Access they are numeric Fields with the format ReplicationID.
The Functions GUIDFromString and StringFromGUID are built in Access Functions to convert those Fields either to or from strings. I know the functions have been available since 2007 not positive they were available in Access Prior to 2007. I think they were in 2003 but not positive.

[June7]

Never connected to SQL Server DB. Wasn't aware of those functions. Why do the keyid values need to be converted?

Do you know that the recordset is retrieving the record?

[RayMilhon]

Not exactly sure of the reason but when the Replication ID is in a text box I'm guessing there is some kind of implicit conversion taking place when you use the textbox in a query It will not find a match using the stringfromguid function on the field or guidfromstring function on the textbox will find the match. If I run a query with the same criteria as the filter it does return a record. When applying the filter in the form and the Form.Current Event fires it sets the newrecord property to true.
If I remove the and portion of the filter it works fine so the issue is the and in the filter I just can't figure out why.

[June7]

The filter works with the first part of the expression? That part doesn't involve a textbox so doesn't explain why conversion needed. And the conversion doesn't seem to help with the second part of expression so I still don't comprehend it.

What is Replication ID? Don't see that name anywhere in code.

Is Me.txtPCPName bound to a field? Replication ID? If condition is pcp_keyid = Me.txtPCPName, why does the textbox have Name in its name? Does this textbox have a name string or a keyid number?

[ssanfu]

Question: if "stringfromguid" is a function, why is it enclosed within quotes? (the first " in the Me.Filter = line

<snip>
Else
        blntest = False
        Me.Filter = "stringfromguid([memb_keyid]) = """ & StringFromGUID(rs!memb_keyid) & """ and stringfromguid([pcp_keyid]) = """ & Me.txtPCPName & """ "
        Me.FilterOn = True
    End If
<snip>

Shouldn't it be something like:

<snip>Else
        blntest = False
        Me.Filter = stringfromguid([memb_keyid]) = """" &  StringFromGUID(rs!memb_keyid) & """ and "" & stringfromguid([pcp_keyid]) =  """ & Me.txtPCPName & """ "
        Me.FilterOn = True
    End If<snip>

Just saw that the 3rd "StringFromGUID" function was enclosed in quotes also.

[June7]

The reason for enclosing in quotes, as far as I can tell, is to include the function in the expression that the Filter property is set to. The result should be the same as if manually typing the expression into the Filter property. Once the Filter property is set to that expression, it should execute the function on the field. Without the quotes, the string passed to the Filter property would be: TRUE

[ssanfu]

It might be more code, but you could try:

Private Sub cmdmbrfilter_Click()
    Dim strsql As String
    Dim strMemb As String, strRS_Memb As String, strPCP As String, strFilter As String
    Dim rs As New ADODB.Recordset, strmbr As String
    strmbr = InputBox("Enter Member Last Name", "Member Search")
    If strmbr = "" Then Exit Sub
    strsql = "select memb_keyid from dbo_current_membership where lastnm Like """ & strmbr & "%"";"
    rs.Open strsql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
    If rs.EOF Then
        MsgBox "There is no Current Member with that Last Name", vbCritical, "Member Data Entry Error"
        FilterOn = False
    Else
        blntest = False
        
        strMemb = StringFromGUID([memb_keyid])
        strRS_Memb = StringFromGUID(rs!memb_keyid)
        strPCP = StringFromGUID([pcp_keyid])
        
        strFilter = strMemb = strRS_Memb & " and " & strPCP = """ & Me.txtPCPName & """
        
        Me.Filter = strFilter
        Me.FilterOn = True
    End If
    rs.Close
    Set rs = Nothing
End Sub

This is easier to step through and see if the filter string is what you want/expect.

[ssanfu]

@June7:
But will VBA evaluate the call to the function when it is enclosed in quotes? My understanding is the the function name is evaluated as part if a string, not as a function call.