Solved

MS Access 2010 Forms-Create a Combo Box to list a set of values from query

Posted on 2013-01-17
21
551 Views
Last Modified: 2013-01-18
Need to list Provider Key IDsSo I have a form that I need to list Provider Key IDs in a combo box, preferrably.

I have been trying to get it to display the list of IDs but don't know what works?
0
Comment
Question by:IEHP1
  • 13
  • 8
21 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38790005
set the rowsource of the combo box with something like this

select distinct ID from TableName


what is the rowsource you are using?

if you have more than 1 column, you can set the
column count
bound column
column witdhs
0
 

Author Comment

by:IEHP1
ID: 38793726
So I set a SELECT Statement in the Row Source property for that combo box, but shows up blank?

Form Question Row Source
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38793748
if you run the select statement that you used,
do you see any results?

upload a copy of your db
0
 

Author Comment

by:IEHP1
ID: 38793815
oh, nevermind that, I had a Form Criteria on the query....I got my Provider's full name and keyid in there separated by a pipe (|) symbol.

Now I have to work on the form working together
0
 

Author Comment

by:IEHP1
ID: 38793839
give me a second with the sample database got to put in some sample data to show you.

Thank you so much.
0
 

Author Comment

by:IEHP1
ID: 38794143
So I have whipped up this sample database real quick and I know the form filters don't work right now (I am not a VB guy, but hope to learn as I go along).

This database sample has been formulated from this link I found while searching how to build the form the way I would like it to be built:  http://allenbrowne.com/ser-62.html

You can download his sample database from there and see how he does it. The query I saw in that sample database on his site looked cumbersome and like it says in the page, maybe it is not best to use that method if you are using a query instead of a table.......

Please explain to me how you go about modifying the VBA code (so I can learn) to get the form to do what I would like it to do.
Form-Building-Using-Filters.accdb
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38794173
can you explain in plain english, what you want to happen..

your combo now is displaying the following

Provider
KENNY SMITH|2222
KEVIN DURANT|8888
KOBE BRYANT|5555
LEBRON JAMES|4444
MICHAEL JORDAN|6666
RUSSELL WESTBROOK|9999
SCOTTIE PIPPEN|7777
SHAQUILLE O'NEAL|1111

1.what is wrong with this ?

2.are you going to use the combo box to locate/filter records in the form?
0
 

Author Comment

by:IEHP1
ID: 38794351
Yes, So in plain english,

user A wants to find Provider Kenny Smith's information (address info, phone, fax, etc.). He/she knows the name of the Provider "Kenny Smith" and so he/she types in Kenny and the Provider combo box brings up the record for Kenny Smith (one or more Provider Key IDs)--this is already taken care of as you can probably see.

the sample db I provided is doing what it is suppose to be doing in the Provider combo box.

I need your help in getting the rest of the form to work.

Scenario 1: So user A types in Kenny, brings up record(s), he/she selects one of them and he/she then clicks the filter button (so that the bottom portion fills in all of the information for that specific provider key id).

Scenario 2: Let's just say user B only knows the last name of the Provider he/she needs to see their address info for. So user B types in the last name in the top portion text box last name and clicks the filter button (so that the bottom portion fills in all of the information for a provider or more than 1 providers that all have the same last name).

Scenario 3: Let's say user C just wants to see all of the Providers in the City of San Bernardino. So user C types in San Bernardino in the top portion text box City and clicks the filter button ((so that the bottom portion fills in all of the information for all providers that all are located in the City of San Bernardino).

The filter button with the red X would clear the top portion\Search Criteria section.

Do you get a better idea of where I would like to go with this? Don't hesitate to ask me more questions if you have them.....

And once again, Thank you so much for helping me with this in advance
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38794431
0
 

Author Comment

by:IEHP1
ID: 38794758
Very awesome capricorn1!!!!! I am super pleased. I have gained some insight and will look at the vb code to see what changes you made and that will help me a little with vb I think.

Except for a couple of things I just noticed:

1. When I select the 1 record from the Provider KeyID combo box and select filter, it says No Criteria selected (or something like that). Can we get it to filter the records based on the Provider KeyID combo box alone as well?

2. Not that it is too big of a deal to me, but that IIf statement in the form footer? Can we disguise it "behind the scenes"?
Form-Filter-IIf-Statement-in-For.JPG
Form-Filter-Form-View-Form-Foote.JPG
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:IEHP1
ID: 38794761
oh, sorry I should have embedded the pictures!
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38794781
i asked you that question at http:#a38794173  see question 2
0
 

Author Comment

by:IEHP1
ID: 38794883
Yes, maybe we weren't on the same page when I replied,

"Scenario 1: So user A types in Kenny, brings up record(s), he/she selects one of them and he/she then clicks the filter button (so that the bottom portion fills in all of the information for that specific provider key id)."

I think you thought types in Kenny in the first name column (which is true the way you designed it)

but if we can extend that to the combo box that lists all of the Provider Key IDs, that would be awesome!!!
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 38794956
0
 

Author Comment

by:IEHP1
ID: 38794997
Why doesn't the query pick up Charles Barkley?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38795048
you don't have zip code 93231 (zip5 in table rv_prov_addinfo)

in your zipcode_lu table
0
 

Author Comment

by:IEHP1
ID: 38795075
oh i saw why nevermind that the link to the zip code lookup table was wrong sorry about that
0
 

Author Comment

by:IEHP1
ID: 38795081
so how did you make the form footer invisible. I still see the Visible property turned on (Yes)??
0
 

Author Comment

by:IEHP1
ID: 38795089
So when I input San Bernardino into the County field, it returned this error?

Compile error
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38795093
is your problem with the combo box and filters solved?

if it is please close this thread.

the q is to address specific problem, not the entire design of a form or the app.

the normal rule is one question per thread..
0
 

Author Comment

by:IEHP1
ID: 38795101
i see ok i will post a new thread now please respond i will post it now and you get the points
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

708 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

18 Experts available now in Live!

Get 1:1 Help Now