I have a table of medical complaints. I am trying to give the user the chance to see if a similar term has been added prior to them adding a new complaint. Ex: The term "seizure" exists and the user wishes to add "seizures". I have taken the term the user has entered and trimed a few letters off it, and placed it in the variable strTerm1.

I have tried to create a sql statment as follows:

strSQL = "SELECT [tblChiefCompl_fe].[ChiefCompl_Description]" & _
" FROM [tblChiefCompl_fe]" & _
" WHERE ((([tblChiefCompl_fe].[ChiefCompl_Description])" & _
" LIKE """ & strTerm1 & "*""" & "))"

And process it using:

DoCmd.RunSQL (strSQL)

Access 2000 responds with error 2342. RunSQL requires an arguement consisting of a sql statement.

Can anyone see what is probably an obvious error on my part?

Thanks

Greg,

The DoCmd.RunSQL can't be used to retrieve values. You can
only use it to do updates/inserts/deletes (action queries).
Where would it display the returned value(s)?

You can use the DLookUp function to put the description into
a textbox on your form (or "No Value" if none found):


Me.SomeControl = Nz(DLookUp("[ChiefCompl_Description]", _
"[tblChiefCompl_fe]", _
"[ChiefCompl_Description] LIKE '*" & strTerm1 & "*'"), "No value")


Wayne

Thank you Wayne. I appreciate the response. Time to reconsider how this form will work. Should be easier with yuor help.

After working with this a bit, I realize that Dlookup may not meet my needs. DLookup does work to find a single record, but there may be multiple records that fit the criteria.

The user may enter Chest Pain. I would like them to see that
Chest Pain - Cardiac
Chest Pain - Trauma
Chest Pain - Non Cardiac
Chest Wall Pain
Already exists.

I made a list box lstSmlrCmplnts. I've created a method of seperating out each word so that I can search on any one or all. I'm confused how to populate the list box with the results of a query.

Got it figured out. Thanks for the ideas.