HOWTO: use RowSource property of a field in MS Access table

[Vladimir]

Hello,

I have a table in MS Access database. It has one field (with BYTE datatype)
that has several properties set in Lookup tab of table Design View.
Display Control = Combo Box. Row Source Type = Value List. Row Source = "1;
"Above"; 2; "Below"; 3; "Equal"".
When I try to SELECT <field> FROM <table> in my C++ application through ADO,
I get numeric value of the field. How can I get string representation of
this numeric value from the Row Source property? The code is below:

CString tmp;
_RecordsetPtr pRs("ADODB.Reco rdset");
tmp = "select field from table"; // query
pRs->Open((_bstr_t) tmp, _variant_t((IDi spatch*)pGWMCon n, true),
adOpenForwardOn ly, adLockReadOnly, adCmdText); // open recordset
tmp = (LPCTSTR)(_bstr _t)pRs->Fields->Item["field"]->Value; // get
numeric value (string from Row Source is needed)
pRs->Close();

Regards,
Vladimir.

[Steve Jorgensen]

The properties you mention only apply to how the table is represented in
the in datasheet view interface in Access. In any other context, you
should use a query join to get the data from the other table.

In some cases, it's better to put the whole SQL statement in the code. In
other cases, it's better to save a querydef (Procedure in ADO parlance)
that encapsulates the query/join. Take your pick.

On Tue, 16 Sep 2003 01:33:48 +0600, "Vladimir" <ge******@ufaco m.ru> wrote:

Hello,

I have a table in MS Access database. It has one field (with BYTE datatype)
that has several properties set in Lookup tab of table Design View.
Display Control = Combo Box. Row Source Type = Value List. Row Source = "1;
"Above"; 2; "Below"; 3; "Equal"".
When I try to SELECT <field> FROM <table> in my C++ application through ADO,
I get numeric value of the field. How can I get string representation of
this numeric value from the Row Source property? The code is below:

CString tmp;
_RecordsetPtr pRs("ADODB.Reco rdset");
tmp = "select field from table"; // query
pRs->Open((_bstr_t) tmp, _variant_t((IDi spatch*)pGWMCon n, true),
adOpenForwardO nly, adLockReadOnly, adCmdText); // open recordset
tmp = (LPCTSTR)(_bstr _t)pRs->Fields->Item["field"]->Value; // get
numeric value (string from Row Source is needed)
pRs->Close();

Regards,
Vladimir.

[Programmer Dude]

Vladimir wrote:

Row Source = "1; "Above"; 2; "Below"; 3; "Equal"".
When I try to SELECT <field> FROM <table> in my C++ application
through ADO, I get numeric value of the field. How can I get
string representation of this numeric value from the Row Source
property?

You don't. It doesn't exist in the table, ONLY in the Access Form
(specifically, in the ComboBox). I assume the BoundColumn prop is
set to 1. That means that when a user selects an item from the CB,
the first column's value is put in the database field. The second
column values (the strings) never leave the CB.

You have two choices:

Ideal: create a new table containing the CB Row Source values, and
use that to populate the CB. When you open a recordset, open an
SQL query that joins the main table to the new table and select
the field containing the strings.

Workable: Re-create the Row Source data in your C++ application
and translate from the numeric value to the string.

--
|_ CJSonnack <Ch***@Sonnack. com> _____________| How's my programming? |
|_ http://www.Sonnack.com/ _______________ ____| Call: 1-800-DEV-NULL |
|______________ _______________ _______________ _|_____________ __________|

[Vladimir]

Hello,

But why can't I get access to Properties collection of Field object? In
Access Help I've read that I can set these (RowSource and others) properties
through VB. Why can't I do this in my C++ app? Also when I execute query
SELECT <field> FROM <table> in Access Query Designer I get string. When I
execute through ADO I get number.

The problem is that I don't know if Row Source string array will never be
changed. If I place this array in my app I will then need to make changes to
source code and re-compile the program.

The "Ideal" way is also unacceptable because the users of my app will need
to add this new table to the database every time it is replaced by new one
(which is without this table). I mean replacing whole MDB files.

Regards,
Vladimir.

Programmer Dude <cj*******@mmm. com> ïèøåò â
ñîîáùåíèè:3F*** ************@mm m.com...

Vladimir wrote:

Row Source = "1; "Above"; 2; "Below"; 3; "Equal"".
When I try to SELECT <field> FROM <table> in my C++ application
through ADO, I get numeric value of the field. How can I get
string representation of this numeric value from the Row Source
property?

You don't. It doesn't exist in the table, ONLY in the Access Form
(specifically, in the ComboBox). I assume the BoundColumn prop is
set to 1. That means that when a user selects an item from the CB,
the first column's value is put in the database field. The second
column values (the strings) never leave the CB.

You have two choices:

Ideal: create a new table containing the CB Row Source values, and
use that to populate the CB. When you open a recordset, open an
SQL query that joins the main table to the new table and select
the field containing the strings.

Workable: Re-create the Row Source data in your C++ application
and translate from the numeric value to the string.

--
|_ CJSonnack <Ch***@Sonnack. com> _____________| How's my programming? |
|_ http://www.Sonnack.com/ _______________ ____| Call: 1-800-DEV-NULL |
|______________ _______________ _______________ _|_____________ __________|

[Steve Jorgensen]

The properties are not stored in the normal table attributes, they're
stored in special Document structures used by Access alone. Even if you
-could- read the properties, you'd still have to parse out the combo box
query text, and run it in your own code to get the related value since
Combo boxes are Access GUI constructs, and not part of tables or query
results directly.

On Tue, 16 Sep 2003 12:10:54 +0600, "Vladimir" <ge******@ufaco m.ru> wrote:

Hello,

But why can't I get access to Properties collection of Field object? In
Access Help I've read that I can set these (RowSource and others) properties
through VB. Why can't I do this in my C++ app? Also when I execute query
SELECT <field> FROM <table> in Access Query Designer I get string. When I
execute through ADO I get number.

The problem is that I don't know if Row Source string array will never be
changed. If I place this array in my app I will then need to make changes to
source code and re-compile the program.

The "Ideal" way is also unacceptable because the users of my app will need
to add this new table to the database every time it is replaced by new one
(which is without this table). I mean replacing whole MDB files.

Regards,
Vladimir.

Programmer Dude <cj*******@mmm. com> ïèøåò â
ñîîáùåíèè:3F** *************@m mm.com...

Vladimir wrote:

> Row Source = "1; "Above"; 2; "Below"; 3; "Equal"".
> When I try to SELECT <field> FROM <table> in my C++ application
> through ADO, I get numeric value of the field. How can I get
> string representation of this numeric value from the Row Source
> property?

You don't. It doesn't exist in the table, ONLY in the Access Form
(specifically, in the ComboBox). I assume the BoundColumn prop is
set to 1. That means that when a user selects an item from the CB,
the first column's value is put in the database field. The second
column values (the strings) never leave the CB.

You have two choices:

Ideal: create a new table containing the CB Row Source values, and
use that to populate the CB. When you open a recordset, open an
SQL query that joins the main table to the new table and select
the field containing the strings.

Workable: Re-create the Row Source data in your C++ application
and translate from the numeric value to the string.

--
|_ CJSonnack <Ch***@Sonnack. com> _____________| How's my programming? |
|_ http://www.Sonnack.com/ _______________ ____| Call: 1-800-DEV-NULL |
|______________ _______________ _______________ _|_____________ __________|

[Programmer Dude]

Vladimir wrote:

But why can't I get access to Properties collection of Field object?
You can. Row Source is *NOT* a property of a Field. It's a property
of the ComboBox on the Form in Access.
Also when I execute query SELECT <field> FROM <table> in Access
Query Designer I get string. When I execute through ADO I get
number.
Are you saying you get "Above" in that case and "1" if you issue
an SQL statement through VC++? That's.... impossible. Something
else is going on. Are you sure the Query Designer isn't linking
in a table or doing some sort of lookup?

Take a look under the hood of that query and maybe look at the
definition of the table in question. Access can link to lookup
tables--did you design the database or did someone else?
The problem is that I don't know if Row Source string array will
never be changed. If I place this array in my app I will then need
to make changes to source code and re-compile the program.
That's why it's non-ideal. You could use an external text file to
initialize your program. That would save re-compiling.
The "Ideal" way is also unacceptable because the users of my app
will need to add this new table to the database every time it is
replaced by new one (which is without this table). I mean
replacing whole MDB files.

I take it you are not supplying the MDB files (else you could just
add it yourself).
--
|_ CJSonnack <Ch***@Sonnack. com> _____________| How's my programming? |
|_ http://www.Sonnack.com/ _______________ ____| Call: 1-800-DEV-NULL |
|______________ _______________ _______________ _|_____________ __________|

[Vladimir]

Hello,

Programmer Dude <Ch***@Sonnack. com> ïèøåò â
ñîîáùåíèè:3F*** ************@So nnack.com...

Vladimir wrote:

But why can't I get access to Properties collection of Field object?
You can. Row Source is *NOT* a property of a Field. It's a property
of the ComboBox on the Form in Access.

Then how can I get those sort of properties?

Also when I execute query SELECT <field> FROM <table> in Access
Query Designer I get string. When I execute through ADO I get
number.

Are you saying you get "Above" in that case and "1" if you issue
an SQL statement through VC++? That's.... impossible. Something
else is going on. Are you sure the Query Designer isn't linking
in a table or doing some sort of lookup?

It is possible. You can create your own table in Access with one field
having lookup with following properties:

Display control = Combo Box
Row ource type = Value list
Row source = 1;"qwe";2;"asd" ;3;"zxc"
Bound column = 1
Column count = 2
Column heads = No
Column widths = 0cm;4cm
List rows = 8
List width = 4cm
Limit to list = Yes

Then fill table with data and enter MANUALLY in Query Designer (View / SQL
mode) this text: SELECT <field> FROM <table> - and you will get strings.
Take a look under the hood of that query and maybe look at the
definition of the table in question. Access can link to lookup
tables--did you design the database or did someone else?

I'm not but I've tried to design table as described above.

Regards,
Vladimir.

[Steve Jorgensen]

Yet again, I correct myself. The properties are available through the
containers/documents collection of the DAO database object, and therefore
they are available outside of Access, though the code that makes use of
this data to display a combo box in datasheed view and look up a matching
row in another table belonce solely to Access.

I'm assuming this is one of those cases where DAO has some capabilities
with JET databases that ADO does not, but they might be accessible as
metadata using the ADOX library. It's worth a try, I suppose.

On Tue, 16 Sep 2003 07:12:43 GMT, Steve Jorgensen <no****@nospam. nospam>
wrote:

The properties are not stored in the normal table attributes, they're
stored in special Document structures used by Access alone. Even if you
-could- read the properties, you'd still have to parse out the combo box
query text, and run it in your own code to get the related value since
Combo boxes are Access GUI constructs, and not part of tables or query
results directly.

On Tue, 16 Sep 2003 12:10:54 +0600, "Vladimir" <ge******@ufaco m.ru> wrote:

Hello,

But why can't I get access to Properties collection of Field object? In
Access Help I've read that I can set these (RowSource and others) properties
through VB. Why can't I do this in my C++ app? Also when I execute query
SELECT <field> FROM <table> in Access Query Designer I get string. When I
execute through ADO I get number.

The problem is that I don't know if Row Source string array will never be
changed. If I place this array in my app I will then need to make changes to
source code and re-compile the program.

The "Ideal" way is also unacceptable because the users of my app will need
to add this new table to the database every time it is replaced by new one
(which is without this table). I mean replacing whole MDB files.

Regards,
Vladimir.

Programmer Dude <cj*******@mmm. com> ïèøåò â
ñîîáùåíèè:3F* **************@ mmm.com...

Vladimir wrote:

> Row Source = "1; "Above"; 2; "Below"; 3; "Equal"".
> When I try to SELECT <field> FROM <table> in my C++ application
> through ADO, I get numeric value of the field. How can I get
> string representation of this numeric value from the Row Source
> property?

You don't. It doesn't exist in the table, ONLY in the Access Form
(specifically, in the ComboBox). I assume the BoundColumn prop is
set to 1. That means that when a user selects an item from the CB,
the first column's value is put in the database field. The second
column values (the strings) never leave the CB.

You have two choices:

Ideal: create a new table containing the CB Row Source values, and
use that to populate the CB. When you open a recordset, open an
SQL query that joins the main table to the new table and select
the field containing the strings.

Workable: Re-create the Row Source data in your C++ application
and translate from the numeric value to the string.

--
|_ CJSonnack <Ch***@Sonnack. com> _____________| How's my programming? |
|_ http://www.Sonnack.com/ _______________ ____| Call: 1-800-DEV-NULL |
|______________ _______________ _______________ _|_____________ __________|

[Programmer Dude]

Vladimir wrote:

Row Source is *NOT* a property of a Field. It's a property
of the ComboBox on the Form in Access.
Then how can I get those sort of properties?

I'm not sure you can with ADO. If you use Access COM, you might
be able to, but it may require the form be open (the Access COM
includes a Forms collection, which would contain the Form in
question, but I'm not sure you can access it unless the Form is
opened).

Are you saying you get "Above" in that case and "1" if you issue
an SQL statement through VC++? That's.... impossible. Something
else is going on. Are you sure the Query Designer isn't linking
in a table or doing some sort of lookup?

It is possible. You can create your own table in Access with one
field having lookup with following properties:

Display control = Combo Box
Row ource type = Value list
Row source = 1;"qwe";2;"asd" ;3;"zxc"
Bound column = 1
Column count = 2
Column heads = No
Column widths = 0cm;4cm
List rows = 8
List width = 4cm
Limit to list = Yes

Yes, this is exactly what I was talking about. The Access table
has a control defined in it. I'm fairly certain you can't access
this via ADO, but you might via Access COM.
Then fill table with data and enter MANUALLY in Query Designer
(View / SQL mode) this text: SELECT <field> FROM <table> - and
you will get strings.

Yes, I see what you mean, now. I'm leaving for the day and might
not be back until Monday, but if you are still having trouble then,
I'll see if I can provide some areas to explore.

--
|_ CJSonnack <Ch***@Sonnack. com> _____________| How's my programming? |
|_ http://www.Sonnack.com/ _______________ ____| Call: 1-800-DEV-NULL |
|______________ _______________ _______________ _|_____________ __________|