display value on the form using SQL statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have a continuos form which is based on an existing querry (Q1). At the
bottom of the form I would like to display addtitional info for selected
record (staying on the secon line I want to display DetailDescription for
ID2, etc.

ID - Name - shortDescription
-----------------------------
1 N1 D1
2 N2 D2
Click to expand...
--------------------------
DetailDescription: Detail2

How should I construct the SQL querry for txtbox DetailDescription in order
to display a detail description for selected record (line2)?

SELECT DetDescr FROM Table1 WHERE ?????

thansk
 
assuming that the ID field is Table1's primary key, try the WHERE clause as

ID = [Forms]![FormName]!ID

but i don't see how that will help you unless the textbox is in a subform.
is the DetailDescription field in the same table that the form is bound to?
if so, you don't need a separate query at all. just make sure that the
DetailDescription field is included in the form's RecordSource, then bind
the textbox to the DetailDescription field. since the textbox is in the
footer, the textbox will show the contents of that field for the current
record, automatically.

hth
 
Thanks Tina,
putting some fields into a footer partially resolved my problem, however I
have a few ohter values I would like to show on the form (either in the body
or in the footer). The only difficulties is that these values do not come
from the same form's RecordSource - so I try to lookup these values in the
txtbox and setup a Control Source as for example" Select count(ID) from
tbTable1 WHERE ID=Forms!MyForm!frmID.

Can I do it like that (querry data from different tables/querris) than the
form is bound to?

Supicek

tina said:
assuming that the ID field is Table1's primary key, try the WHERE clause as

ID = [Forms]![FormName]!ID

but i don't see how that will help you unless the textbox is in a subform.
is the DetailDescription field in the same table that the form is bound to?
if so, you don't need a separate query at all. just make sure that the
DetailDescription field is included in the form's RecordSource, then bind
the textbox to the DetailDescription field. since the textbox is in the
footer, the textbox will show the contents of that field for the current
record, automatically.

hth


Supicek said:
Hi,
I have a continuos form which is based on an existing querry (Q1). At the
bottom of the form I would like to display addtitional info for selected
record (staying on the secon line I want to display DetailDescription for
ID2, etc.

ID - Name - shortDescription
-----------------------------
1 N1 D1
--------------------------
DetailDescription: Detail2

How should I construct the SQL querry for txtbox DetailDescription in order
to display a detail description for selected record (line2)?

SELECT DetDescr FROM Table1 WHERE ?????

thansk
Click to expand...
Click to expand...
 
I think I found where was my problem. I was trying to enter the SQL statement
into Control Source property, instead to insert it into RowSource

anyway Tina thanks for your reply

Supicek said:
Thanks Tina,
putting some fields into a footer partially resolved my problem, however I
have a few ohter values I would like to show on the form (either in the body
or in the footer). The only difficulties is that these values do not come
from the same form's RecordSource - so I try to lookup these values in the
txtbox and setup a Control Source as for example" Select count(ID) from
tbTable1 WHERE ID=Forms!MyForm!frmID.

Can I do it like that (querry data from different tables/querris) than the
form is bound to?

Supicek

tina said:
assuming that the ID field is Table1's primary key, try the WHERE clause as

ID = [Forms]![FormName]!ID

but i don't see how that will help you unless the textbox is in a subform.
is the DetailDescription field in the same table that the form is bound to?
if so, you don't need a separate query at all. just make sure that the
DetailDescription field is included in the form's RecordSource, then bind
the textbox to the DetailDescription field. since the textbox is in the
footer, the textbox will show the contents of that field for the current
record, automatically.

hth


Supicek said:
Hi,
I have a continuos form which is based on an existing querry (Q1). At the
bottom of the form I would like to display addtitional info for selected
record (staying on the secon line I want to display DetailDescription for
ID2, etc.

ID - Name - shortDescription
-----------------------------
1 N1 D1
2 N2 D2
--------------------------
DetailDescription: Detail2

How should I construct the SQL querry for txtbox DetailDescription in order
to display a detail description for selected record (line2)?

SELECT DetDescr FROM Table1 WHERE ?????

thansk
Click to expand...
Click to expand...
Click to expand...
 
you're welcome, and to answer your question (for future reference): if you
want to pull a value from a table that is not in the form's RecordSource,
and display it in a textbox control on the form, you can use a DLookUp()
function to retrieve a field value from either a table or a query. see the
DLookUp() Function topic in Access Help to learn the specifics of how it
works.

hth


Supicek said:
I think I found where was my problem. I was trying to enter the SQL statement
into Control Source property, instead to insert it into RowSource

anyway Tina thanks for your reply

Supicek said:
Thanks Tina,
putting some fields into a footer partially resolved my problem, however I
have a few ohter values I would like to show on the form (either in the body
or in the footer). The only difficulties is that these values do not come
from the same form's RecordSource - so I try to lookup these values in the
txtbox and setup a Control Source as for example" Select count(ID) from
tbTable1 WHERE ID=Forms!MyForm!frmID.

Can I do it like that (querry data from different tables/querris) than the
form is bound to?

Supicek

tina said:
assuming that the ID field is Table1's primary key, try the WHERE clause as

ID = [Forms]![FormName]!ID

but i don't see how that will help you unless the textbox is in a subform.
is the DetailDescription field in the same table that the form is bound to?
if so, you don't need a separate query at all. just make sure that the
DetailDescription field is included in the form's RecordSource, then bind
the textbox to the DetailDescription field. since the textbox is in the
footer, the textbox will show the contents of that field for the current
record, automatically.

hth


Hi,
I have a continuos form which is based on an existing querry (Q1). At the
bottom of the form I would like to display addtitional info for selected
record (staying on the secon line I want to display DetailDescription for
ID2, etc.

ID - Name - shortDescription
-----------------------------
1 N1 D1
2 N2 D2
--------------------------
DetailDescription: Detail2

How should I construct the SQL querry for txtbox DetailDescription in
order
to display a detail description for selected record (line2)?

SELECT DetDescr FROM Table1 WHERE ?????

thansk
Click to expand...
Click to expand...
Click to expand...
 
Back
Top