Need help on fetching column values and populating a combobox.

[ssb]

Hello,

This may be very elementary, but, need help because I am new to access
programming.

(1) Say, I have a column EMPLOYEE_NAME. How do I fetch (maybe, cursor
?) the values one by one and populate a combo box with these names.
(this way, I can display all the EMPLOYEE_NAME values)

(2) In general, can I do additional processing on column values from
within a cursor?

Eg: (a) populate a combo box,
(b) convert column value into a text-label value or a text box
value,
(c) store the column value into a local variable and use it in
future etc..

(3) Can I do a reverse process. i.e. Read a combox box sequentially
and taking that element as KEY, do and INSERT / UPDATE / DELETE on
EMPLOYEE_TABLE..??

Can you suggest some resources, where sample codes are given for these
kind of manipulations..?

Thanks for the help...

[Bruce Dodds]

ssb wrote:

Hello,

This may be very elementary, but, need help because I am new to access
programming.

(1) Say, I have a column EMPLOYEE_NAME. How do I fetch (maybe, cursor
?) the values one by one and populate a combo box with these names.
(this way, I can display all the EMPLOYEE_NAME values)

(2) In general, can I do additional processing on column values from
within a cursor?

Eg: (a) populate a combo box,
(b) convert column value into a text-label value or a text box
value,
(c) store the column value into a local variable and use it in
future etc..

(3) Can I do a reverse process. i.e. Read a combox box sequentially
and taking that element as KEY, do and INSERT / UPDATE / DELETE on
EMPLOYEE_TABLE..??

Can you suggest some resources, where sample codes are given for these
kind of manipulations..?

Thanks for the help...

You can do most of that.

Look up the RowSouce property in Help for info on how to populate a
combo box from a table.

You can use the AfterUpdate event of the combo box to take the selected
value and put it in a text box or local variable, or make changes to the
source table.

The Access Developer Handbook series by Litwin, Getz et al (Sybex) are
excellent resources.

[Larry Linson]

"ssb" wrote

(1) Say, I have a column EMPLOYEE_NAME.
How do I fetch (maybe, cursor?) the values
one by one and populate a combo box with
these names. (this way, I can display all the
EMPLOYEE_NAME values)
We generally do not use the "cursor" term in Access unless referring to a
server database, and there is a very simple way to populate the Combo Box
with the data you describe. Create a Query, using the Query Builder, that
has the columns you want, and use the Wizard to create the Combo Box. If, as
is often convenient, you have a unique EmployeeID field in that table, it
may well be the field that you wish to store.
(2) In general, can I do additional processing
on column values from within a cursor?
In a Query, you can define calculated fields using Access expressions and
user-defined Functions... that is very flexible. If you choose to use DAO --
Data Access Objects code in Visual Basic for Applications (or ADO, though I
recommend DAO, generally), you can add processing for each or particular
records.
Eg: (a) populate a combo box,
See above
(b) convert column value into a text-label
value or a text box value,
Labels have a Caption (no Value property) that can be set from VBA code, but
not from a Query -- they are not intended to be used to display data from
your tables. Text Boxes have a Control Source property that can be used to
bind them to a Field in the Form's RecordSource (a Query, SQL Statement, or
Table), or that can be used to contain an expression for a calculated
Control.
(c) store the column value into a local
variable and use it in future etc..
You can do this from VBA code, either in a Form or Report event, or code
executed that reads in a RecordSet and processes it.
(3) Can I do a reverse process. i.e. Read a
combox box sequentially and taking that
element as KEY, do and INSERT /
UPDATE / DELETE on EMPLOYEE_TABLE..??
Yes, but the intention of a Combo Box is not for "data input", and certainly
not for its list to be accessed sequentially. It is to allow the user to
select a list entry.
Can you suggest some resources, where sample
codes are given for these kind of manipulations..?

From the questions you ask, it would appear to me that you need some basic,
novice Access user training/education, although you seem familiar with some
(server) database terminology (such as 'cursor').

I'd suggest you start by investing in a simple self study book such as
"Microsoft Access Step-by-Step" from Microsoft Press -- it is a very good
book for beginning Access users (but see below). Another Microsoft Press
Book that starts at the beginning and goes deeper is "Microsoft Access 2003
Inside Out" by John Viescas - I strongly recommend this one. A third-party
book that I have used in past editions is Roger Jenning's "Special Edition
Using Access <version>". There are a couple of other series that I suggest,
later in your study, Dr. Rick Dobson's "Programming Microsoft Access
<version>" is good for moving from power-user to developer with an emphasis
on VBA (though I tend to think he underemphasizes DAO and overemphasizes
ADO), and a second series that is a concensus choice of serious Access
developers, the Access Developer's Handbook, by Litwin, Getz, et al, from
Sybex -- but you are some study away from being ready for either of these.

If you are using Access 2002 or 2003, the training courses that you can
reach via
http://office.microsoft.com/training...EC790020111033
are free and may get you started so that you can bypass the "Step by Step"
book.

Welcome to the world of Access and good luck with your studies. One piece of
advice: don't expect Access to be something else with which you are
familiar, either scaled down or up -- cherish it for itself, because it is a
really good product that extends far beyond the "little desktop database"
that some have thought it to be.

Larry Linson
Microsoft Access MVP

[Bruce Dodds]

Larry Linson wrote:

"ssb" wrote

> (1) Say, I have a column EMPLOYEE_NAME.
> How do I fetch (maybe, cursor?) the values
> one by one and populate a combo box with
> these names. (this way, I can display all the
> EMPLOYEE_NAME values)

We generally do not use the "cursor" term in Access unless referring to a
server database, and there is a very simple way to populate the Combo Box
with the data you describe. Create a Query, using the Query Builder, that
has the columns you want, and use the Wizard to create the Combo Box. If, as
is often convenient, you have a unique EmployeeID field in that table, it
may well be the field that you wish to store.

> (2) In general, can I do additional processing
> on column values from within a cursor?

In a Query, you can define calculated fields using Access expressions and
user-defined Functions... that is very flexible. If you choose to use DAO --
Data Access Objects code in Visual Basic for Applications (or ADO, though I
recommend DAO, generally), you can add processing for each or particular
records.

> Eg: (a) populate a combo box,

See above

> (b) convert column value into a text-label
> value or a text box value,

Labels have a Caption (no Value property) that can be set from VBA code, but
not from a Query -- they are not intended to be used to display data from
your tables. Text Boxes have a Control Source property that can be used to
bind them to a Field in the Form's RecordSource (a Query, SQL Statement, or
Table), or that can be used to contain an expression for a calculated
Control.

> (c) store the column value into a local
> variable and use it in future etc..

You can do this from VBA code, either in a Form or Report event, or code
executed that reads in a RecordSet and processes it.

> (3) Can I do a reverse process. i.e. Read a
> combox box sequentially and taking that
> element as KEY, do and INSERT /
> UPDATE / DELETE on EMPLOYEE_TABLE..??

Yes, but the intention of a Combo Box is not for "data input", and certainly
not for its list to be accessed sequentially. It is to allow the user to
select a list entry.

> Can you suggest some resources, where sample
> codes are given for these kind of manipulations..?

From the questions you ask, it would appear to me that you need some basic,
novice Access user training/education, although you seem familiar with some
(server) database terminology (such as 'cursor').

I'd suggest you start by investing in a simple self study book such as
"Microsoft Access Step-by-Step" from Microsoft Press -- it is a very good
book for beginning Access users (but see below). Another Microsoft Press
Book that starts at the beginning and goes deeper is "Microsoft Access 2003
Inside Out" by John Viescas - I strongly recommend this one. A third-party
book that I have used in past editions is Roger Jenning's "Special Edition
Using Access <version>". There are a couple of other series that I suggest,
later in your study, Dr. Rick Dobson's "Programming Microsoft Access
<version>" is good for moving from power-user to developer with an emphasis
on VBA (though I tend to think he underemphasizes DAO and overemphasizes
ADO), and a second series that is a concensus choice of serious Access
developers, the Access Developer's Handbook, by Litwin, Getz, et al, from
Sybex -- but you are some study away from being ready for either of these.

If you are using Access 2002 or 2003, the training courses that you can
reach via
http://office.microsoft.com/training...EC790020111033
are free and may get you started so that you can bypass the "Step by Step"
book.

Welcome to the world of Access and good luck with your studies. One piece of
advice: don't expect Access to be something else with which you are
familiar, either scaled down or up -- cherish it for itself, because it is a
really good product that extends far beyond the "little desktop database"
that some have thought it to be.

Larry Linson
Microsoft Access MVP

That was awesome, Larry. If you have any insight about the tabledef
problem I posted about two lines up, let me know.

ssb, I assumed you were a developer due to your use of "cursor". If my
advice presumed too much knowledge, regrets.