• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 623
  • Last Modified:

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

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
IEHP1
Asked:
IEHP1
  • 13
  • 8
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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
 
IEHP1Author Commented:
So I set a SELECT Statement in the Row Source property for that combo box, but shows up blank?

Form Question Row Source
0
 
Rey Obrero (Capricorn1)Commented:
if you run the select statement that you used,
do you see any results?

upload a copy of your db
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
IEHP1Author Commented:
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
 
IEHP1Author Commented:
give me a second with the sample database got to put in some sample data to show you.

Thank you so much.
0
 
IEHP1Author Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
IEHP1Author Commented:
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
 
Rey Obrero (Capricorn1)Commented:
0
 
IEHP1Author Commented:
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
 
IEHP1Author Commented:
oh, sorry I should have embedded the pictures!
0
 
Rey Obrero (Capricorn1)Commented:
i asked you that question at http:#a38794173  see question 2
0
 
IEHP1Author Commented:
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
 
Rey Obrero (Capricorn1)Commented:
0
 
IEHP1Author Commented:
Why doesn't the query pick up Charles Barkley?
0
 
Rey Obrero (Capricorn1)Commented:
you don't have zip code 93231 (zip5 in table rv_prov_addinfo)

in your zipcode_lu table
0
 
IEHP1Author Commented:
oh i saw why nevermind that the link to the zip code lookup table was wrong sorry about that
0
 
IEHP1Author Commented:
so how did you make the form footer invisible. I still see the Visible property turned on (Yes)??
0
 
IEHP1Author Commented:
So when I input San Bernardino into the County field, it returned this error?

Compile error
0
 
Rey Obrero (Capricorn1)Commented:
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
 
IEHP1Author Commented:
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

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 13
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now