Drop Down Menu in a Parameter Box?

[VAsbury]

Co-worker asked me if there was a way to add a drop down selection menu to a parameter box. Seems like ACCESS can do a lot of neat things. Any assistance of problem solving this would be appreciated.
Thanks,
VA

 

[boblarson]

You can if you build your own form instead of relying on parameter prompts.

 

[AccidentalFate]

you should still be able to do it in a table if you prefer to... go to table design view... lookup tab... display control change to list box/combo box

 

[boblarson]

you should still be able to do it in a table if you prefer to... go to table design view... lookup tab... display control change to list box/combo box

In my opinion that is a bad thing to do. You should not use lookups at table level unless you are very, very experienced and know of all of the pitfalls of doing so.

 

[AccidentalFate]

In my opinion that is a bad thing to do. You should not use lookups at table level unless you are very, very experienced and know of all of the pitfalls of doing so.

just saying that it is possible

 

[boblarson]

just saying that it is possible

Yes, but without sharing the possible pitfalls to that suggestion is why I chimed in and stated thusly.

 

[VAsbury]

Thank you to all who have posted. Today is my first day at the forum.
I did find another item posted 11/14/2003 from Mile-O which stated:

You need the combobox to be on a form.
Once you have the combobox on a form ou can reference it in the criteria: i.e. [Forms]![MyForm]![MyCombo]

Okay, SQL is not my first language.

Can anyone help step me through this process?

If I am in the query - which is based on a table.
The data entry for the table is a form with a combo box.
What next?

Thanks,
Valerie

 

[spikepl]

Jumping of bridges is also possible. But perhaps not advisable?

 

[boblarson]

Thank you to all who have posted. Today is my first day at the forum.
I did find another item posted 11/14/2003 from Mile-O which stated:

You need the combobox to be on a form.
Once you have the combobox on a form ou can reference it in the criteria: i.e. [Forms]![MyForm]![MyCombo]

Okay, SQL is not my first language.

Can anyone help step me through this process?

If I am in the query - which is based on a table.
The data entry for the table is a form with a combo box.
What next?

Thanks,
Valerie

See if this helps:
http://www.btabdevelopment.com/ts/frmrefinqry

 

[VAsbury]

Thanks for the link and awesome information.
I have tried the solution.
It did not work.
I discovered the "loaded form" is one that you open in the design view before the expression builder.

Still no drop down menu in the parameter box.

Suggestions?

Thanks,
VA

 

[boblarson]

Thanks for the link and awesome information.
I have tried the solution.
It did not work.

So can you outline the exact steps you have tried to do?

I discovered the "loaded form" is one that you open in the design view before the expression builder.

So did you actually build a form with a combo box on it from which you would be selecting the parameter value for the query?

Still no drop down menu in the parameter box.

How about you upload your database here with fake data so then we can put it in place and you can see how it is done?

If you decide to do so, please remember to

1. Run Compact and Repair on the database file
2. Right click on the file and select SEND TO > COMPRESSED FOLDER to zip it
3. Include a description of which query it is and which form you would like to use to select the criteria from the combo box.

 

[VAsbury]

I tried the following:

1. Created a form (Form1) based on a table "Process Failure".

• The table only contains one field "Process Failure".
• The form contains a label (Process Failure_Label).
• The form contains a combo box (Combo0)
• It is unbound like displayed in the example you supplied.
  • (I could not see all of the property sheet. Does the Record set type = dynaset?)

2. Followed the example using the builder in the query and result was: Forms![Form1]![Combo0]

3. When query viewed in datasheet view there is no parameter pop-up box, but there is a drop down menu.

4. In the Query the Table for all fields is "Medication" where all the data is stored.

5. The Process Table is also used as a LookUp in the Design view of the Medication Table (Display Control - combo box).

6. Medication Form used for data entry in the Medication Table contains the drop down combo box also.

7. I tried using the Medication form and following the expression builder directions. Result was the same as with the Form1.

Am I missing a step?

Thanks,
VA

 

[boblarson]

I think we need to have you upload a copy of the database to keep from having to write a book. Also, it sounds like you have used lookups directly on fields at table level and, for most new users, that is not a wise thing to do.

Read this to see why:
http://www.mvps.org/access/lookupfields.htm

 

[VAsbury]

Thanks for the additional information. I appreciate your expertise.

We use the lookup combo boxes to enhance accurate data entry.
This improves data integrity by refining criteria for queries and reports.
We have kept the process of queries pretty simple, but obviously by the information you provided the more complex, the more problems we may encounter due to masking relational methodology.

I appreciate the advice and am learning alot on this forum.

Thanks,
VA

 

[boblarson]

Thanks for the additional information. I appreciate your expertise.

We use the lookup combo boxes to enhance accurate data entry.
This improves data integrity by refining criteria for queries and reports.
We have kept the process of queries pretty simple, but obviously by the information you provided the more complex, the more problems we may encounter due to masking relational methodology.

I appreciate the advice and am learning alot on this forum.

Thanks,
VA

You can use combo boxes in FORMS for data entry. NOBODY should be doing data entry directly into tables.