ComoBox Does Not Display Bound Record

[gray]

Hi All

Access 2002/2007
WinXPPro


Anyone seen this before?

I have a form with a subform. On the subform is a Combobox. The Rowsource and ControlSource of the ComboBox is edited from a button's proc in the main form.

1. Row Source Type is always Table/Query
2. Rowsource is set to a table e.g. My_Table1 with autonum ID
3. Controlsource is set to a field from the subform's recordsource e.g. My_Table2.My_Table1_ID
4. I requery the Combobox each time.
5. listrows = 10

This all works happily and the ComboBox scrolls to the correct record in the relevant table which can be seen in first row if one clicks the drop-down control....

What it does not do, however, is to display the record in the display portion of the combo...

By default my subform is in datasheet view.... if I flick it into form view the combobox displays the record correctly and continues to do so if I then flick back to datasheet view........??

I've re-imported the form & re-created the combobox but no joy...

derrr.....

thanks

 

[boblarson]

Did you make sure to change the combo's Column Count property to the correct number of fields from the query?

And then the Column Widths property set to display the columns you want?

 

[boblarson]

Oh, and I was wondering - does the rowsource pull from the same table as the main form?

 

[gray]

Hi Bob

Yes, I have my columncount specified correctly and my listwidth = Auto....

When the main form button is pressed, yes, it first alters the recordsource for the subform, sets the rowsource and controlsource for the combobox then requeries the subform, then requeries the combobox... but the subform recordsource-table is never equal to the combobox rowsource-table....

I might reset the listwidths to an actual value....

 

[boblarson]

Hi Bob

Yes, I have my columncount specified correctly and my listwidth = Auto....

Read again - I didn't say listwidth I said COLUMN WIDTHS. There is a BIG difference.

But anyway, I think I would have a better understanding of what's happening if I could actually play with a copy of the database (with bogus data of course).

 

[gray]

Apologies Bob.. I should have read more carefully...I think I was wondering subconciously about what listwidths=auto did .. as it happens setting it to a value did not cure the problem.

My column counts are also set correctly... and my bound column is too ... it's all very odd... as I say the combobox behaves itself perfectly when it's host form is switched to form view....

I'll try to extract the relevant parts into a dummy Db... thanks again

 

[smig]

I think it's one more hit for the known problem of changing the rowsource of a combo in a continuous form.

if you cahnge the rousource of a combo to look in a different table/query it will chnage for all combo in a continuous form. this will cause combo that don't have a matching key to the new recordsource to show a blank data.

if this is the case: it was discussed before on these forums.

 

[gray]

Hi Smig

Thanks for your reply. I did a quick check... my main and subforms have a default view of Form and Datasheet respectively. The buttons I have only switch the forms between these two modes.

Actually, having done a little more analysis I think what is happening is that the comboboxes are showing 'ghost' records. When switching rowsources, the combos display entries from the old table but using the numbers from the new table... so for example:-

1. Rowsource = "Addresses" - Corresponding ID to display from the bound column is, say, 21. The combo (after switching into form view) correctly displays 21 from Addresses table.

2. Reset Rowsource = "Contacts" - Corresponding ID from the bound column is, say, 56 but the lookup is being done on the Addresses table rather than Contacts... so if 56 exists in the Addresses table its value is displayed in the combo. If ID 56 does not exist in table Addresses then the combo is "blank".

This happens despite doing requeries on the combo and its host form. Further, I placed a button to msgbox the combo's rowsource and guess what? it is correctly returned as the current rowsource and current ID - in my example.. ID 56 and Contacts table.... when the drop-down button is clicked the first entry is correctly displayed too.... it's just the 'display' portion of the combo (i.e. when drop-down button is not clicked)

As I mentioned the only way to kickstart the combo to display the correct value is to switch it between views... and I think this is because it forces a re-load of the subform and all its controls...

Another Arthur C. Clarke mystery of the Universe....

 

[vbaInet]

I don't think this is a bug of any sort. I believe it has something to do with your code and you not setting the Column Count and Column Width properties according in code. Like Bob asked, did you set those properties in code after you changed the rowsource property in the click event of the button? The bound column must be the first column that appears in the sql statement.

It would be much easier to diagnose if a stripped down version of your db was posted as already requested.

 

[DCrake]

VBINet
Any column in a combo box or list box can be the bound column, and it does not have to be hidden, but good practice staes that it is better to be the first column as Access tries to predict and hide the bound column. As long as you can pass the relevant properties to the combo box along with the correct bound column number then everything should be ok. Obviously something has gone amiss.

 

[vbaInet]

VBINet
Any column in a combo box or list box can be the bound column, and it does not have to be hidden, but good practice staes that it is better to be the first column as Access tries to predict and hide the bound column. As long as you can pass the relevant properties to the combo box along with the correct bound column number then everything should be ok. Obviously something has gone amiss.

I'm aware of that. In the case of the OP I think he's only binding the first column which was the context in which I was speaking.

 

[gray]

Hi Chaps

Actually, I have pared down a Db in which I can demonstrate the issue... There is no live data in but I still wasn't sure of the etiquette... that is, should I load it up here (approx 370k) or to a private email address? I dropped Bob a PMsg to ask but I guess he has, very sensibly, taken the weekend off

I think the column count, bound column, column widths and listrows etc are all OK because when the combobox drop-down button is pressed it shows the correct table and scrolls to the appropriate record ... it's just the "display" portion (sorry I don't know the correct term for that)..

If I understand you correctly, are you saying it is best practice to reset the column count,bound col, column widths and listrows immediately after changing the rowsource?

thanks

 

[vbaInet]

Actually, I have pared down a Db in which I can demonstrate the issue... There is no live data in but I still wasn't sure of the etiquette... that is, should I load it up here (approx 370k) or to a private email address? I dropped Bob a PMsg to ask but I guess he has, very sensibly, taken the weekend off

You can post it here. Here's how:

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

If I understand you correctly, are you saying it is best practice to reset the column count,bound col, column widths and listrows immediately after changing the rowsource?

Not necessarily, unless a different bound column is needed or you want to see a certain column.

 

[gray]

Thanks VBA1NET- Database attached.... instructions to re-create issue as follows:-

1. Open db
2. Set option to allow code to run
3. Open Address_Details_Form
4. Scroll to record 4, "TestAddress(115_1)" (unique_no = 115)
5. Click Assoc Contacts tab and examine subform entries
6. Click Assoc Orgs tab and examine subform entries
7. Click Utilisations tab - the utilisations tab is intended to be a housekeeping utility whereby admins can see where any given record is in use.
8. In the main form comboboxm scroll to:-
"Utilisations in Item Type ~ Address to Contact ~ This Address Only"
and click retrieve button. Notice the Contacts combobox on the subform
9. Scroll to:-
"Utilisations in Item Type ~ Address to Organisation ~ This Address Only"
in the main form combobox and click retrieve button again. Notice that the combobox on the subform is now entitled "Organisations". Examine the entries in that combobox.
10. By Clicking in the subform and clicking the View button you can switch the subform to and from datasheet view.
11. Notice the that the results in the Contacts / Organisations combobox vary...

cheers

P.S. The subform's recordsource and combo's rowsource are built in the "Select_Utilisations_Button_Click"

 

[vbaInet]

That was a rather long set of instructions. Short and sweet is what we like

10. By Clicking in the subform and clicking the View button you can switch the subform to and from datasheet view.

It's not the subform that goes into datasheet view, it's the main form.

From your explanations I still don't get what or where the problem is? The combo box seems fine to me. Are you talking about after clicking the button the subform doesn't show the correct records?

 

[boblarson]

I'm a bit lost on this too. Perhaps you should create a set of screenshots where you can actually point out what you are seeing. If you paste a screenshot into Word you can use the tools on the Drawing toolbar to circle and stuff (if you don't have a regular screen capture tool like SnagIt or even the SnippingTool from Windows Vista or Win7.

 

[gray]

Hi

Apologies for the late reply... have been a bit tied up today...

I've attached a few screenshots to illustrate the problem... plse view in Screen1-Screen5 sequence...

Just a quick note on the "View" button... if you click in one of the subforms first, the View button will switch the subform between Form and Datasheet view (it uses the previous control property to decide which form to switch).

Thanks for your assistance.....

 

[vbaInet]

In the attached db there is no test data that we could test it on in order to see what's happening.

Also, when you've finished setting the Record Source of your subform, you need to Requery it too.

 

[boblarson]

Also, when you've finished setting the Record Source of your subform, you need to Requery it too.

Nope, not really. When you set a new record source for a form (including subform) it AUTOMATICALLY will requery - no need to requery it again.

 

[vbaInet]

Nope, not really. When you set a new record source for a form (including subform) it AUTOMATICALLY will requery - no need to requery it again.

Yep, so it should happen, but I've seen it fail before. A perfectly normal form but won't requery unless forced to.