Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-01-17
21
Medium Priority
?
615 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 8
21 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

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

upload a copy of your db
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
 

Author Comment

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

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 38794956
0
 

Author Comment

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

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

719 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