By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,720 Members | 2,135 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,720 IT Pros & Developers. It's quick & easy.

Can't see combobox values in table lookup

P: n/a
I have a table with a field that uses a combobox to populate values. The
Lookup tab within table design mode is the following:

Display Control Combo Box
Row Source Type Table/Query
Row Source SELECT [Employee Linked].LastName, [Employee
Linked].FirstName, [Employee Linked].EmployeeNumber FROM [Employee Linked];
Bound Column 3
Column Count 3
Column Heads Yes
Column Widths 1";1";1"
List Rows 8
List Width 3"
Limit To List Yes

When I run that query on its own I get results. But when I open the table
and click on the EmployeeID field I can see the drop down combobox and the
column headings but no data being populated. What could be causing this?

Thanks

Mark
Jul 15 '08 #1
Share this Question
Share on Google+
14 Replies


P: n/a
On Tue, 15 Jul 2008 13:58:50 -0400, Mark wrote:
I have a table with a field that uses a combobox to populate values. The
Lookup tab within table design mode is the following:

Display Control Combo Box
Row Source Type Table/Query
Row Source SELECT [Employee Linked].LastName, [Employee
Linked].FirstName, [Employee Linked].EmployeeNumber FROM [Employee Linked];
Bound Column 3
Column Count 3
Column Heads Yes
Column Widths 1";1";1"
List Rows 8
List Width 3"
Limit To List Yes

When I run that query on its own I get results. But when I open the table
and click on the EmployeeID field I can see the drop down combobox and the
column headings but no data being populated. What could be causing this?

Thanks

Mark
Always include your Access and Windows Version numbers. It may make a
difference.

Are you using Access 2003 with Service Pack 3 installed?
There is a bug.
see...
http://support.microsoft.com/kb/945674
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Jul 15 '08 #2

P: n/a
Mark wrote:
I have a table with a field that uses a combobox to populate values. The
Lookup tab within table design mode is the following:

Display Control Combo Box
Row Source Type Table/Query
Row Source SELECT [Employee Linked].LastName, [Employee
Linked].FirstName, [Employee Linked].EmployeeNumber FROM [Employee Linked];
Bound Column 3
Column Count 3
Column Heads Yes
Column Widths 1";1";1"
List Rows 8
List Width 3"
Limit To List Yes

When I run that query on its own I get results. But when I open the table
and click on the EmployeeID field I can see the drop down combobox and the
column headings but no data being populated. What could be causing this?

Thanks

Mark

Have you considered using a form instead? If you need direct editing
and not rules based data entry, create a query instead that links to the
data table. Get rid of the lookup.
Jul 15 '08 #3

P: n/a
I agree with Fred; this sounds like another manifestation of the dreaded SP3
bug monster. Follow his link to the hotfix and install it.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200807/1

Jul 15 '08 #4

P: n/a
Fred,

I am running Access 2003 SP3 with Win XP Pro SP2.

Also the SQL noted below is getting its data from an Excel spreadsheet that
I have linked. Could that be causing an issue as well?

Thanks

Mark

"fredg" <fg******@example.invalidwrote in message
news:1w*****************************@40tude.net...
On Tue, 15 Jul 2008 13:58:50 -0400, Mark wrote:
>I have a table with a field that uses a combobox to populate values. The
Lookup tab within table design mode is the following:

Display Control Combo Box
Row Source Type Table/Query
Row Source SELECT [Employee Linked].LastName, [Employee
Linked].FirstName, [Employee Linked].EmployeeNumber FROM [Employee
Linked];
Bound Column 3
Column Count 3
Column Heads Yes
Column Widths 1";1";1"
List Rows 8
List Width 3"
Limit To List Yes

When I run that query on its own I get results. But when I open the table
and click on the EmployeeID field I can see the drop down combobox and
the
column headings but no data being populated. What could be causing this?

Thanks

Mark

Always include your Access and Windows Version numbers. It may make a
difference.

Are you using Access 2003 with Service Pack 3 installed?
There is a bug.
see...
http://support.microsoft.com/kb/945674
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Jul 16 '08 #5

P: n/a
On Wed, 16 Jul 2008 00:24:00 -0400, Mark wrote:
Fred,

I am running Access 2003 SP3 with Win XP Pro SP2.

Also the SQL noted below is getting its data from an Excel spreadsheet that
I have linked. Could that be causing an issue as well?

Thanks

Mark

"fredg" <fg******@example.invalidwrote in message
news:1w*****************************@40tude.net...
>On Tue, 15 Jul 2008 13:58:50 -0400, Mark wrote:
>>I have a table with a field that uses a combobox to populate values. The
Lookup tab within table design mode is the following:

Display Control Combo Box
Row Source Type Table/Query
Row Source SELECT [Employee Linked].LastName, [Employee
Linked].FirstName, [Employee Linked].EmployeeNumber FROM [Employee
Linked];
Bound Column 3
Column Count 3
Column Heads Yes
Column Widths 1";1";1"
List Rows 8
List Width 3"
Limit To List Yes

When I run that query on its own I get results. But when I open the table
and click on the EmployeeID field I can see the drop down combobox and
the
column headings but no data being populated. What could be causing this?

Thanks

Mark

Always include your Access and Windows Version numbers. It may make a
difference.

Are you using Access 2003 with Service Pack 3 installed?
There is a bug.
see...
http://support.microsoft.com/kb/945674
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Did you not read the knowledgebase article?

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Jul 16 '08 #6

P: n/a
On Jul 16, 10:25*am, fredg <fgutk...@example.invalidwrote:
On Wed, 16 Jul 2008 00:24:00 -0400, Mark wrote:
Fred,
I am running Access 2003 SP3 with Win XP Pro SP2.
Also the SQL noted below is getting its data from an Excel spreadsheet that
I have linked. Could that be causing an issue as well?
Thanks
Mark
"fredg" <fgutk...@example.invalidwrote in message
news:1w*****************************@40tude.net...
On Tue, 15 Jul 2008 13:58:50 -0400, Mark wrote:
>I have a table with a field that uses a combobox to populate values. The
Lookup tab within table design mode is the following:
>Display Control * * * * * *Combo Box
Row Source Type * * * * * *Table/Query
Row Source * * * * * * * * SELECT [Employee Linked].LastName, [Employee
Linked].FirstName, [Employee Linked].EmployeeNumber FROM [Employee
Linked];
Bound Column * * * * * * * 3
Column Count * * * * * * * 3
Column Heads * * * * * * * Yes
Column Widths * * * * * * *1";1";1"
List Rows * * * * * * * * *8
List Width * * * * * * * * 3"
Limit To List * * * * * * *Yes
>When I run that query on its own I get results. But when I open the table
and click on the EmployeeID field I can see the drop down combobox and
the
column headings but no data being populated. What could be causing this?
>Thanks
>Mark
Always include your Access and Windows Version numbers. It may make a
difference.
Are you using Access 2003 with Service Pack 3 installed?
There is a bug.
see...
http://support.microsoft.com/kb/945674
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Did you not read the knowledgebase article?

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail- Hide quoted text -

- Show quoted text -
Fred,

I read the article. This is not a new install and combo boxes have
worked fine with this version except in this one instance. Should I
apply the hotfix anyway? The only thing different about this combobox
is that it is part of a Lookup within the table properties. I've never
done this before.

Mark
Jul 16 '08 #7

P: n/a
On Wed, 16 Jul 2008 07:57:59 -0700 (PDT), mpdsal wrote:
On Jul 16, 10:25*am, fredg <fgutk...@example.invalidwrote:
>On Wed, 16 Jul 2008 00:24:00 -0400, Mark wrote:
>>Fred,
>>I am running Access 2003 SP3 with Win XP Pro SP2.
>>Also the SQL noted below is getting its data from an Excel spreadsheet that
I have linked. Could that be causing an issue as well?
>>Thanks
>>Mark
>>"fredg" <fgutk...@example.invalidwrote in message
news:1w*****************************@40tude.net ...
On Tue, 15 Jul 2008 13:58:50 -0400, Mark wrote:
>>>>I have a table with a field that uses a combobox to populate values. The
Lookup tab within table design mode is the following:
>>>>Display Control * * * * * *Combo Box
Row Source Type * * * * * *Table/Query
Row Source * * * * * * * * SELECT [Employee Linked].LastName, [Employee
Linked].FirstName, [Employee Linked].EmployeeNumber FROM [Employee
Linked];
Bound Column * * * * * * * 3
Column Count * * * * * * * 3
Column Heads * * * * * * * Yes
Column Widths * * * * * * *1";1";1"
List Rows * * * * * * * * *8
List Width * * * * * * * * 3"
Limit To List * * * * * * *Yes
>>>>When I run that query on its own I get results. But when I open the table
and click on the EmployeeID field I can see the drop down combobox and
the
column headings but no data being populated. What could be causing this?
>>>>Thanks
>>>>Mark
>>>Always include your Access and Windows Version numbers. It may make a
difference.
>>>Are you using Access 2003 with Service Pack 3 installed?
There is a bug.
see...
http://support.microsoft.com/kb/945674
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Did you not read the knowledgebase article?

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail- Hide quoted text -

- Show quoted text -

Fred,

I read the article. This is not a new install and combo boxes have
worked fine with this version except in this one instance. Should I
apply the hotfix anyway? The only thing different about this combobox
is that it is part of a Lookup within the table properties. I've never
done this before.

Mark
I would not use any combo box in a table.
Use it on the form you use for data entry/viewing and bind the combo
to the field in the table.
The table should just store the value of the selection you made on the
form.

However, I just linked to a spreadsheet, and added a lookup field to
the table as a test. it worked. I do not use Access 2003. I would
suggest you apply the hotfix. That should fix this immediate problem.
Then change the table field Lookup from Combo Box to Text Box and add
a combo box to the form for data entry and viewing. Make sure you get
the datatype correct.

By the way, looking at your current combo row source, how do you
intend to handle those instances where there might be more than one
employee with the same last and first names? It does happen often
enough to be of concern. How will you know which 'John Jones' is the
correct one to select from the combo?

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Jul 16 '08 #8

P: n/a
On Jul 16, 1:00*pm, fredg <fgutk...@example.invalidwrote:
On Wed, 16 Jul 2008 07:57:59 -0700 (PDT), mpdsal wrote:
On Jul 16, 10:25*am, fredg <fgutk...@example.invalidwrote:
On Wed, 16 Jul 2008 00:24:00 -0400, Mark wrote:
Fred,
>I am running Access 2003 SP3 with Win XP Pro SP2.
>Also the SQL noted below is getting its data from an Excel spreadsheet that
I have linked. Could that be causing an issue as well?
>Thanks
>Mark
>"fredg" <fgutk...@example.invalidwrote in message
news:1w*****************************@40tude.net. ..
On Tue, 15 Jul 2008 13:58:50 -0400, Mark wrote:
>>>I have a table with a field that uses a combobox to populate values.. The
Lookup tab within table design mode is the following:
>>>Display Control * * * * * *Combo Box
Row Source Type * * * * * *Table/Query
Row Source * * * * * * * * SELECT [Employee Linked]..LastName, [Employee
Linked].FirstName, [Employee Linked].EmployeeNumber FROM [Employee
Linked];
Bound Column * * * * * * * 3
Column Count * * * * * * * 3
Column Heads * * * * * * * Yes
Column Widths * * * * * * *1";1";1"
List Rows * * * * * * * * *8
List Width * * * * * * * * 3"
Limit To List * * * * * * *Yes
>>>When I run that query on its own I get results. But when I open thetable
and click on the EmployeeID field I can see the drop down combobox and
the
column headings but no data being populated. What could be causing this?
>>>Thanks
>>>Mark
>>Always include your Access and Windows Version numbers. It may make a
difference.
>>Are you using Access 2003 with Service Pack 3 installed?
There is a bug.
see...
http://support.microsoft.com/kb/945674
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Did you not read the knowledgebase article?
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail- Hide quoted text -
- Show quoted text -
Fred,
I read the article. This is not a new install and combo boxes have
worked fine with this version except in this one instance. Should I
apply the hotfix anyway? The only thing different about this combobox
is that it is part of a Lookup within the table properties. I've never
done this before.
Mark

I would not use any combo box in a table.
Use it on the form you use for data entry/viewing and bind the combo
to the field in the table.
The table should just store the value of the selection you made on the
form.

However, I just linked to a spreadsheet, and added a lookup field to
the table as a test. it worked. I do not use Access 2003. I would
suggest you apply the hotfix. That should fix this immediate problem.
Then change the table field Lookup from Combo Box to Text Box and add
a combo box to the form for data entry and viewing. Make sure you get
the datatype correct.

By the way, looking at your current combo row source, how do you
intend to handle those instances where there might be more than one
employee with the same last and first names? It does happen often
enough to be of concern. How will you know which 'John Jones' is the
correct one to select from the combo?

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail- Hide quoted text -

- Show quoted text -
The Hotfix worked. Thanks for the input. I will consider your
recommendations concerning the combobox.

Thanks

Mark
Jul 18 '08 #9

P: n/a
Access 2003 SP3 - Still Combo Box Problems after Hotfix 945674

Issue
I am using Access 2003, SP3, with Hotfix 945674 applied after reading
these postings and MS Knowledgebase etc.

I am still experiencing inconsistant problems with adding look-up values
to Forms and Tables, e.g.

Using a Look-up Table in a Combo Box, I wish to display a textual value
label in a Form, and store its associated numerical value in the
associated Table.

After days of errors, I have now tested the issue extensively with a
simple 3-value 2-column look-up table. I am unable to get Access to
*consistently* apply the look-up values as required.

The nearest that I have come to a solution is:

Using this Look-up List in a Form:

Value Value Label
-9 Absent
1 Front
2 Back

When the Bound Column for the Look-up Table attached to the Form is set
to 0:
The textual Value Label correctly displays in the Form: Absent, Front,
Back
The numerical value correctly displays in the Table: -9, 1, 2

When achieving the above results, the Data Table Look-up (if it matters)
is set to Look-up List Bound Column 0, Column Count 2.

Data can be entered into the Data Table from the Table-bound Look-up
list without problem, and is also reflected back correctly into the
Form.

This all woked fine and dandy for a few operations, after which, with
nmo obvious changes made, the Form now displays numerical values, rather
than text labels.

It seems that no matter what I do, there is some inconsistancy in Access
operation which is driving me to distraction...

Any advice or work-arounds would be most welcome - this is a
work-stopper!

Paul

*** Sent via Developersdex http://www.developersdex.com ***
Jul 28 '08 #10

P: n/a
navvyman wrote:
Access 2003 SP3 - Still Combo Box Problems after Hotfix 945674

Issue
I am using Access 2003, SP3, with Hotfix 945674 applied after reading
these postings and MS Knowledgebase etc.

I am still experiencing inconsistant problems with adding look-up values
to Forms and Tables, e.g.

Using a Look-up Table in a Combo Box, I wish to display a textual value
label in a Form, and store its associated numerical value in the
associated Table.

After days of errors, I have now tested the issue extensively with a
simple 3-value 2-column look-up table. I am unable to get Access to
*consistently* apply the look-up values as required.

The nearest that I have come to a solution is:

Using this Look-up List in a Form:

Value Value Label
-9 Absent
1 Front
2 Back

When the Bound Column for the Look-up Table attached to the Form is set
to 0:
The textual Value Label correctly displays in the Form: Absent, Front,
Back
The numerical value correctly displays in the Table: -9, 1, 2

When achieving the above results, the Data Table Look-up (if it matters)
is set to Look-up List Bound Column 0, Column Count 2.

Data can be entered into the Data Table from the Table-bound Look-up
list without problem, and is also reflected back correctly into the
Form.

This all woked fine and dandy for a few operations, after which, with
nmo obvious changes made, the Form now displays numerical values, rather
than text labels.

It seems that no matter what I do, there is some inconsistancy in Access
operation which is driving me to distraction...

Any advice or work-arounds would be most welcome - this is a
work-stopper!

Paul

*** Sent via Developersdex http://www.developersdex.com ***
Why is the bound column 0? Change it to 1 and see if that works.

Jul 28 '08 #11

P: n/a

Thanks for that, but I have tried BoundColumn values 0, 1 and 2 !! in
desperation and can't get it to work properly with any setting.

I used Bound Column 0 following the advice at
http://office.microsoft.com/assistan...HP030898651033,
which says that the Bound Column number is an offset, with the first
column having a value of 0. That column contain the numerical values
that I wish to store in the Data Table.

The results when I try bound columns 1 and 2 (all other settings
remaining the same) were:

When the Bound Column for the Look-up Table attached to the Form is set
to 1:
The textual Value Label displays in the Form: Absent, Front, Back, but
with the Error Message – “The value you entered isn’t valid for this
field”
Numerical value cannot be stored in the Table

When the Bound Column for the Look-up Table attached to the Form is set
to 2:
The textual Value Label displays in the Form: Absent, Front, Back
The numerical value displays in the Table: [No value], 1, 2

Paul

*** Sent via Developersdex http://www.developersdex.com ***
Jul 29 '08 #12

P: n/a
The link you provided didn't work, but that information is incorrect.
The Bound Column property is 1 based, meaning that you need to enter 1
for the first column, 2 for the second, etc. If you enter a zero, the
list index is saved to the field instead of an individual value. The
list index is zero based. So a combo box is kind of like the below:

List Index Value Value Label
(Column 0) (Column 1) (Column 2)
0 -9 Absent
1 1 Front
2 2 Back

You can't display the list index, you can only see columns 1 or higher.

To store numerical values and display text values, you need to set your
combo box like this (assuming your number is in the first column and
your text in the second):

Column Widths: 0";1" (or however wide your combo box needs to be)
Bound Column: 1

Leave the column count at 2.

Hope that helps,

Carlos

Aug 2 '08 #13

P: n/a
Carlos
Very many thanks for that; I have got it to work. It seems that in a
2-column look-up table, the Column Width that is set to "0" determines
that the otehr column value is displayed, whether that be text or
numeral. I would not have found that by logical deduction!

Thanks again.

Paul

*** Sent via Developersdex http://www.developersdex.com ***
Aug 3 '08 #14

P: n/a
More precisely, Access comboboxes display the leftmost column with a
nonzero width. Like you said, in a two column combobox setting one column
to zero width will cause the combobox to display the other one. In a three
or more column combobox, the column you want to display has to be not only
nonzero width, but the leftmost.

I'm glad you were able to get it working again.

Carlos

navvyman <an*******@devdex.comwrote in news:1217787259_29008
@news.newsfeeds.com:
Carlos
Very many thanks for that; I have got it to work. It seems that in a
2-column look-up table, the Column Width that is set to "0" determines
that the otehr column value is displayed, whether that be text or
numeral. I would not have found that by logical deduction!

Thanks again.

Paul

*** Sent via Developersdex http://www.developersdex.com ***
Aug 6 '08 #15

This discussion thread is closed

Replies have been disabled for this discussion.