Using MS Access/Access 2007 Lookup

Advertisement


Question
QUESTION: I am using Access 2007.  I have created a button on a form that performs a word lookup in a field using a parameter and then opens a report with any records that have that particular word in that field.

Example:  Form Button generates Parameter: "Enter Item or first few letters or press Enter for all" then type in "magneto" and it looks in the field "Items" and generates the report.

Is there a way that whatever word is entered into the Parameter that word could be in the Report title?

Example:  The Report generated in the example above would automatically have "Magneto" in the Title because that is what was typed in the lookup parameter.

ANSWER: Yes, but not the way you are doing it. It sounds like you are using a parameter prompt query. You have set the criteria in your query to something like:

Like "*" & [Enter Item or first few letters or press Enter for all] & "*"

Instead, create a form, on the form, have a text box to enter the word. In your query set the criteria to:

Like "*" & Forms!formname!controlname & "*"

Add a button on the form to run the report.

On the report have a text box with a controlsource of:
=Forms!formname!controlname

In both cases, formname is the name of your form and controlname the name of the textbox you are entering the keyword in.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: I had the Look Up command "button" on my Dashboard Form.  Do I add a new text box there?  Is the text box you want me to add blank or do you mean to use the appropriate field that the records are in?  I'm a little confused by your answer, sorry.

ANSWER: If you have a button on your form to already run your report or query, then yes you can use the same form for the unbound textbox.

If you can post the SQL of your query I can maybe be clearer.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

Report Screen Print
Report Screen Print  
QUESTION: Getting close - the query works fine, the command button on the dashboard opens the report fine, however, the report opens fine but the title is not showing what was typed into the parameter box.

Example:  If "magneto" is typed into the parameter box - that word should show up in the header of the report. See attached screen print of report.

As you requested SQL of Query (but the query works fine)SELECT [Vender Data].[COMBINED PRODUCTS], [Vender Data].VENDORID, [Vender Data].[Parts Section], [Vender Data].VENDORNAME, [Vender Data].Deleted, [Vender Data].TELEPHONE1, [Vender Data].TEL1ADD, [Vender Data].TOLLFREE, [Vender Data].TOLLFREEADD, [Vender Data].FAX1, [Vender Data].FAX1ADD, [Vender Data].TELEPHONE2, [Vender Data].TEL2ADD, [Vender Data].FAX2, [Vender Data].FAX2ADD, [Vender Data].TELEX, [Vender Data].CONTACT, [Vender Data].WEB, [Vender Data].EMAIL, [Vender Data].STREETADDRESS1, [Vender Data].STREETADDRESS2, [Vender Data].CITY, [Vender Data].ST, [Vender Data].ZIP, [Vender Data].FIELDLOC, [Vender Data].CATEGORIES, [Vender Data].[WOH PRICING], [Vender Data].COMMENTS, [Vender Data].COUNTRY
FROM [Vender Data]
WHERE ((([Vender Data].[COMBINED PRODUCTS]) Like "*" & [Forms]![Parts_Catalog_Dashboard]![Look_Up_Item] & "*") AND (([Vender Data].Deleted) Is Null))
ORDER BY [Vender Data].VENDORNAME;

Answer
Are you closing the Form?

Also its hard to tell, but the ControlSource of the control doesn't appear to be correct. It looks like you have extra parentheses in there. It should be:

=[Forms]![Parts_Catalog_Dashboard]![Look_Up_Item]

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA  

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.