How do I create a drop down box?

Hello,
I'm trying to create a drop down box, so that the end user can choose a table heading. Once they choose that table heading, they will get a popup that will ask what they want to search/query for. For example, I have 5 table heading which are labeled "Manufacturer", "Product Name", "Type of Product", and "Box Number". The end user would go to the drop down box and choose the heading "Manufacturer". Next they should be prompted to "Enter Keyword". They would enter "Microsoft". Finally, a listing of all Microsoft items would pull up from the table. I have a button created that will run a query on the column "Product Name". For example, the end user will click on a button called "Run Query". Once they click on this button, they are prompted to "Enter Keyword". Once they enter something like "Word", everything with "Word" in the "Product Name" column of my table will pull. I need to create a drop down box which will give my user the option of choosing any column in the table to do a search on. If anyone has a way of doing this, I would really appreciate it. Thank you. Dawn

Hi there,
what language are you writing this in? PHP or ASP or other?

the theory is the same what ever you are writing it in...

open the DB table "products" (or whatever!) using and SQL...

"SQL = SELECT column_headers FROM product;"

then using a while loop output the results in to the drop down box... something like: ( i will write in ASP... but it can easily be converted in to PHP) (comments in //)


//first draw the beginning of the <select> box (the drop down box)

<select name="select_colum">

<%

while NOT objRS.EOF //start loop to get results

response.write "<option value=" & objRS("column_name") & ">" & objRS("column_name") & "</option>

wend // end loop
%>
</select>

if there were say 4 records in the "products" table (4 different headings say) the drop down menu would look like:

<select name="select_column">
<option value="Product_Name">Product_Name</option>
<option value="Manufacturer">Manufacturer</option>
<option value="Type_of_Product">Type_of_Product</option>
<option value="Box_Number">Box_Number</option>
</select>

I hope this goes some way to helping you.... let me know if I have understood you correctly!

Regards,

forgot to say that you will need to say

objRS.MoveNext

before the "wend" part...otherwise it wont move to the next record and you'll get an error msg!

Hello Matt,

I'm pretty new to the behind the scenes workings of Microsoft Access, so I'm not entirely sure where to put this code. Would I go into Build Event to start this or should I create another new button and start over.

Please help.

Thank you.

Dawn

what are you making the website in!?

Hello. This isn't a website. I'm actually working in Microsoft Access 97. The user has an Inventory database that they need to be able to query off of. Right now, I have it setup so the user can click on a button and type in "Windows" and all of the items in the Manufacturer column with the word "Windows" will come up in a table. I used the code {Like "*" & [Enter Keyword] & "*"}, without the brackets in the Manufacturer column, but when I use it in the criteria column of the other columns it will not work. I need to find a way to reference each column header in my table, so that I will only get query results from that particular column. Therefore, I have 5 columns with the headings Product Name, Type of Product, Manufacturer, ID/Serial Number, and Box Number. I need to find a way to specify each column header in my criteria, so that I will only get results from that particular column. So, the user will be able to go to the drop down box that I've created and choose "Type of Product". Then a box will pop up and the user can type in "Software". Finally, all of the items in the "Type of Product" column withe the word "Software" will come up in a table. I have this working for one column, but the putting the same criteria in each column does not work. Any suggestions would be greatly appreciated. Thank you in advance.

aaah I see...so you are using forms? Well, I have the Access 97 bible (over 1000 pages..!) so I'll have a squiz through and see what I can find... not used access like that for a few years!!

I'll get back to you asap!

Thank you. I'm going to try a few things on my end too.

Dawn

Are You Ready! Probably Not but here it is:
[img]mk:@MSITStore:C:Program%20FilesMicrosoft%20OfficeO ffice101033acmain10.chm::/images/collapsetri.gif[/img]Hide All

Create a form to enter report criteria

1. Create an unbound form (unbound form or report: A form or report that isn't connected to a record source such as a table, query, or SQL statement. (The form's or report's RecordSource property is blank.)) that prompts for report criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter. For example, the following criterion selects records for which the value for the Order Amount field is greater than 30,000: Order Amount > 30000.). [img]mk:@MSITStore:C:Program%20FilesMicrosoft%20OfficeO ffice101033acmain10.chm::/images/bluedrop.gif[/img]How?
   1. In the Database window (Database window: The window that appears when you open an Access database or an Access project. It displays shortcuts for creating new database objects and opening existing objects.), click Forms [img]mk:@MSITStore:C:Program%20FilesMicrosoft%20OfficeO ffice101033acmain10.chm::/Images/dbcform.gif[/img] under Objects.
   2. Click the New button on the Database window toolbar.
   3. In the New Form dialog box, click Design View, and click OK.<LI class=LPROC>In Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.), set the following form properties. PropertySettingCaptionName you want to appear in the title bar of the formDefaultViewSingle FormViewsAllowedFormScrollBarsNeitherRecordSelectorsNoNavigationButtonsNoBorderStyleDialog
   4. Click the Text Box tool [img]mk:@MSITStore:C:Program%20FilesMicrosoft%20OfficeO ffice101033acmain10.chm::/images/textbox.gif[/img] on the toolbox (toolbox: A set of tools that are available in Design view to add controls to a form, report, or data access page. The toolset available in page Design view is different from the toolset available in form and report Design view.) to add a text box to the form for each criteria you want to enter.
   5. Set the properties for the text boxes as follows. PropertySettingNameName that describes the type of criteria; for example, BeginningDate.FormatFormat that reflects the data type (data type: The characteristic of a field that determines what type of data it can hold. Data types include Boolean, Integer, Long, Currency, Single, Double, Date, String, and Variant (default).) of the criteria. For example, for a date criteria, select a format such as Medium Date.
   6. Save the form and give it a name, such as Sales Dialog. You'll add OK and Cancel command buttons to the form after you create macros for them.
2. In the Database window (Database window: The window that appears when you open an Access database or an Access project. It displays shortcuts for creating new database objects and opening existing objects.), click Macros [img]mk:@MSITStore:C:Program%20FilesMicrosoft%20OfficeO ffice101033acmain10.chm::/images/dbcmac.gif[/img], and then click the New button on the Database window toolbar.
3. Create a macro that opens the Sales Dialog form.
   
   
   [img]mk:@MSITStore:C:Program%20FilesMicrosoft%20OfficeO ffice101033acmain10.chm::/images/bluedrop.gif[/img]How?
   1. Begin by clicking Macro Names [img]mk:@MSITStore:C:Program%20FilesMicrosoft%20OfficeO ffice101033acmain10.chm::/images/macronms.gif[/img] to display the Macro Name column. Type a macro name, such as Open Dialog, in the Macro Name column, and then click the OpenForm action (action: The basic building block of a macro; a self-contained instruction that can be combined with other actions to automate tasks. This is sometimes called a command in other macro languages.). Then set the action arguments (action argument: Additional information required by some macro actions — for example, the object affected by the action or special conditions under which the action is carried out.) as follows. ArgumentSettingForm NameSales DialogViewFormData ModeEditWindow ModeDialog
   2. Add a second action, CancelEvent, that cancels previewing or printing the report when the Cancel button on the form is clicked.
   3. If the Condition column is not displayed, click Conditions [img]mk:@MSITStore:C:Program%20FilesMicrosoft%20OfficeO ffice101033acmain10.chm::/images/conditn.gif[/img] .
   4. 
      
      Type the following expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) in the Condition column:
      
      Not IsLoaded("Sales Dialog") Note IsLoaded is a function defined in the Utility Functions module in the Northwind sample database. It's used to check whether a form is open in Form view (Form view: A window that displays a form to either show or accept data. Form view is the primary means of adding and modifying data in tables. You cannot change the design of a form in this view.) or Datasheet view (Datasheet view: A window that displays data from a table, form, query, view, or stored procedure in a row-and-column format. In Datasheet view, you can edit fields, add and delete data, and search for data.). You must define the IsLoaded function in your database before you can use it. (You can copy and paste this function from Northwind into a utility module in your database.)
4. 
   
   
   Create a macro that closes the form. [img]mk:@MSITStore:C:Program%20FilesMicrosoft%20OfficeO ffice101033acmain10.chm::/images/bluedrop.gif[/img]How?
   
   Give the macro a name, such as Close Dialog. Click the Close action (action: The basic building block of a macro; a self-contained instruction that can be combined with other actions to automate tasks. This is sometimes called a command in other macro languages.). Then set its action arguments (action argument: Additional information required by some macro actions — for example, the object affected by the action or special conditions under which the action is carried out.) as follows: ArgumentSettingObject TypeFormObject NameSales DialogSaveNo
   

<LI>

1. Create a macro for the OK button. [img]mk:@MSITStore:C:Program%20FilesMicrosoft%20OfficeO ffice101033acmain10.chm::/images/bluedrop.gif[/img]How?
2. This macro hides the form. Give the macro a name, such as OK, and click the SetValue action (action: The basic building block of a macro; a self-contained instruction that can be combined with other actions to automate tasks. This is sometimes called a command in other macro languages.).Then set its action arguments (action argument: Additional information required by some macro actions — for example, the object affected by the action or special conditions under which the action is carried out.) as follows: ArgumentSettingItem[Visible]ExpressionNo
3. 
   
   
   Create a macro for the Cancel button. [img]mk:@MSITStore:C:Program%20FilesMicrosoft%20OfficeO ffice101033acmain10.chm::/images/bluedrop.gif[/img]How?
   
   This macro closes the form. Give the macro a name, such as Cancel, and click the Close action (action: The basic building block of a macro; a self-contained instruction that can be combined with other actions to automate tasks. This is sometimes called a command in other macro languages.). Then set its action arguments (action argument: Additional information required by some macro actions — for example, the object affected by the action or special conditions under which the action is carried out.) as follows: ArgumentSettingObject TypeFormObject NameSales DialogSaveNo
4. Save and close the macro group. Give the macro group a name — for example, the same name that you gave the unbound form.

1. Add OK and Cancel command buttons to the form. [img]mk:@MSITStore:C:Program%20FilesMicrosoft%20OfficeO ffice101033acmain10.chm::/images/bluedrop.gif[/img]How?
2. <LI class=LPROC>Reopen the Sales Dialog form in Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.). <LI class=LPROC>Make sure Control Wizards [img]mk:@MSITStore:C:Program%20FilesMicrosoft%20OfficeO ffice101033acmain10.chm::/images/cntlwizh.gif[/img] in the toolbox (toolbox: A set of tools that are available in Design view to add controls to a form, report, or data access page. The toolset available in page Design view is different from the toolset available in form and report Design view.) isn't selected and create an OK command button.
3. Set its properties as follows. PropertySettingNameOKCaptionOKDefaultYesOnClickName of the macro; for example, Sales Dialog.OK<LI class=LPROC>Create a Cancel command button, and set its properties as follows. PropertySettingNameCancelCaptionCancelOnClickName of the macro; for example, Sales Dialog.Cancel

1. Save and close the form.
2. Enter the criteria in the underlying query or stored procedure for the report. [img]mk:@MSITStore:C:Program%20FilesMicrosoft%20OfficeO ffice101033acmain10.chm::/images/bluedrop.gif[/img]How?
3. Open the underlying query or stored procedure for the report in Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.).<LI class=LPROC>Enter the criteria for the data. In the expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.), use the Forms object, the name of the form, and the names of the controls (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.) in the criteria.
   • For example, in a Microsoft Access database (Microsoft Access database: A collection of data and objects, such as tables, queries, or forms, related to a particular topic or purpose. The Microsoft Jet database engine manages the data.) (.mdb), for a form called Sales Dialog, you would use the following expression to refer to controls named Beginning Date and Ending Date in the query: Between [Forms]![Sales Dialog]![Beginning Date] And [Forms]![Sales Dialog]![Ending Date]
   • In a Microsoft Access project (Microsoft Access project: An Access file that connects to a Microsoft SQL Server database and is used to create client/server applications. A project file doesn't contain any data or data-definition-based objects such as tables and views.) (.adp), you must first explicitly name the parameters in the stored procedure; for example:
     
     @Beginning_Date datetime, @Ending_Date datetime
     
     and then use those parameters in the WHERE clause; for example:
     
     WHERE Sales.ShippedDate Between @Beginning_Date And @Ending_Date In an Access project, you set the reference to the controls on the form in the InputParameters property in the report, as shown in the next procedure.
4. <LI class=LPROC>In a Microsoft Access project (Microsoft Access project: An Access file that connects to a Microsoft SQL Server database and is used to create client/server applications. A project file doesn't contain any data or data-definition-based objects such as tables and views.), set the InputParameters property in the main report.
5. [img]mk:@MSITStore:C:Program%20FilesMicrosoft%20OfficeO ffice101033acmain10.chm::/images/bluedrop.gif[/img]How?
6. <LI class=LPROC>Open the report in Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.).
7. Set the report's InputParameters property to a string that specifies the parameters that are passed to the stored procedure that the report is bound to. As in the following example, the string must be an expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) that includes the parameters you specified in the stored procedure and the reference to the controls on the dialog box: @Beginning_date datetime = [Forms]![Sales Dialog]![Beginning Date], @Ending_date datetime = [Forms]![Sales Dialog]![Ending Date]

1. Attach the macros to the main report. [img]mk:@MSITStore:C:Program%20FilesMicrosoft%20OfficeO ffice101033acmain10.chm::/images/bluedrop.gif[/img]How?
2. Open the report in Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.).
3. Set the following report properties. PropertySettingOnOpenName of the macro that opens the Sales Dialog form; for example, Sales Dialog.Open DialogOnCloseName of the macro that closes the form; for example, Sales Dialog.Close Dialog

Hopefully you can do this. It took me awhile to figure it out.