Solved

MS Access 2010 prompted query to display a list not working, cannot trouble-shoot

Posted on 2013-06-04
16
230 Views
Last Modified: 2013-06-24
I have created a form based on a query which prompts for user name and filters the records displayed based on the name value entered. Fine. Now I want to take it to the next level: instead of the user having to type in his/her name, I'd like to a drop-down list of name values to appear, when one is selected the form is then filtered.

I do NOT want to use code. I am convinced there's a (relatively) simple way to accomplish this using basic Access function. From what I gathered on several Access forums, I pieced together the following steps:

1. Created a new form with just one control source (user name) as a combo box. The combo box has criteria attached to the Row Source to display only the users I want.

2. My main form calls this combo box with (what I believed to be) the correct syntax, attached.

Every time I open the form however, instead of a drop-down list based on the prompt, I get the attached error message suggesting Access doesn't know what to do with this prompted field. It's virtually impossible to trouble-shoot because of course when Access can't interpret something it creates a prompt, but in this case I actually want a prompt. Help!

Cathy Munsen
KMA One
Screen-captures-130604---MS-Acce.docx
0
Comment
Question by:CathyMunsen
  • 8
  • 8
16 Comments
 
LVL 21
Comment Utility
Now I want to take it to the next level ... I do NOT want to use code.

Unfortunately, using a little VBA code is what really takes your database to the next level. There is only so much you can do without using VBA code. It really is not difficult. VBA code is what makes Access databases so powerful.

To do what you are attempting will require some VBA code.  

I use a form to collect data from the user to run reports a lot.

The basic steps:
1) In the report's On Open even I use VBA code to open a  form in dialog mode.
2) After filing out the form the user click a continue button. The On CLick event uses VBA code to do data validation. If it passes it hides the form. If they click the cancel button then the  form closes.
3) Next the report checks to see if the form is still open. If it is then the report runs.

It is all doe with just a few lines of VBA code.

The Report's record source is a query that has a form reference to the control on the opened but hidden form.

There may be a way to use a form and no VBA code to do what you are attempting. Maybe with Macros. I an mot sure how.
0
 

Author Comment

by:CathyMunsen
Comment Utility
I am not opposed to using VBA code, only opposed to it when basic Access function should be able to accomplish same. (I'm serving as analyst, designer, and developer on this application and am trying to avoid potential code "black boxes" wherever possible.)

Within this same application, I have used VBA code behind a button to open a form  - the code passes variables to the form and populates the corresponding fields in the table. This seems like a reasonable use of VBA code, what I'm trying to solve here does not.

In any case, your "solution" tells me next to nothing about how to tackle this. Anyone else?

Cathy
0
 
LVL 21
Comment Utility
I was think you wanted to filter a report using a popup for not another form. Sorry about that.  

This may help: Search criteria

Also: Using a Combo Box to Find RecordsUsing a Combo Box to Find Records


... I am not opposed to using VBA code, only opposed to it when basic Access function should be able to accomplish same
Unfortunately that can lead to issues when you upgrade to a new version of Access. I have found that VBA code generally works better across multiple versions of Access. I have code I wrote over 10 years ago in Access 2.0 (Windows 3.1) that still works today. The Macros and what you call  "basic Access Function" did not work two versions later.

Boyd Trimmell, aka HiTechCoach
Microsoft Access MVP
0
 

Author Comment

by:CathyMunsen
Comment Utility
Hmmmm...I didn't say anything about a report. I'm trying to filter a FORM with a combo box on a prompted field. I will look at your link in more detail to see if the technique is transferable to a form, though my cursory review tells me your link accomplishes something else.

In terms of VBA code vs "basic Access function", I would not include macros (though I do make good use of them) in the latter category since macros are more or less a prompted way to write code, in natural language. When I say "basic Access function", in this instance I am referring to a simple statement that can be typed into the Criteria line of a query. It seems to me if I can type in: "[Enter your name]" in the Criteria line and it works, I should be able to (relatively easily) modify the statement such that "[Enter your name]" displays a list.

Thanks for trying,

Cathy
0
 
LVL 21
Comment Utility
What you wanting to do is common with Report.  The techniques used to do it with report also work the same with Forms. Sorry for the confusion. Forms and reports are very similar and can use a lot of the same controls. FYI: A form can be saved as a report.  

I'm trying to filter a FORM with a combo box on a prompted field.
Unfortunately Access does not have built-in support this.

It is common to place a combo box on a form to collect data from a user. You can use a form reference to the combo box in a query. Example: Where [CustomerID} = forms!MyFormName.MyComboboxControlName. This works great for filtering a query. For this to work the form with the combo box my be must be open when the query is executed.

When using a combo box to filter a form, it is common to place the combo box on same form that will display the data. This avoids the need for an extra form to hold the combo box like with a report.

Would it help to see an example?

Boyd Trimmell, aka HiTechCoach
Microsoft Access MVP
0
 

Author Comment

by:CathyMunsen
Comment Utility
Hi Boyd, apologies for the delay in getting back to you. (As you can imagine, this question is just one in a rather long list of development tasks for the system I'm building.)

Yes, it would definitely help to see an example! And yes, I had certainly hoped to place the combo box used in the prompt on the same form displaying the data.

Thank you,

Cathy
0
 
LVL 21
Comment Utility
Cathy,

And yes, I had certainly hoped to place the combo box used in the prompt on the same form displaying the data

There is a combo box wizard that will help you create what you want.

Steps
1)  Make sure the wizard is turned on.
2) Add a combo box to the form's header.
3) Select option 3 and follow the Wizard prompts.

Combobox Wizard
0
 

Author Comment

by:CathyMunsen
Comment Utility
Ah were it so simple! Unfortunately the 3rd option does not appear in the list, only the first two. (Yes, I have "Use Control Wizards" selected).

Assuming there is a way around this, I'm trying to visualize how this combo box works with how I have the form set up now: does it simply replace the prompted field in the Record Source query? See attached file.

Thanks for seeing this through Boyd.

Cathy
Screen-captures-130617---MS-Acce.docx
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 21
Comment Utility
The third option for the combo box wizard is only available for bound forms. The search code uses a Recordset clone of the record source of a bound form. Also make sure to include the primary key in the record source.

On a the bound form used to display the records is where you place the combo box. This would be used in place of a prompt in the query.
0
 

Author Comment

by:CathyMunsen
Comment Utility
OK, I got it. So yes, the combo box "works" on a bound form though without the ability to display only a grouped set of values and filter the form on a given value, I cannot use this method.

Please take a look at the attached file and if you can think of another way Access can (relatively easily) meet my needs, I welcome it. Otherwise I will "Accept as Solution" your last entry. Thanks again for all your efforts.

Cathy
Screen-captures-130617---MS-Acce.docx
0
 
LVL 21
Comment Utility
Ah ... you are not looking for a single record. You want all the records assigned to for a person.

You can still use a combo box on the form. In the query you would replace your prompt with a form reference to the combo box. In the combo box's after update event you would use:  one like of VBA code:

Me.Requery

Open in new window

0
 

Author Comment

by:CathyMunsen
Comment Utility
"In the combo box's after update event you would use:  one like of VBA code: 1. Me.Requery"

Is something missing here? I've read through your last entry a few times with a furled brow - it's not making sense. If not, I'm guessing it's due to my limited knowledge of VBA code. ;)

Cathy
0
 
LVL 21
Comment Utility
I will create an example and post it.
0
 

Author Comment

by:CathyMunsen
Comment Utility
Got an example? I'd love to see it and close this question. Thanks!

Cathy
0
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 500 total points
Comment Utility
Cathy,

Sorry fore the delay.  Check out this example. There is a combo box in the form header that can be used to filter the records.
Tasks-hitechcoach.zip
0
 

Author Closing Comment

by:CathyMunsen
Comment Utility
Thanks again Boyd for seeing this all the way through. The only hitch with the detailed example you provided is the data model, specifically the one to many relationship between Contacts & Tasks which naturally requires a subform. Implementing this would involve a major restructure of my current form. (To be sure, I have several one to many relationships and subforms in my data model now, just not on the form I'm attempting to filter with a prompted query.)

Cathy
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now