Change subreport recordsource in VB

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

Guest

I want to run the following to change the recordsource of a subreport. The
parameters of firstqtr will change (in reality it is not hard coded).

How many things am I missing?

Also ,where do I put this? I thought it was in the report open, but ran
into problems.

code begin******************

private sub report_open()

Dim ssql1 As String
dim firstqtr as string

firstqtr = "20051"

ssql1 = "SELECT tblEVENT_LOG.txtAUDIT_ENTITY,
rtblACTION_TABLE.txtACTION," & _
"tblEVENT_LOG.memNOTES, tblEVENT_LOG.intRISK_SCORE " & _
"FROM tblEVENT_LOG " & _
"INNER JOIN rtblACTION_TABLE " & _
"ON tblEVENT_LOG.intACTION_NO = rtblACTION_TABLE.intACTION_NO "
& _
"WHERE ((([tblEVENT_LOG]![txtIMPACT_YEAR] & " & _
"[tblEVENT_LOG]![txtIMPACT_QTR])=" & "'" & firstqtr & "'" & "))"

MsgBox ssql1
Me!srptdetail_qtr_plus1.report.recordsource = ssql1


code end **********************************
 
Are you able to change the SQL property of the subreport's record source
query prior to opening the main report?
 
I can change anything I really want, this database is still in testing. The
subreport record source is simply a select query that I copied into this
ssql1 statement. The problem is, and why I tried to go this way, was that I
didn't know how to take the variable that I created in the VBA to the
existing query.

I might be to far to help.

Here is my problem. The user selects a year and a quarter. This is the
baseline month. I want to select records that took place in the next quarter
after the baseline month. This is the variable firstqtr, and I have some VBA
that creates this variable. However, I don't know how to pass this variable
to the query itself. So when you ask your question, how would I do that
without changing the subreport recordsource and change the query itself?

I hope that makes sense, I am a little tired.
--
Ficticiously Yours, Biggles


Duane Hookom said:
Are you able to change the SQL property of the subreport's record source
query prior to opening the main report?

--
Duane Hookom
MS Access MVP


Biggles said:
I want to run the following to change the recordsource of a subreport. The
parameters of firstqtr will change (in reality it is not hard coded).

How many things am I missing?

Also ,where do I put this? I thought it was in the report open, but ran
into problems.

code begin******************

private sub report_open()

Dim ssql1 As String
dim firstqtr as string

firstqtr = "20051"

ssql1 = "SELECT tblEVENT_LOG.txtAUDIT_ENTITY,
rtblACTION_TABLE.txtACTION," & _
"tblEVENT_LOG.memNOTES, tblEVENT_LOG.intRISK_SCORE " & _
"FROM tblEVENT_LOG " & _
"INNER JOIN rtblACTION_TABLE " & _
"ON tblEVENT_LOG.intACTION_NO = rtblACTION_TABLE.intACTION_NO "
& _
"WHERE ((([tblEVENT_LOG]![txtIMPACT_YEAR] & " & _
"[tblEVENT_LOG]![txtIMPACT_QTR])=" & "'" & firstqtr & "'" &
"))"

MsgBox ssql1
Me!srptdetail_qtr_plus1.report.recordsource = ssql1


code end **********************************
Click to expand...
Click to expand...
 
Assuming your subreport's record source query is "qselForSubRpt" you could
use code like:

CurrentDb.QueryDefs("qselForSubRpt").SQL = "SELECT
tblEVENT_LOG.txtAUDIT_ENTITY, .."

--
Duane Hookom
MS Access MVP


Biggles said:
I can change anything I really want, this database is still in testing.
The
subreport record source is simply a select query that I copied into this
ssql1 statement. The problem is, and why I tried to go this way, was that
I
didn't know how to take the variable that I created in the VBA to the
existing query.

I might be to far to help.

Here is my problem. The user selects a year and a quarter. This is the
baseline month. I want to select records that took place in the next
quarter
after the baseline month. This is the variable firstqtr, and I have some
VBA
that creates this variable. However, I don't know how to pass this
variable
to the query itself. So when you ask your question, how would I do that
without changing the subreport recordsource and change the query itself?

I hope that makes sense, I am a little tired.
--
Ficticiously Yours, Biggles


Duane Hookom said:
Are you able to change the SQL property of the subreport's record source
query prior to opening the main report?

--
Duane Hookom
MS Access MVP


Biggles said:
I want to run the following to change the recordsource of a subreport.
The
parameters of firstqtr will change (in reality it is not hard coded).

How many things am I missing?

Also ,where do I put this? I thought it was in the report open, but
ran
into problems.

code begin******************

private sub report_open()

Dim ssql1 As String
dim firstqtr as string

firstqtr = "20051"

ssql1 = "SELECT tblEVENT_LOG.txtAUDIT_ENTITY,
rtblACTION_TABLE.txtACTION," & _
"tblEVENT_LOG.memNOTES, tblEVENT_LOG.intRISK_SCORE "
& _
"FROM tblEVENT_LOG " & _
"INNER JOIN rtblACTION_TABLE " & _
"ON tblEVENT_LOG.intACTION_NO =
rtblACTION_TABLE.intACTION_NO "
& _
"WHERE ((([tblEVENT_LOG]![txtIMPACT_YEAR] & " & _
"[tblEVENT_LOG]![txtIMPACT_QTR])=" & "'" & firstqtr & "'" &
"))"

MsgBox ssql1
Me!srptdetail_qtr_plus1.report.recordsource = ssql1


code end **********************************
Click to expand...
Click to expand...
Click to expand...
 
Duane

Thanks, that worked quite well, now I need some help on the next step. The
reason I needed some of this is that I have four sub reports that are
identical, each pulling from a different query, which are identical except
for the filter, hence the sql statement. So what I did was to create four
statements like what you suggested, using the currentdb.querydefs. That
works great, but is not elegant.

Ok, now I want to do this in a for next loop. If I run this, I don't get
the ssql statement to get the value of qtr1, just qtr1. Is there something
else I need?

QTR1 = '20051'
QTR2 = '20052'
QTR3 = '20053'
QTR4 = '20054'

For x = 1 To 4
xqtr = "qtr" & x
MsgBox xqtr
queryname = "qryrpt_detail_qtr_plus" & x
MsgBox queryname
ssql = "SELECT tblEVENT_LOG.txtAUDIT_ENTITY,
rtblACTION_TABLE.txtACTION," & _
"tblEVENT_LOG.memNOTES, tblEVENT_LOG.intRISK_SCORE " & _
"FROM tblEVENT_LOG " & _
"INNER JOIN rtblACTION_TABLE " & _
"ON tblEVENT_LOG.intACTION_NO = rtblACTION_TABLE.intACTION_NO "
& _
"WHERE ((([tblEVENT_LOG]![txtIMPACT_YEAR] & " & _
"[tblEVENT_LOG]![txtIMPACT_QTR])=" & xqtr & "))"
MsgBox ssql
CurrentDb.QueryDefs(queryname).SQL = ssql
Next x

The variable xqtr is converting to qtr1 like I half want, but I want it to
convert to '20051'

Hope this makes some sense
--
Ficticiously Yours, Biggles


Duane Hookom said:
Assuming your subreport's record source query is "qselForSubRpt" you could
use code like:

CurrentDb.QueryDefs("qselForSubRpt").SQL = "SELECT
tblEVENT_LOG.txtAUDIT_ENTITY, .."

--
Duane Hookom
MS Access MVP


Biggles said:
I can change anything I really want, this database is still in testing.
The
subreport record source is simply a select query that I copied into this
ssql1 statement. The problem is, and why I tried to go this way, was that
I
didn't know how to take the variable that I created in the VBA to the
existing query.

I might be to far to help.

Here is my problem. The user selects a year and a quarter. This is the
baseline month. I want to select records that took place in the next
quarter
after the baseline month. This is the variable firstqtr, and I have some
VBA
that creates this variable. However, I don't know how to pass this
variable
to the query itself. So when you ask your question, how would I do that
without changing the subreport recordsource and change the query itself?

I hope that makes sense, I am a little tired.
--
Ficticiously Yours, Biggles


Duane Hookom said:
Are you able to change the SQL property of the subreport's record source
query prior to opening the main report?

--
Duane Hookom
MS Access MVP


I want to run the following to change the recordsource of a subreport.
The
parameters of firstqtr will change (in reality it is not hard coded).

How many things am I missing?

Also ,where do I put this? I thought it was in the report open, but
ran
into problems.

code begin******************

private sub report_open()

Dim ssql1 As String
dim firstqtr as string

firstqtr = "20051"

ssql1 = "SELECT tblEVENT_LOG.txtAUDIT_ENTITY,
rtblACTION_TABLE.txtACTION," & _
"tblEVENT_LOG.memNOTES, tblEVENT_LOG.intRISK_SCORE "
& _
"FROM tblEVENT_LOG " & _
"INNER JOIN rtblACTION_TABLE " & _
"ON tblEVENT_LOG.intACTION_NO =
rtblACTION_TABLE.intACTION_NO "
& _
"WHERE ((([tblEVENT_LOG]![txtIMPACT_YEAR] & " & _
"[tblEVENT_LOG]![txtIMPACT_QTR])=" & "'" & firstqtr & "'" &
"))"

MsgBox ssql1
Me!srptdetail_qtr_plus1.report.recordsource = ssql1


code end **********************************
Click to expand...
Click to expand...
Click to expand...
 
A couple options:
Use an array for the quarter or
"[tblEVENT_LOG]![txtIMPACT_QTR])='2005" & x & "'))"
This assumes txtIMPACT_QTR is text.

--
Duane Hookom
MS Access MVP


Biggles said:
Duane

Thanks, that worked quite well, now I need some help on the next step.
The
reason I needed some of this is that I have four sub reports that are
identical, each pulling from a different query, which are identical except
for the filter, hence the sql statement. So what I did was to create four
statements like what you suggested, using the currentdb.querydefs. That
works great, but is not elegant.

Ok, now I want to do this in a for next loop. If I run this, I don't get
the ssql statement to get the value of qtr1, just qtr1. Is there
something
else I need?

QTR1 = '20051'
QTR2 = '20052'
QTR3 = '20053'
QTR4 = '20054'

For x = 1 To 4
xqtr = "qtr" & x
MsgBox xqtr
queryname = "qryrpt_detail_qtr_plus" & x
MsgBox queryname
ssql = "SELECT tblEVENT_LOG.txtAUDIT_ENTITY,
rtblACTION_TABLE.txtACTION," & _
"tblEVENT_LOG.memNOTES, tblEVENT_LOG.intRISK_SCORE " &
_
"FROM tblEVENT_LOG " & _
"INNER JOIN rtblACTION_TABLE " & _
"ON tblEVENT_LOG.intACTION_NO = rtblACTION_TABLE.intACTION_NO
"
& _
"WHERE ((([tblEVENT_LOG]![txtIMPACT_YEAR] & " & _
"[tblEVENT_LOG]![txtIMPACT_QTR])=" & xqtr & "))"
MsgBox ssql
CurrentDb.QueryDefs(queryname).SQL = ssql
Next x

The variable xqtr is converting to qtr1 like I half want, but I want it to
convert to '20051'

Hope this makes some sense
--
Ficticiously Yours, Biggles


Duane Hookom said:
Assuming your subreport's record source query is "qselForSubRpt" you
could
use code like:

CurrentDb.QueryDefs("qselForSubRpt").SQL = "SELECT
tblEVENT_LOG.txtAUDIT_ENTITY, .."

--
Duane Hookom
MS Access MVP


Biggles said:
I can change anything I really want, this database is still in testing.
The
subreport record source is simply a select query that I copied into
this
ssql1 statement. The problem is, and why I tried to go this way, was
that
I
didn't know how to take the variable that I created in the VBA to the
existing query.

I might be to far to help.

Here is my problem. The user selects a year and a quarter. This is
the
baseline month. I want to select records that took place in the next
quarter
after the baseline month. This is the variable firstqtr, and I have
some
VBA
that creates this variable. However, I don't know how to pass this
variable
to the query itself. So when you ask your question, how would I do
that
without changing the subreport recordsource and change the query
itself?

I hope that makes sense, I am a little tired.
--
Ficticiously Yours, Biggles


:

Are you able to change the SQL property of the subreport's record
source
query prior to opening the main report?

--
Duane Hookom
MS Access MVP


I want to run the following to change the recordsource of a
subreport.
The
parameters of firstqtr will change (in reality it is not hard
coded).

How many things am I missing?

Also ,where do I put this? I thought it was in the report open, but
ran
into problems.

code begin******************

private sub report_open()

Dim ssql1 As String
dim firstqtr as string

firstqtr = "20051"

ssql1 = "SELECT tblEVENT_LOG.txtAUDIT_ENTITY,
rtblACTION_TABLE.txtACTION," & _
"tblEVENT_LOG.memNOTES, tblEVENT_LOG.intRISK_SCORE
"
& _
"FROM tblEVENT_LOG " & _
"INNER JOIN rtblACTION_TABLE " & _
"ON tblEVENT_LOG.intACTION_NO =
rtblACTION_TABLE.intACTION_NO "
& _
"WHERE ((([tblEVENT_LOG]![txtIMPACT_YEAR] & " & _
"[tblEVENT_LOG]![txtIMPACT_QTR])=" & "'" & firstqtr & "'"
&
"))"

MsgBox ssql1
Me!srptdetail_qtr_plus1.report.recordsource = ssql1


code end **********************************
Click to expand...
Click to expand...
Click to expand...
 
Never mind, figured out what was causing me problems, I moved the definition
of what I wanted xqtr to actually represent into the for next loop as well,
easy since it was actually running through the four quarters.

I will still want to know how to use just one query and one subreport to
accomplish this, but, I am still flush from victory, so I will ask later.
 
Back
Top