ComboBox dropdownlist index - How do I get the value?

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???
Who is Participating?
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!

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.
GOLLEMAuthor Commented:

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...


Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

GOLLEMAuthor Commented:
This question has a deletion request Pending
This question no longer is pending deletion
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 !
GOLLEMAuthor Commented:

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.

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!

GOLLEMAuthor Commented:

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.


GOLLEMAuthor Commented:

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,

Looks interesting!

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

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.