2 Column Counts & Display

  • Thread starter Faraz A. Qureshi
  • Start date
F

Faraz A. Qureshi

I have in field "Nature" set two columns to be looked into and presented in a
combo box, Column 1 showing the Account ID and Column 2 the Account Title.
However, when I select an entry only the first column, i.e. Account ID is
displayed. I want the entry to be either showing both or the second column's
relevant entry i.e. either:
Account ID | Account Title or
Account Title

Any suggestions/guidance?
 
T

Tom Wickerath

Hi Faraz ,

A combo box that is not in dropped down mode will only display the first
visible column. The easiest way to display the second column, Account Title,
is to set the column width for the first column to 0 (zero).

If you want to display both columns, you will need to concatenate them into
one column. Something like this, as the row source for the combo box:

SELECT [Account ID], [Account ID] & " | " & [Account Title] AS Account
FROM YourTableName
ORDER BY [Account Title]

This query still has two columns, [Account ID] and the concatenated Account
info. You want the first column as the bound column, so that the Account ID
value is what will get saved. You need to specify the appropriate field to
save this data to as the Control Source property. It must have the same data
type and size as the [Account ID] field (use Long Integer if Account ID is an
Autonumber data type). Set the number of columns to 2, with column widths of
0, 1.5 so that the bound field will not be displayed.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
 
F

Faraz A. Qureshi

Thanx Tom,

Unfortunately, I think I was not clear. Actually it is a SINGLE field. In
Design Form I selected the Data option "Lookup Wizard", then I selected two
columns. In first column I inserted numbers like 01, 02 03 .... and in the
second column I inserted their relevant entries like Choice A, Choice B,
Choice C...

While inserting data when I open the drop down both the columns are
displayed but when I choose any entry the result displayed in the same is 01,
02, 03...

Could the entry in any way be shown as both or
Only the second column, i.e. Choice A, Choice B, Choice C... ?

--
Thanx in advance,
Best Regards,

Faraz


Tom Wickerath said:
Hi Faraz ,

A combo box that is not in dropped down mode will only display the first
visible column. The easiest way to display the second column, Account Title,
is to set the column width for the first column to 0 (zero).

If you want to display both columns, you will need to concatenate them into
one column. Something like this, as the row source for the combo box:

SELECT [Account ID], [Account ID] & " | " & [Account Title] AS Account
FROM YourTableName
ORDER BY [Account Title]

This query still has two columns, [Account ID] and the concatenated Account
info. You want the first column as the bound column, so that the Account ID
value is what will get saved. You need to specify the appropriate field to
save this data to as the Control Source property. It must have the same data
type and size as the [Account ID] field (use Long Integer if Account ID is an
Autonumber data type). Set the number of columns to 2, with column widths of
0, 1.5 so that the bound field will not be displayed.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

Faraz A. Qureshi said:
I have in field "Nature" set two columns to be looked into and presented in a
combo box, Column 1 showing the Account ID and Column 2 the Account Title.
However, when I select an entry only the first column, i.e. Account ID is
displayed. I want the entry to be either showing both or the second column's
relevant entry i.e. either:
Account ID | Account Title or
Account Title

Any suggestions/guidance?
--
Thanx in advance,
Best Regards,

Faraz
Click to expand...
Click to expand...
 
T

Tom Wickerath

Hi Faraz,

I'm not understanding the situation...

You wrote "Actually it is a SINGLE field", but you also wrote "...then I
selected two columns." I'm confused now. Is it a single field or is it two
fields (columns)?

Please provide the following:

Rowsource:
Rowsource Type:
Bound Column:
Control Source:
Column Count:
Column Widths:

If your Rowsource is a saved query, provide the SQL (Structured Query
Language) statement for this query. You can open the query in design view and
then flip to SQL View, where you can copy the SQL statement and paste it into
a reply. If your Rowsource is a table, say so, but I suggest changing it to a
query.
While inserting data when I open the drop down both the columns are
displayed but when I choose any entry the result displayed in the same is 01,
02, 03...
Click to expand...

Yes, a combo box will only display the first non-zero width column when it
is not dropped down. This sure sounds to me like you are dealing with two
columns and not a single field (column).

I am calling it a night now, but I'll be back to check this thread in
approx. 11 hours. Someone else may step in, so you can help them and me by
answering the questions I have about the properties of your combo box.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

Faraz A. Qureshi said:
Thanx Tom,

Unfortunately, I think I was not clear. Actually it is a SINGLE field. In
Design Form I selected the Data option "Lookup Wizard", then I selected two
columns. In first column I inserted numbers like 01, 02 03 .... and in the
second column I inserted their relevant entries like Choice A, Choice B,
Choice C...

While inserting data when I open the drop down both the columns are
displayed but when I choose any entry the result displayed in the same is 01,
02, 03...

Could the entry in any way be shown as both or
Only the second column, i.e. Choice A, Choice B, Choice C... ?

--
Thanx in advance,
Best Regards,

Faraz


Tom Wickerath said:
Hi Faraz ,

A combo box that is not in dropped down mode will only display the first
visible column. The easiest way to display the second column, Account Title,
is to set the column width for the first column to 0 (zero).

If you want to display both columns, you will need to concatenate them into
one column. Something like this, as the row source for the combo box:

SELECT [Account ID], [Account ID] & " | " & [Account Title] AS Account
FROM YourTableName
ORDER BY [Account Title]

This query still has two columns, [Account ID] and the concatenated Account
info. You want the first column as the bound column, so that the Account ID
value is what will get saved. You need to specify the appropriate field to
save this data to as the Control Source property. It must have the same data
type and size as the [Account ID] field (use Long Integer if Account ID is an
Autonumber data type). Set the number of columns to 2, with column widths of
0, 1.5 so that the bound field will not be displayed.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

Faraz A. Qureshi said:
I have in field "Nature" set two columns to be looked into and presented in a
combo box, Column 1 showing the Account ID and Column 2 the Account Title.
However, when I select an entry only the first column, i.e. Account ID is
displayed. I want the entry to be either showing both or the second column's
relevant entry i.e. either:
Account ID | Account Title or
Account Title

Any suggestions/guidance?
--
Thanx in advance,
Best Regards,

Faraz
Click to expand...
Click to expand...
Click to expand...
 
F

Faraz A. Qureshi

Tom,

In the design form of a table I chose "Lookup Wizard" as Data Type;
In the first question I selected "I will type in the values that I want";
I inserted 2 as the number of columns;
In Col1, I inserted IDs 1 to 9;
In Col2, it was ChoiceA to ChoiceI that I have inserted;
Row Source is:
1;"A";2;"B";3;"C";4;"D";5;"E";6;"F";7;"G";8;"H";9;"I";
& RowSource Type is:
Value List
--
Thanx in advance,
Best Regards,

Faraz


Tom Wickerath said:
Hi Faraz,

I'm not understanding the situation...

You wrote "Actually it is a SINGLE field", but you also wrote "...then I
selected two columns." I'm confused now. Is it a single field or is it two
fields (columns)?

Please provide the following:

Rowsource:
Rowsource Type:
Bound Column:
Control Source:
Column Count:
Column Widths:

If your Rowsource is a saved query, provide the SQL (Structured Query
Language) statement for this query. You can open the query in design view and
then flip to SQL View, where you can copy the SQL statement and paste it into
a reply. If your Rowsource is a table, say so, but I suggest changing it to a
query.
While inserting data when I open the drop down both the columns are
displayed but when I choose any entry the result displayed in the same is 01,
02, 03...
Click to expand...

Yes, a combo box will only display the first non-zero width column when it
is not dropped down. This sure sounds to me like you are dealing with two
columns and not a single field (column).

I am calling it a night now, but I'll be back to check this thread in
approx. 11 hours. Someone else may step in, so you can help them and me by
answering the questions I have about the properties of your combo box.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

Faraz A. Qureshi said:
Thanx Tom,

Unfortunately, I think I was not clear. Actually it is a SINGLE field. In
Design Form I selected the Data option "Lookup Wizard", then I selected two
columns. In first column I inserted numbers like 01, 02 03 .... and in the
second column I inserted their relevant entries like Choice A, Choice B,
Choice C...

While inserting data when I open the drop down both the columns are
displayed but when I choose any entry the result displayed in the same is 01,
02, 03...

Could the entry in any way be shown as both or
Only the second column, i.e. Choice A, Choice B, Choice C... ?

--
Thanx in advance,
Best Regards,

Faraz


Tom Wickerath said:
Hi Faraz ,

A combo box that is not in dropped down mode will only display the first
visible column. The easiest way to display the second column, Account Title,
is to set the column width for the first column to 0 (zero).

If you want to display both columns, you will need to concatenate them into
one column. Something like this, as the row source for the combo box:

SELECT [Account ID], [Account ID] & " | " & [Account Title] AS Account
FROM YourTableName
ORDER BY [Account Title]

This query still has two columns, [Account ID] and the concatenated Account
info. You want the first column as the bound column, so that the Account ID
value is what will get saved. You need to specify the appropriate field to
save this data to as the Control Source property. It must have the same data
type and size as the [Account ID] field (use Long Integer if Account ID is an
Autonumber data type). Set the number of columns to 2, with column widths of
0, 1.5 so that the bound field will not be displayed.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

:

I have in field "Nature" set two columns to be looked into and presented in a
combo box, Column 1 showing the Account ID and Column 2 the Account Title.
However, when I select an entry only the first column, i.e. Account ID is
displayed. I want the entry to be either showing both or the second column's
relevant entry i.e. either:
Account ID | Account Title or
Account Title

Any suggestions/guidance?
--
Thanx in advance,
Best Regards,

Faraz
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Wickerath

Hi Faraz,

What about the other properties I asked about?
Bound Column:
Control Source:
Column Count:
Column Widths:
Click to expand...

Given that you are using a Value list (which I really do not recommend), you
are going to need to incorporate the concatenated display values manually,
like this:

Row Source:
1;"01 | A";2;"02 | B";3;"03 | C";4;"04 | D";5;"05 | E";6;"06 | F";7;"07 |
G";8;"08 | H";9;"09 | I";

in order to see values such as: Account ID | Account Title

Furthermore, you need the Column Count = 2, Bound Column =1 (most likely),
and Column Widths = 0;SomeValue

I recommend that you reconsider using the Value list. A better design, in my
opinion, is to create a separate lookup table with the same values. This way,
you can easily add or remove items from the combo box without ever having to
open a form or table in design view. Also, by storing a numeric value instead
of the actual text value via a value list, if your value list ever got
removed by accident, what records would you have available to reconstruct the
corresponding Account Titles?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

Faraz A. Qureshi said:
Tom,

In the design form of a table I chose "Lookup Wizard" as Data Type;
In the first question I selected "I will type in the values that I want";
I inserted 2 as the number of columns;
In Col1, I inserted IDs 1 to 9;
In Col2, it was ChoiceA to ChoiceI that I have inserted;
Row Source is:
1;"A";2;"B";3;"C";4;"D";5;"E";6;"F";7;"G";8;"H";9;"I";
& RowSource Type is:
Value List
--
Thanx in advance,
Best Regards,

Faraz


Tom Wickerath said:
Hi Faraz,

I'm not understanding the situation...

You wrote "Actually it is a SINGLE field", but you also wrote "...then I
selected two columns." I'm confused now. Is it a single field or is it two
fields (columns)?

Please provide the following:

Rowsource:
Rowsource Type:
Bound Column:
Control Source:
Column Count:
Column Widths:

If your Rowsource is a saved query, provide the SQL (Structured Query
Language) statement for this query. You can open the query in design view and
then flip to SQL View, where you can copy the SQL statement and paste it into
a reply. If your Rowsource is a table, say so, but I suggest changing it to a
query.
While inserting data when I open the drop down both the columns are
displayed but when I choose any entry the result displayed in the same is 01,
02, 03...
Click to expand...

Yes, a combo box will only display the first non-zero width column when it
is not dropped down. This sure sounds to me like you are dealing with two
columns and not a single field (column).

I am calling it a night now, but I'll be back to check this thread in
approx. 11 hours. Someone else may step in, so you can help them and me by
answering the questions I have about the properties of your combo box.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

Faraz A. Qureshi said:
Thanx Tom,

Unfortunately, I think I was not clear. Actually it is a SINGLE field. In
Design Form I selected the Data option "Lookup Wizard", then I selected two
columns. In first column I inserted numbers like 01, 02 03 .... and in the
second column I inserted their relevant entries like Choice A, Choice B,
Choice C...

While inserting data when I open the drop down both the columns are
displayed but when I choose any entry the result displayed in the same is 01,
02, 03...

Could the entry in any way be shown as both or
Only the second column, i.e. Choice A, Choice B, Choice C... ?

--
Thanx in advance,
Best Regards,

Faraz


:

Hi Faraz ,

A combo box that is not in dropped down mode will only display the first
visible column. The easiest way to display the second column, Account Title,
is to set the column width for the first column to 0 (zero).

If you want to display both columns, you will need to concatenate them into
one column. Something like this, as the row source for the combo box:

SELECT [Account ID], [Account ID] & " | " & [Account Title] AS Account
FROM YourTableName
ORDER BY [Account Title]

This query still has two columns, [Account ID] and the concatenated Account
info. You want the first column as the bound column, so that the Account ID
value is what will get saved. You need to specify the appropriate field to
save this data to as the Control Source property. It must have the same data
type and size as the [Account ID] field (use Long Integer if Account ID is an
Autonumber data type). Set the number of columns to 2, with column widths of
0, 1.5 so that the bound field will not be displayed.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

:

I have in field "Nature" set two columns to be looked into and presented in a
combo box, Column 1 showing the Account ID and Column 2 the Account Title.
However, when I select an entry only the first column, i.e. Account ID is
displayed. I want the entry to be either showing both or the second column's
relevant entry i.e. either:
Account ID | Account Title or
Account Title

Any suggestions/guidance?
--
Thanx in advance,
Best Regards,

Faraz
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

The value you entered isn't valid for this field. 2
Combobox column 1 6
OpenForm trouble 2
Reworking an old database - need some help 1
combo box, column, calculated control, 2
Limiting Results in Query 1
manipulating a dual column combobox 3
Creating table based on existing table 9

Top