Combobox not displaying initial value

[collizz]

Hi

Hopefully, this is a basic problem that an experienced Access programmer has come across. I have a technical background, but haven't used Access for a while. Sorry this is a tad long, but I've tried to provide as much detail as possible.

I have a simple "new record" form that includes two comboboxes: cboGroup and cboOwner.

Both are populated via select queries, that return more than one row of two columns:

qryListGroup - SELECT subCode, description FROM tblCodeDecode WHERE categoryCode = "GRP". This returns an alphanumeric code and name, e.g. "M", "Master Group". Note that the PK for tblCodDecode is categoryCode + subCode (categoryCode = "GRP").

qryListOwner - SELECT ownerID, ownerName FROM tblOwner. This returns an integer ID and owner name, e.g. 13, "John Smith". PK is ownerID.

The combo properties are identical, other than their row source (qryListGroup, qryListOwner). The Column Count for both is set to 2, Column Widths is set to 0cm;3cm, and Bound Column is set to 1.

In the form's Load event, I set their default values:
Me.cboGroup.DefaultValue = Me.cboGroup.ItemData(0)
Me.cboOwner.DefaultValue = Me.cboOwner.ItemData(0)
before setting focus on the first modifiable field (msgbox displays show these settings are OK).

My problem is that cboGroup shows no default value when the form is displayed, although the rows have been loaded and are loaded into the combo control when the dropdown arrow is clicked.

Meanwhile, cboOwner has all rows loaded, the default value is loaded into the control and the default value is shown as being "active" with a reverse highlight (annoying, but a fix for another day).

Apart from the fact that the first value in cboGroup is alpha (not numeric, as in cboOwner) I cannot for the life of me figure out what is wrong.

I have a nagging thought that it may have something to do with the fact that qryListGroup runs against a table with a 2-part key of categoryCode + subCode, the field subCode being the first column retrieved in the qryGroup, along with the group name in tblCodeDecode.description.

Any suggestions will be gratefully received.

 

[vbaInet]

When the form loads, the combo boxes don't get selected and hence ItemData() will have no value. Just remove .ItemData(0) and use .Value or .Column(0)

 

[collizz]

Thanks, vbaINet, but as I mentioned in my post, I loaded both comboboxes and checked out their contents with a msgbox display. Both ItemData(0) values were non-blank, with the first expected value inplace. They just don't both show up when the form is presented!

 

[vbaInet]

Ok, I just skimmed through your post. But ItemData() is used in the wrong context.

The DefaultValue property will only show up when a new record is created. Do you open the form in Add mode? Plus does the value you're setting it to correspond to the bound column?

 

[collizz]

I got the ItemData() usage from a Google search, and it does load the values (I display them immediately following the load in the Load event).
The form is normally opened as acFormEdit (from a menu) but I have changed it a fair bit and now I'm testing it "standalone", so I don't know how it "thinks" it's being run.
The cbo shows the correct "description" values, with the "code" field as the bound column.
It's just weird how one cbo works fine and the other doesn't.
I'm still playing around with it, but please come back if you can shed any more light!

 

[vbaInet]

Yes ItemData() will work but like I mentioned it's used in the wrong context. ItemData is normally used for multi select listboxes so you're better off using a more direct method such as those members I already mentioned.

If you open the form in Edit mode, the Default value will not fire. It will only fire if opened in acFormAdd mode.

 

[collizz]

I just created tblTest, with two columns testID (autonum) and testText, populated a few rows, created a query to select both columns, changed the control's Row Source to "qryTest" and it works fine.
Then, I changed the tblTest key field to be text, changed the key values from 1,2,3 to "A","B","C" and tried again. It failed to load the data in the control, as before.
Can't be a number thing, can it?

 

[collizz]

Linked it back in to the calling menu, opened it in Add mode and still no success, unless I used a numeric key in the source table. Going to lunch!
Thanks

 

[vbaInet]

When you say it failed to load the data, what do you mean? Regardless of whatever datatype you've made your columns your combo box will display the data as long as the Column is visible with the right settings of the Column Count and Column Width properties.

 

[collizz]

The table data is retrieved as expected by both queries and is available to the combo boxes (you can the data when you click on the dropdown arrow). The difference is that in one case, the combo is provided an intial value that is shown in the control. The other combo has nothing loaded into the control, although it can be viewed and loaded into the control, when you click on a row of data.
The combos are defined the same (I cut and pasted), apart than their source query, as I explained before.
Just changing the data type of the source table (using tblTest) made a difference, without changing anything else! The data type of the bound column does seem to make a difference.
I do appreciate you taking the time to slog through this. Any other ideas?

 

[vbaInet]

I would get a clearer picture if I can see the form. Can we see a stripped down version of your db?

 

[collizz]

OK, all is now well.

I removed ".DefaultValue" from the initialization process for the combo boxes and everything now loads properly.

I would still like to know why the heck it had problems with alphabetic key values, but you know, I'm going to pretend it never happened!

Thanks so much for your help and patience.

 

[collizz]

One last question - how do I close this thread ??

 

[vbaInet]

Glad to hear you had that resolved.

There's no closing of threads on this forum.