Full Version: Error Message Using DoCmd.OpenForm To Filter Records
UtterAccess Discussion Forums > Microsoft® Access > Access Forms
MtnGoat
I'm getting the following message -

"The OpenForm action was closed"

when I use the following code behind a command button:

CODE
stDocName = "frmPhoneLog"
    stLinkCriteria = "tblContact.ContactLN Like Chr(34)*Chr(34) &" & _
    "[Forms]![frmMainMenu].[Caller] & Chr(34)*Chr(34)" & _
    "AND tblContact.ResolvedDate Is Null"
    DoCmd.OpenForm stDocName, , , stLinkCriteria


The filter works if I use "=" instead of "Like Chr(34) . . ." but then I can't use partial names.

Any suggestions?
BenPurser
You are actually creating a string that includes your "Chr(34)" characters in the literal sense...try this:

stLinkCriteria = "[tblContact.ContactLN] Like " & Chr(34) & "*" & [Forms]![frmMainMenu].[Caller] & "*" & Chr(34) & " AND [tblContact.ResolvedDate] Is Null"

HTH
Ben
SerranoG
Assuming this code is called from frmMainMenu and the textbox Caller is on that same form, and frmPhoneLog gets its data from tblContact, then

CODE
stDocName = "frmPhoneLog"
stLinkCriteria = "[ContactLN] LIKE '*" & Me.Caller & "*' AND [ResolvedDate] Is Null"
DoCmd.OpenForm stDocName, , , stLinkCriteria
MtnGoat
Hi Ben. Thanks for the code - it works.

I'm revising some of my query-based forms, using the SQL as string criteria filters for the OpenForm command. I was having some difficulty visualizing your code, so I passed it back to a query as SQL and was better able to see what you were suggesting after that.

Much appreciated.
MtnGoat
Hi Greg. Your code works also. Nice and tight.

It appears I don't need to use Chr(34). What is the '*" & Me.Caller & "*' doing? I've never seen that syntax before.

Thanks for your input.
SerranoG
You had CHR(34) in your code. That is a quotation mark ". You essentially were stating this:

CODE
...Like " & Chr(34) & "*" & [Forms]![frmMainMenu].[Caller] & "*" & Chr(34) & " AND...


what you were doing was surrounding your Caller with wildcards and surrounding those with quotes. You separated the quotes into CHR(34) so Access wouldn't confuse the quote surrounding your text search vs. the quotes needed for the SQL statement. I merely avoided all that by doing

CODE
...Like '*" & Me.Caller & "*' AND...


Using single quotes around a word or variable for text searches replaces the need for using CHR(34) or double double-quotes that some people use.

All the same output but getting simpler to type and understand and no need to remember that 34 is a double-quote.:

CODE
"SELECT [lngRecNo] FROM tblTable WHERE [strText] = " & Chr(34) & Me.txtText & Chr(34)
"SELECT [lngRecNo] FROM tblTable WHERE [strText] = " & """" & Me.txtText & """"
"SELECT [lngRecNo] FROM tblTable WHERE [strText] = """ & Me.txtText & """"
"SELECT [lngRecNo] FROM tblTable WHERE [strText] = '" & Me.txtText & "'"
MtnGoat
Thanks Greg. It's a good day when you learn something new - that also works.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.