Solved

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

Posted on 2013-01-17
21
561 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

910 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

22 Experts available now in Live!

Get 1:1 Help Now