subqueries in text control boxes

[Gwin]

I am programming a client service dbase in access 2000 for a
non-profit health clinic.

I want to create a navigation menu that can show varying numbers of
command buttons with varying captions, driven by data from a table.

1. First, the user selects a "client" by code. This opens a
navigation form which offers command buttons for each of the services
the client is enrolled in, e.g., nutrition, pre-natal care, drug
treatment, etc. There are 10 possible services, so there are ten
buttons, all invisible until a count of client services is established
(from a table, where each of the services has its own index). If the
client has 4 services, then buttons 1 through 4 become visible. So
far, so good. I am able to get that to work.

2. I would like each command button to have a caption that varies
depending on the services. Each time the listing the services is
generated, the records are numbered consecutively. So I would like to
use text control for each command button, where the control source is
a subquery to make something which says,

(SELECT tService.Service FROM tService WHERE
(((tsvcCount.counter)=1)));

(or counter = 2 or whatever....)

Each command button's caption would be driven by the text appearing in
this text control.

So far, I can get the text control to accept the syntax of the
subquery, but when I view the form, the text control just says #Name?

Am I trying the impossible? Is it possible to use a subquery in a
text control?

Gwindor

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You are trying to create a Switchboard form. Why not use the
Switchboard Manager to create one instead of building one from scratch.
Or, create a Switchboard form & use it as a template for your menu form.

Acc2K2:
Tools > Database Utilities > Switchboard Manager

Acc97:
Tools > Addins > Switchboard Manager

For future reference: a query in a TextBox's ControlSource will not
return the query's resultset. You may wish to try the DLookup()
function instead. E.g.:

Control Source: =DLookup("Service","tService","Counter=1")

See the Access VBA Help article "DLookup Function" for more info.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQJaPeIechKqOuFEgEQKYHwCffMhy+fJsj+ux3eX193pdqo VXY6EAn2Ht
qoYeRxRLP2PWrrEkPh/iuB0c
=VbxQ
-----END PGP SIGNATURE-----
Gwin wrote:

I am programming a client service dbase in access 2000 for a
non-profit health clinic.

I want to create a navigation menu that can show varying numbers of
command buttons with varying captions, driven by data from a table.

1. First, the user selects a "client" by code. This opens a
navigation form which offers command buttons for each of the services
the client is enrolled in, e.g., nutrition, pre-natal care, drug
treatment, etc. There are 10 possible services, so there are ten
buttons, all invisible until a count of client services is established
(from a table, where each of the services has its own index). If the
client has 4 services, then buttons 1 through 4 become visible. So
far, so good. I am able to get that to work.

2. I would like each command button to have a caption that varies
depending on the services. Each time the listing the services is
generated, the records are numbered consecutively. So I would like to
use text control for each command button, where the control source is
a subquery to make something which says,

(SELECT tService.Service FROM tService WHERE
(((tsvcCount.counter)=1)));

(or counter = 2 or whatever....)

Each command button's caption would be driven by the text appearing in
this text control.

So far, I can get the text control to accept the syntax of the
subquery, but when I view the form, the text control just says #Name?

Am I trying the impossible? Is it possible to use a subquery in a
text control?

[Gwin]

Thanks! I will try the dlookup.

The switchboard isn't viable because I want to control which buttons
appear at which times. But dlookup is something I need to learn
anyhow!

Gwindor
On Mon, 03 May 2004 18:29:04 GMT, MGFoster <[e-mail]> wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You are trying to create a Switchboard form. Why not use the
Switchboard Manager to create one instead of building one from scratch.
Or, create a Switchboard form & use it as a template for your menu form.

Acc2K2:
Tools > Database Utilities > Switchboard Manager

Acc97:
Tools > Addins > Switchboard Manager

For future reference: a query in a TextBox's ControlSource will not
return the query's resultset. You may wish to try the DLookup()
function instead. E.g.:

Control Source: =DLookup("Service","tService","Counter=1")

See the Access VBA Help article "DLookup Function" for more info.