ComboBox dropdownlist index - How do I get the value?

Posted on 2000-04-07
Medium Priority
Last Modified: 2012-05-04
This is my problem:

I have a form with a subform on it.
When I put a combobox on my subform, I can no longer select an item from the list.

If anybody knows about this problem and knows how to solve it, please tell me, but I'm guessing this is a functionality that is not supported by MS-Access.

Next step is to find a way to work around this problem.

I know how to change the value of my ComboBox, how to change the dropdownlist displayed and how to get the value for a record in the dropdownlist through it's indexnumber.

What I don't know is how to get the indexnumber for the record selected in the dropdownlist. I've checked the access-helpfile and can't find a property that contains the indexnumber.

I think access stores this into some temporary variable somewhere, but what's the name of this variable???
Question by:GOLLEM
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
  • 6
  • 5
LVL 54

Expert Comment

ID: 2692645
Make sure the box is "bound" to a field in the recordset. When necessary add a dummy field to the query to get it bound.
You can loop through the combo entries and when .selected is true your loop count has the indexnumber. (from 0 to count-1 !)
I would advise however to (invisible) store a unique key in the combobox and use that. AfterUpdate just take the column from the actual value and continue.

Author Comment

ID: 2692693

found the real problem...

I was changing the sourceobject property of my subform during runtime.

This is not possible it seems. it does work when the subform is bound.

So now I simply do the following:

turn off the echo
open the main form in design view
change the property
open the form in form view
turn the echo back on

this works :)

sorry to have bothered you, but I had been searching for the problem all day yesterday...



Author Comment

ID: 2692694
This question has a deletion request Pending
Technology Partners: 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!

LVL 54

Expert Comment

ID: 2692737
This question no longer is pending deletion
LVL 54

Expert Comment

ID: 2692738
Isn't it possible to get it bound dynamically by also filling the main/sub linkfields and/or do a refresh or requery on the main form ?

Please don't delete the question. Let EE reset the points to zero and have it stored, so others with the same problem can find the solution you found !

Author Comment

ID: 2692786

the subform isn't linked to the main form, so that can't be the problem.

tried the refresh also (first thing I did when I discovered what the real problem was)

I think the developers just didn't anticipate someone trying to do this.

By the way, I'm thinking of writing an add-in that serves as a fast and easy database user infterface builder.

The project I'm currently involved in has a low time-priority, so I am able to put a lot of time into standardising the design.

Any suggestions on what features would be helpfull will be greatly appreciated.
(Of course the add-in will be distributed as freeware)

So far I've already thought of dynamic query, form or report design from a form and I have a lot of extra functionality to make life a bit easier.

LVL 54

Expert Comment

ID: 2693009
Think I know why the combo isn't working:
The recordset you are using is probably read-only !
Also the binding trick isn't working then. Obvious, there is nothing you can do with the result when you can't store it!
Just check and let me know if I'm right!

A different approach to show different "subforms" can be the use of a tabbed control. Gives also more space and the selection criterium is the name of the tab.

The dynamic form/report builder you are thinking of is already more or less available in the form of the Access wizards. But by manipulating the form- or report-object you can always write your own add-in. (Tip: Make a .mdb and use "Make .mde file"option. The result can be added as add-in)
I would like to have the possibility to separate the form fields and the layout and steering (buttons) functionality. A bit like the different templates you can use within FrontPage. Without rewriting every thing you can give the web-site a totally different look but the functionality remains the same!
However, it's my experience that after thinking of this type of possibilities often the functionality (often even more) is offered in a next product release or by a new add-in from the web!


Author Comment

ID: 2693084

I realised the problem couldn't be the combo, because i couldn't type anything into it.
I couldn't even get that done with an ordinary textbox.
It's not about the recordset, but about some kind of pointer MS-access uses to indicate which form the subform is pointing to. This pointer is probably only updated when you open a form.

I had another weird problem though changing the subform though. When using a pop-up form for the main which is as large as the screen part of your main form becomes invisible, quite weird.. :)

So now I also actually close the form in between.

On your comment on steering buttons:

What buttons do you deem best???
I now have a list of buttons at the bottom of the main form.

The first is called Search Options and leads to the query section, in which it is possible to open queries in a pop-up, build queries or assign them to different subforms (the subforms are menuchoices of course)

The second is  called Post / Refresh and is used for saving the record you have been changing

The third is called summaries and is used for reports

The fourth is called Print Options and is used for different kinds of printing: e.g. full screen, subform, query or report
There are four pop-up forms displaying queries or forms you selected. These pop-ups are of course resizable and can be closed. You can ask to print any query or report you have opened in one of them

The last button is called Show Pop-ups and is used because the pop-ups disappear behind the main form when you select the main form.

There is no button for designing forms, because this is bes placed under a menu-option called Maintenance (or something like that.

I don't like distributing in MDE, because I think Open-Source is the way to go.

I'll leave different layouts up to someone else who is better at that. I just provide the functionality.


LVL 54

Accepted Solution

nico5038 earned 80 total points
ID: 2693221
It's becoming quite a story this question!

1) The subform issue:
Think it has to do with the forms "active" and those still stored.
When you switch subforms I guess you'll have to "load" it first (invisible) to get it available. (when using the "build" form Access also makes a distinction between currently loaded and not!)
Just give it a try!

2) The Wizard issue:
I use normally two types of forms:
a) A general selection form with all occurrences of a table/query. On this form a user can use a button to specify the basic operation (Add, change, view or delete) on that object.
b) A record oriented add/change/view or delete form. Basically the same form only different for key-fields (only editable on add-form) and detail-fields (editable on add-form and change form)
The view and delete form only differ for the delete button. (when deleting the user should be able to verify the information that's the correct entry he's deleting!)

On the general selection form the occurrences are displayed in a datasheet type of subform. Thus giving the user the right-click filter and sort possibilities. (It takes some explanation to the user, but you will be amazed how they like this flexibility!)
As a user convienience I add a field showing the active filter and sort. When printing a report, this filter is passed to the report (and printed at the bottom line).

When making an add-in, you should separate the layout and the functionality. This can be done by storing the actual screen information in a table and use standardized names for the template objects. Thus you can loop through all form objects and change their properties to those wanted.
The template objects and their properties should also be placed in a table. Preferably this table should be build from (a set of) standard forms that are forming the template.
The standard switchboard Access uses when creating a new sample database (like addresses) can be seen as an example!

OK, enough for today. Hope it triggers some fruitfull thoughts and idea's!


Author Comment

ID: 2693522

Japanese saying:

looking at a spiders web one often forgets the spider...

In my case this was also the question :)
I was looking for an answer in code, while the whole thing I did wrong was that I locked the subform by accident.

I am a bit embarrassed..... *LOL*

I've already stored all functionality in global procedures and standardised my controls (I use the strategy of step by step design, first bound fields, then unbound fields, etcetera and store the amount for each in a table) so that's no problem.

the reason I link my queries to screens is that this is helpfull. The user doesn't want to be presented queries that have nothing to do with the screen he/she is in, so it makes sense that the users are only able to call or change queries that relate to the screen (and make new related queries of course)

For forms this is not the case, so thats why there is no button for them. i am thinking of making a Form Maintenance button and linking the forms to menu-choices. This way you could make a flexible menu on the left side of the screen that displays all possibilities. I'm not sure how I'm going to do this yet..

As a thank you for your comments I'm going to give you the points anyway :)

Kind regards,

LVL 54

Expert Comment

ID: 2693644
Looks interesting!

Just keep me informed. (See profile for address)
Would like to give a sample .mdb a test/review.

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

801 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