need help understanding and fixing search criteria code

[Robert Blackwell]

I'm trying to duplicate a search function that is on the main screen of our
database, but change the search criteria and use a new form.

The main screen of our dbase is frmContacts

My problem is when I've duplicated the search from frmContacts onto a
new/blank form the search does not work.

This is the code in the criteria field of the query for the "Name" column is
Like [Forms]![frmContacts]![SearchContacts] & "*"


Now, I'm still a novice but I thought exclamations meant not? So I don't
understand their meaning here except maybe concatenation?
It would make sense though that it is not working because this new search
form that I am making is not on the original frmContacts, however, I did try
replacing frmContacts with frmTestSearch which is the name of my new form,
and it still did not work.

Any help greatly appreciated, also I'll be happy to reply back with as much
info to help you help me )

 

[fredg]

I'm trying to duplicate a search function that is on the main screen of our
database, but change the search criteria and use a new form.

The main screen of our dbase is frmContacts

My problem is when I've duplicated the search from frmContacts onto a
new/blank form the search does not work.

This is the code in the criteria field of the query for the "Name" column is
Like [Forms]![frmContacts]![SearchContacts] & "*"

Now, I'm still a novice but I thought exclamations meant not? So I don't
understand their meaning here except maybe concatenation?
It would make sense though that it is not working because this new search
form that I am making is not on the original frmContacts, however, I did try
replacing frmContacts with frmTestSearch which is the name of my new form,
and it still did not work.

Any help greatly appreciated, also I'll be happy to reply back with as much
info to help you help me )

A Query criteria of
Like Forms!frmTestSearch![SearchContacts]] & "*"
should find all names that BEGIN with the text entered in the form
control, IF... the name of the control on the form is SearchContacts
and the form is OPEN when the query is run.

Note: If you really do have a field in your table named "Name" I would
strongly suggest you change it to something else, perhaps "txtName",
"ClientName", etc.
Name is a reserved word in Access/VBA/Jet, and should not be used as a
field name.

See the Microsoft KnowledgeBase article for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

 

[John Vinson]

Now, I'm still a novice but I thought exclamations meant not? So I don't
understand their meaning here except maybe concatenation?

In the context of [Forms]![formname]![controlname], the ! character is
simply a delimiter meaning "a member of a collection". The Forms
collection is the set of all open Forms in an access database; your
form's name is one member of that collection. And a Form's default
collection is the set of Controls on that form.

John W. Vinson[MVP]

 

[Robert Blackwell]

I know about reserved names, I was just using an example. Here's a snapshot
of my designview query.

http://www.biblesatcost.com/download/qry_designview.gif

Also, regarding the form not working when the frmContacts isn't open isn't a
problem because that one is always open I have another explaination as to
maybe why later if it's important.

Anyways, what's most important is just getting this thing to work.

in the screenshot it shows frmTestSearch instead of frmContacts because I
changed it to see if that worked. But considering that it is it's own form
and no longer part of the frmContacts this is probably how it should be
anyways.

Here's the code under my search text box:

Option Compare Database

Private Sub InvoiceNoSearch_AfterUpdate()
Me.subfrmSearchOrders.Requery
End Sub

Private Sub InvoiceNoSearch_DblClick(Cancel As Integer)
Me.InvoiceNoSearch = Null
Me.subfrmSearchOrders.Requery

End Sub

 

[John Vinson]

I know about reserved names, I was just using an example. Here's a snapshot
of my designview query.

http://www.biblesatcost.com/download/qry_designview.gif

Thanks for posting a link instead of an attachment... but it's really
much better to open the query in SQL view and post the SQL text here.
That is the *real* query, and most of the volunteers can read it
easily. Could you please do so?


John W. Vinson[MVP]

 

[Robert Blackwell]

SELECT DISTINCTROW [LastName] & ", " & [FirstName] AS GetName,
tblOrderHdr.InvoiceNo, tblContactData.LastName, tblContactData.ConStatusID,
tblContactData.ContactID, tblContactData.ChurchName
FROM (tblContactData LEFT JOIN tblContactTypes ON
tblContactData.ContactTypeID = tblContactTypes.ContactTypeID) INNER JOIN
tblOrderHdr ON tblContactData.ContactID = tblOrderHdr.oContactID
WHERE (((tblOrderHdr.InvoiceNo) Like
[Forms]![frmTestSearch]![InvoiceNoSearch] & "*") AND
((tblContactData.LastName) Is Not Null))
ORDER BY [LastName] & ", " & [FirstName], tblContactData.LastName;