Dynamic Column Heading/Field Label

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

Guest

Hello,

I have a query as a data source into a Datasheet Form.

I am trying to have a field in one source of my query, that source being a
query, become the column heading or field label of a field in my other source
of the query, which is a table.

The syntax: [qryCriteriaHeadings]![CriteriaDesc1]:
[MasterCriteria]![CriteriaValue1] does not yield the result I expect, in
fact, I get an error saying [qryCriteriaHeadings]![CriteriaDesc1] is not a
valid name.

Is there a way to do this using QBE?

Thanks,
 
Duane,

Thanks. Any code samples you can provide for this?
--
David


Duane Hookom said:
I think you would need to use code to modify the SQL property of a saved
query.

--
Duane Hookom
MS Access MVP
--

David said:
Hello,

I have a query as a data source into a Datasheet Form.

I am trying to have a field in one source of my query, that source being a
query, become the column heading or field label of a field in my other
source
of the query, which is a table.

The syntax: [qryCriteriaHeadings]![CriteriaDesc1]:
[MasterCriteria]![CriteriaValue1] does not yield the result I expect, in
fact, I get an error saying [qryCriteriaHeadings]![CriteriaDesc1] is not a
valid name.

Is there a way to do this using QBE?

Thanks,
Click to expand...
Click to expand...
 
You can use DAO to modify the SQL property of a saved query. I use this
basic function in most of my MDBs. You would need to build your sql syntax
based on your needs and then use this function to update your saved query.

Sub ChangeSQL(pstrQuery As String, pstrSQL As String)
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb
Set qd = db.QueryDefs(pstrQuery)
qd.SQL = pstrSQL

Set qd = Nothing
Set db = Nothing

End Sub

--
Duane Hookom
MS Access MVP
--

David said:
Duane,

Thanks. Any code samples you can provide for this?
--
David


Duane Hookom said:
I think you would need to use code to modify the SQL property of a saved
query.

--
Duane Hookom
MS Access MVP
--

David said:
Hello,

I have a query as a data source into a Datasheet Form.

I am trying to have a field in one source of my query, that source
being a
query, become the column heading or field label of a field in my other
source
of the query, which is a table.

The syntax: [qryCriteriaHeadings]![CriteriaDesc1]:
[MasterCriteria]![CriteriaValue1] does not yield the result I expect,
in
fact, I get an error saying [qryCriteriaHeadings]![CriteriaDesc1] is
not a
valid name.

Is there a way to do this using QBE?

Thanks,
Click to expand...
Click to expand...
Click to expand...
 
Thanks Duane.

So, if I follow your thinking:

I pass the query name as pstrQuery
I pass the desired SQL syntax as pstrSQL?

Of course, I will have needed to create the SQL syntax prior to this call,
but I could then use the query as the datasource for the Datasheet form?



--
David


Duane Hookom said:
You can use DAO to modify the SQL property of a saved query. I use this
basic function in most of my MDBs. You would need to build your sql syntax
based on your needs and then use this function to update your saved query.

Sub ChangeSQL(pstrQuery As String, pstrSQL As String)
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb
Set qd = db.QueryDefs(pstrQuery)
qd.SQL = pstrSQL

Set qd = Nothing
Set db = Nothing

End Sub

--
Duane Hookom
MS Access MVP
--

David said:
Duane,

Thanks. Any code samples you can provide for this?
--
David


Duane Hookom said:
I think you would need to use code to modify the SQL property of a saved
query.

--
Duane Hookom
MS Access MVP
--

Hello,

I have a query as a data source into a Datasheet Form.

I am trying to have a field in one source of my query, that source
being a
query, become the column heading or field label of a field in my other
source
of the query, which is a table.

The syntax: [qryCriteriaHeadings]![CriteriaDesc1]:
[MasterCriteria]![CriteriaValue1] does not yield the result I expect,
in
fact, I get an error saying [qryCriteriaHeadings]![CriteriaDesc1] is
not a
valid name.

Is there a way to do this using QBE?

Thanks,
Click to expand...
Click to expand...
Click to expand...
 
You got it.
There is a sample MDB at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane which
shows how to dynamically set a subform to a query.

--
Duane Hookom
MS Access MVP
--

David said:
Thanks Duane.

So, if I follow your thinking:

I pass the query name as pstrQuery
I pass the desired SQL syntax as pstrSQL?

Of course, I will have needed to create the SQL syntax prior to this call,
but I could then use the query as the datasource for the Datasheet form?



--
David


Duane Hookom said:
You can use DAO to modify the SQL property of a saved query. I use this
basic function in most of my MDBs. You would need to build your sql
syntax
based on your needs and then use this function to update your saved
query.

Sub ChangeSQL(pstrQuery As String, pstrSQL As String)
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb
Set qd = db.QueryDefs(pstrQuery)
qd.SQL = pstrSQL

Set qd = Nothing
Set db = Nothing

End Sub

--
Duane Hookom
MS Access MVP
--

David said:
Duane,

Thanks. Any code samples you can provide for this?
--
David


:

I think you would need to use code to modify the SQL property of a
saved
query.

--
Duane Hookom
MS Access MVP
--

Hello,

I have a query as a data source into a Datasheet Form.

I am trying to have a field in one source of my query, that source
being a
query, become the column heading or field label of a field in my
other
source
of the query, which is a table.

The syntax: [qryCriteriaHeadings]![CriteriaDesc1]:
[MasterCriteria]![CriteriaValue1] does not yield the result I
expect,
in
fact, I get an error saying [qryCriteriaHeadings]![CriteriaDesc1] is
not a
valid name.

Is there a way to do this using QBE?

Thanks,
Click to expand...
Click to expand...
Click to expand...
 
Thanks. I'll give it a whirl.
--
David


Duane Hookom said:
You got it.
There is a sample MDB at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane which
shows how to dynamically set a subform to a query.

--
Duane Hookom
MS Access MVP
--

David said:
Thanks Duane.

So, if I follow your thinking:

I pass the query name as pstrQuery
I pass the desired SQL syntax as pstrSQL?

Of course, I will have needed to create the SQL syntax prior to this call,
but I could then use the query as the datasource for the Datasheet form?



--
David


Duane Hookom said:
You can use DAO to modify the SQL property of a saved query. I use this
basic function in most of my MDBs. You would need to build your sql
syntax
based on your needs and then use this function to update your saved
query.

Sub ChangeSQL(pstrQuery As String, pstrSQL As String)
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb
Set qd = db.QueryDefs(pstrQuery)
qd.SQL = pstrSQL

Set qd = Nothing
Set db = Nothing

End Sub

--
Duane Hookom
MS Access MVP
--

Duane,

Thanks. Any code samples you can provide for this?
--
David


:

I think you would need to use code to modify the SQL property of a
saved
query.

--
Duane Hookom
MS Access MVP
--

Hello,

I have a query as a data source into a Datasheet Form.

I am trying to have a field in one source of my query, that source
being a
query, become the column heading or field label of a field in my
other
source
of the query, which is a table.

The syntax: [qryCriteriaHeadings]![CriteriaDesc1]:
[MasterCriteria]![CriteriaValue1] does not yield the result I
expect,
in
fact, I get an error saying [qryCriteriaHeadings]![CriteriaDesc1] is
not a
valid name.

Is there a way to do this using QBE?

Thanks,
Click to expand...
Click to expand...
Click to expand...
 
Back
Top