473,324 Members | 2,456 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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

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.Recordset");
tmp = "select field from table"; // query
pRs->Open((_bstr_t)tmp, _variant_t((IDispatch*)pGWMConn, true),
adOpenForwardOnly, 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.


Nov 12 '05 #1
8 20256
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******@ufacom.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.Recordset");
tmp = "select field from table"; // query
pRs->Open((_bstr_t)tmp, _variant_t((IDispatch*)pGWMConn, true),
adOpenForwardOnly, 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.


Nov 12 '05 #2
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 |
|_____________________________________________|___ ____________________|
Nov 12 '05 #3
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 |
|_____________________________________________|___ ____________________|

Nov 12 '05 #4
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******@ufacom.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 |
|_____________________________________________|___ ____________________|


Nov 12 '05 #5
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 |
|_____________________________________________|___ ____________________|
Nov 12 '05 #6
Hello,

Programmer Dude <Ch***@Sonnack.com> ïèøåò â
ñîîáùåíèè:3F***************@Sonnack.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.
Nov 12 '05 #7
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******@ufacom.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 |
|_____________________________________________|___ ____________________|


Nov 12 '05 #8
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 |
|_____________________________________________|___ ____________________|
Nov 12 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Irena | last post by:
Hi all again, Maybe, I'd better try to do this: How can it programmatically get/return the "Row source" or Rowsource property of a generic Table in a generic Access 2K database??? I found...
0
by: Irena | last post by:
Hi all there, Form time to time, I go back to this project of mine that drives me nuts, now. Sometimes ago, I have been suggested to use DAO insted of ADO for getting the "ROWSOURCE" property...
8
by: bbdata | last post by:
ok i have a problem here and not much time to play round. have a form bound to a table. one of the combos is bound to a field Agents. i have active and retired agents. thing is, i want to be able...
13
by: nyt | last post by:
I have a problem of number and text field. I got the database file(mdb) that contains many combo boxes used and its list values are created by "value list" For eg field Field name= 'furniture'...
3
by: Richard Hollenbeck | last post by:
I have a column in a table that currently only has one record. That column is set to combo box. I want the user to be able to lookup values from the column itself--the very same column--rather...
3
by: u473 | last post by:
How do I call a reusable report with a modifiable Rowsource property and or code ? Like in the following case where the table name will change any following day ? I am using Access 2003. ...
4
by: madereal | last post by:
howdy scriptors… here’s my question: Project Background: I’m creating a database that will hold questionnaire data. Situation: I would like to set up a combobox with a changing rowsource...
8
by: Simon | last post by:
Dear reader, The syntax for the VBA code to change the RowSource of a Form is: Forms!FormName..RowSource = "TableOrQueryName"
11
by: Simon | last post by:
Dear reader, The syntax for the VBA code to change the RowSource of a Master Report is: Me.RowSource = "TableOrQueryName"
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.