report and VBA Query

[ismail]

Please help in this issue...
i used to make reports by wizard...but now....???
i would like to make report...and the query is in coding..ie VBA query..
how to connect this query to my report and get the fields...into the reprt
if u give me sample code ...better for me....

Thanks and Regards

 

[Marshall Barton]

i used to make reports by wizard...but now....???
i would like to make report...and the query is in coding..ie VBA query..
how to connect this query to my report and get the fields...into the reprt
if u give me sample code ...better for me....

A query is an SQL Statement, regardless of where/how you use
it. Even the query design grid is just a user inteface that
makes it convenient to specify the elements of a query, but
behind the scenes, Access uses the grid info to construct an
SQL statement. What all that is leading up to is that you
have to embed your "VBA query" as an SQL statement in the
context of a VBA procedure. Example:

Dim strSQL As String
strSQL = "SELECT f1, f2, ... FROM table WHERE ..."
Me.RecordSource = strSQL

Note that a report's RecordSource property can only be set
in the report's Open event procedure.

 

[ismail]

thanks for explaining.......the things...u see normally i assign record
source and
then i get fields..i drag into reprt..i adjust...but...now...i have sql
string in my report
open event...then how can i use different fields in my report

expecting ur reply thanks..............

 

[Marshall Barton]

Normally, a report is design to work with a specific set of
fields and the query is required to supply that set of
fields.

If you do not know what fields will be available to the
report, then you are into an extremely advanced programming
problem, that, from the tone of your question, I don't think
you're ready for.

Quite probably you do not need to get involved with the
general problem, so I suggest that you explain speciifically
what you are trying to do. Then we'll see if there is a
specific solution that may avoid all the generalities that
you are asking so far.

 

[ismail]

please note this query..........


Const SQL = "SELECT petrol,dateofS,stid,totalS FROM details where stid="

Dim strSQL As String
strSQL = Replace(SQL, "petrol", Forms![searchfrm]![activitycmb])
strSQL = strSQL & " " & Forms![searchfrm]![stidcmb].Value & ""

i will select values for activitycmb and stidcmb through combo box...

then i will have record which has petrol.dateofs,stid,totalS........


now how can i display into the report....this is my Question...expecting ur
reply...

Regards

Normally, a report is design to work with a specific set of
fields and the query is required to supply that set of
fields.

If you do not know what fields will be available to the
report, then you are into an extremely advanced programming
problem, that, from the tone of your question, I don't think
you're ready for.

Quite probably you do not need to get involved with the
general problem, so I suggest that you explain speciifically
what you are trying to do. Then we'll see if there is a
specific solution that may avoid all the generalities that
you are asking so far.
--
Marsh
MVP [MS Access]

thanks for explaining.......the things...u see normally i assign record
source and
then i get fields..i drag into reprt..i adjust...but...now...i have sql
string in my report
open event...then how can i use different fields in my report

 

[Marshall Barton]

Ahhh, much easier than the general problem. Just set the
appropriate text box's ControlSource property:

Dim strSQL As String
strSQL = "SELECT " & Forms!searchfrm!activitycmb & _
",dateofS,stid,totalS FROM details WHERE stid=" & _
Forms!searchfrm!stidcmb
Me.thetextbox.ControlSource = Forms!searchfrm!activitycmb

There would be a little more if you have a footer section
with a total text box on the specified field.
--
Marsh
MVP [MS Access]

Const SQL = "SELECT petrol,dateofS,stid,totalS FROM details where stid="

Dim strSQL As String
strSQL = Replace(SQL, "petrol", Forms![searchfrm]![activitycmb])
strSQL = strSQL & " " & Forms![searchfrm]![stidcmb].Value & ""

i will select values for activitycmb and stidcmb through combo box...
then i will have record which has petrol.dateofs,stid,totalS........
now how can i display into the report

Normally, a report is design to work with a specific set of
fields and the query is required to supply that set of
fields.

If you do not know what fields will be available to the
report, then you are into an extremely advanced programming
problem, that, from the tone of your question, I don't think
you're ready for.

Quite probably you do not need to get involved with the
general problem, so I suggest that you explain speciifically
what you are trying to do. Then we'll see if there is a
specific solution that may avoid all the generalities that
you are asking so far.

 

[ismail]

Oooo..!!! Please ....can i display the other fields..from the
query(petrol,dateofS,stid,totalS ....etc) on the report...


Thanks

Ahhh, much easier than the general problem. Just set the
appropriate text box's ControlSource property:

Dim strSQL As String
strSQL = "SELECT " & Forms!searchfrm!activitycmb & _
",dateofS,stid,totalS FROM details WHERE stid=" & _
Forms!searchfrm!stidcmb
Me.thetextbox.ControlSource = Forms!searchfrm!activitycmb

There would be a little more if you have a footer section
with a total text box on the specified field.
--
Marsh
MVP [MS Access]

Const SQL = "SELECT petrol,dateofS,stid,totalS FROM details where stid="

Dim strSQL As String
strSQL = Replace(SQL, "petrol", Forms![searchfrm]![activitycmb])
strSQL = strSQL & " " & Forms![searchfrm]![stidcmb].Value & ""

i will select values for activitycmb and stidcmb through combo box...
then i will have record which has petrol.dateofs,stid,totalS........
now how can i display into the report

Normally, a report is design to work with a specific set of
fields and the query is required to supply that set of
fields.

If you do not know what fields will be available to the
report, then you are into an extremely advanced programming
problem, that, from the tone of your question, I don't think
you're ready for.

Quite probably you do not need to get involved with the
general problem, so I suggest that you explain speciifically
what you are trying to do. Then we'll see if there is a
specific solution that may avoid all the generalities that
you are asking so far.


ismail wrote:
thanks for explaining.......the things...u see normally i assign record
source and
then i get fields..i drag into reprt..i adjust...but...now...i have sql
string in my report
open event...then how can i use different fields in my report


ismail wrote:
i used to make reports by wizard...but now....???
i would like to make report...and the query is in coding..ie VBA query..
how to connect this query to my report and get the fields...into the reprt
if u give me sample code ...better for me....


:
A query is an SQL Statement, regardless of where/how you use
it. Even the query design grid is just a user inteface that
makes it convenient to specify the elements of a query, but
behind the scenes, Access uses the grid info to construct an
SQL statement. What all that is leading up to is that you
have to embed your "VBA query" as an SQL statement in the
context of a VBA procedure. Example:

Dim strSQL As String
strSQL = "SELECT f1, f2, ... FROM table WHERE ..."
Me.RecordSource = strSQL

Note that a report's RecordSource property can only be set
in the report's Open event procedure.

 

[Marshall Barton]

I don't understand what the difficulty is. Why can't you
just bind text boxes to those fields? The query is used as
the report's record source, isn't it?
--
Marsh
MVP [MS Access]

Oooo..!!! Please ....can i display the other fields..from the
query(petrol,dateofS,stid,totalS ....etc) on the report...

Ahhh, much easier than the general problem. Just set the
appropriate text box's ControlSource property:

Dim strSQL As String
strSQL = "SELECT " & Forms!searchfrm!activitycmb & _
",dateofS,stid,totalS FROM details WHERE stid=" & _
Forms!searchfrm!stidcmb
Me.thetextbox.ControlSource = Forms!searchfrm!activitycmb

There would be a little more if you have a footer section
with a total text box on the specified field.

Const SQL = "SELECT petrol,dateofS,stid,totalS FROM details where stid="

Dim strSQL As String
strSQL = Replace(SQL, "petrol", Forms![searchfrm]![activitycmb])
strSQL = strSQL & " " & Forms![searchfrm]![stidcmb].Value & ""

i will select values for activitycmb and stidcmb through combo box...
then i will have record which has petrol.dateofs,stid,totalS........
now how can i display into the report