Combo/List boxes empty on Access 2000 form 1

[Scanner]

OK, this is driving me nuts.

I have created a simple form to add/modify an "employee" table in my database. One of the fields in the employee table is their department. I've also created a department table and populated it with all of the department titles.

I want to use a combo or list box on the employee form showing the data from the department table and use that to assign a department to each new hire.

Simple enough, right?

I've gone through the wizards and although the data from the department table is listed while in the wizard, once the field is actually on the form all data disappears. Both the List box and the Combo box are empty.

The more I try the more frustrated I become.

 

[maxhugen]

In form design view, go to the query in the Row Source of the problem combo. Open the query, and switch to datasheet view, to ensure you're returning records.

Max Hugen
Australia

 

[PHV]

Provided the relationships are well defined the combo wizard should do the job correctly.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Scanner]

Sorry I haven't been able to reply before now. Just an update... it still doesn't work.

maxhugen: I followed your advice and the datasheet view shows all of the fields and data. It's only when I activate the control on the form when it only shows the value of the first column.

PHV: I've tried setting the relationship every way imaginable. I've also manipulated the join type, tried all of the Referential Integrity settings and even removed the relationship completely. The wizard DOES show the values that I want in the screen prior to "finishing" the wizard. That's the confusing part, AFTER the wizard is completed, the data in the second column is blank when I click the dropdown arrow (the data in the first column is there).


To both: Thanks for your help, but I'm still going gray trying to figure this out. Any more ideas?

 

[fneily]

During the creation with the wizard, did you uncheck Hide Key?
For your combobox, what does your Column Count and Column Widths say?
Or you can manually create the rowsource. Your rowsource should look something like:
Select Distinct [DepartmentName] From [tblDepartment] Order By [DepartmentName];

 

[maxhugen]

it only shows the value of the first column

It sounds like the Column Count property hasn't been set correctly. If you have 2 fields in the combo, and you want them both to display in the dropdown list, set Column Count to 2, and also set Column Widths to something like 1";3" (or 2.5cm;7.5cm if metric).

I note the combo sets the Dept; if your fields are DeptID and DeptName (for example), then assuming you're saving the value of DeptID, but want users need to see DeptName, you can set Column Widths to 0";3" (or 0cm;7.5cm). By setting the first column width to zero, it's there, but doesn't display.

Also make sure the Bound Column is correctly set; in this example it would be 1 (DeptID).

Max Hugen
Australia

 

[TheAceMan1]

How are ya scanner . . .

A birds eye view would excelerate resolution here. Post the following [blue]properties[/blue] of the combobox:

[ol][li][blue]Row Source Type[/blue][/li]
[li][blue]Row Source[/blue]. If query name, post the SQL of the query.[/li]
[li][blue]Column Count[/blue][/li]
[li][blue]Column Widths[/blue][/li]
[li][blue]List Width[/blue][/li]
[li][blue]Width[/blue][/li][/ol]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Scanner]

Sorry I haven't been back in a while. I've been having family issues that have been taking me away from work.

Just an FYI...

I figured out my problem. It seems that Access doesn't like it when you are using formatting when doing combo/list boxes the way I've been using them. Here's the synopsis:

Tables/fields involved -

tblEmployees
DeptCode

tblDepts
DeptCode
DeptTitle

Relationships:
tblDepts.DeptCode (1)<--->(many) tblEmployees.DeptCode

There was nothing wrong with my Employee table or the way in which I had been creating my combo and list boxes.

The list box within tblEmployees was, and still is, designed to show two fields to the operator for selection: DeptCode and DeptTitle.

The DeptCode field within tblDepts was constructed with forcing all upper-case using ">". My company uses the DeptCode in a lot of things and wants to keep it that way for readability.

The tblEmployees.DeptCode list box displays no records until I remove the ">" from the Format property in tblDepts.DeptCode.

Once the property is removed...voila...the box is populated as expected and when selected the correct DeptCode is stored in tblEmployees.

Thanks again for everyone's help on this one.

Scanner

 

[PHV]

I guess you're playing with ac2003 SP3 ...

 

[Scanner]

Yup...you guessed it. I take it that other builds/service packs don't have this issue?