Syntax for obtaining a value from one subform to another

[Susan L]

I should be able to figure this out, but...so far no luck with the syntax.

I have a main report that contains several subreports. On one subreport
(subrpt1), there's a value (-1,0) in a control called NoAutoInterface. I want
an unbound text box in the footer on subrpt2 to use that value to determine
whether a message should appear. The following syntax (among others I've
tried) is not working.

=IIf([Reports]![mainrpt].[subrpt1].[Report]![NoAutoInterface]=-1,"This state
has requested no automated interfaces for testing.","")

Any suggestions?

 

[Marshall Barton]

I should be able to figure this out, but...so far no luck with the syntax.

I have a main report that contains several subreports. On one subreport
(subrpt1), there's a value (-1,0) in a control called NoAutoInterface. I want
an unbound text box in the footer on subrpt2 to use that value to determine
whether a message should appear. The following syntax (among others I've
tried) is not working.

=IIf([Reports]![mainrpt].[subrpt1].[Report]![NoAutoInterface]=-1,"This state
has requested no automated interfaces for testing.","")

I don't think you can reliably make one subreport dependent
on another subreport. Your posted syntax appears to be what
would be correct if you could do that kind of thing.

Is there any way you can use a main report text box instead?

 

[Susan L]

First, I tried incorporating the NoAutoInterface field into the query on
which the second report is based, but that didn't work. The two tables
involved have a one (NoAutoInterface's table) to many (table on which subrpt2
is based) relationship, and the field list did not show the NoAutoInterface
field.

The main report is based on a query in code in the on open event of the
report. It gets its value from a form where the state is selected (and passed
to gstrState).
strSql = "SELECT State, FIPS FROM tbl_List_States WHERE State = """ &
gstrState & """"
Me.RecordSource = strSql

NoAutoInterface is in a different table (tbl_StateInfo); the tables would be
joined on the field State. I confess that I'm not sure how to write the query
to incorporate the NoAutoInterface field.


--
susan

I should be able to figure this out, but...so far no luck with the syntax.

I have a main report that contains several subreports. On one subreport
(subrpt1), there's a value (-1,0) in a control called NoAutoInterface. I want
an unbound text box in the footer on subrpt2 to use that value to determine
whether a message should appear. The following syntax (among others I've
tried) is not working.

=IIf([Reports]![mainrpt].[subrpt1].[Report]![NoAutoInterface]=-1,"This state
has requested no automated interfaces for testing.","")

I don't think you can reliably make one subreport dependent
on another subreport. Your posted syntax appears to be what
would be correct if you could do that kind of thing.

Is there any way you can use a main report text box instead?

 

[Marshall Barton]

First, I tried incorporating the NoAutoInterface field into the query on
which the second report is based, but that didn't work. The two tables
involved have a one (NoAutoInterface's table) to many (table on which subrpt2
is based) relationship, and the field list did not show the NoAutoInterface
field.

The main report is based on a query in code in the on open event of the
report. It gets its value from a form where the state is selected (and passed
to gstrState).
strSql = "SELECT State, FIPS FROM tbl_List_States WHERE State = """ &
gstrState & """"
Me.RecordSource = strSql

NoAutoInterface is in a different table (tbl_StateInfo); the tables would be
joined on the field State. I confess that I'm not sure how to write the query
to incorporate the NoAutoInterface field.

This is a likely way to write the query:

strSql = "SELECT tbl_List_States.State, FIPS " _
& "FROM tbl_List_States INNER JOIN tbl_StateInfo " _
& " ON tbl_List_States.State = tbl_StateInfo.State " _
& "WHERE tbl_List_States.State = """ & gstrState & """"

If you want records from the states table that have no
matching records in the info table, then change the INNER to
LEFT.

 

[Susan L]

Marsh: Thanks so much for your response. However, when I looked more closely
at the StateInfo table, I realized that I had all the data needed in that one
table, so there was no need for joining the two tables. I also decided to
build a standing query rather than put the record source in the On Open
event, primarily to make the record source easier for others to identify.

My original question has been answered: I now have the NoAutoInterface on
the main report and have used the syntax Me.Parent!NoAutoInterface on the
subform to access the data needed. This part is working.

However now, my question has changed. What I can't get to work is the
reference to my public variable "gstrState" to pull the state's name from a
control on a form. I tried putting "gstrState" in the Criteria row, but
received an error. I suppose I could put a reference to the form in the
criteria, but I'd really like to use the variable. Any suggestions?

 

[Marshall Barton]

Marsh: Thanks so much for your response. However, when I looked more closely
at the StateInfo table, I realized that I had all the data needed in that one
table, so there was no need for joining the two tables. I also decided to
build a standing query rather than put the record source in the On Open
event, primarily to make the record source easier for others to identify.

My original question has been answered: I now have the NoAutoInterface on
the main report and have used the syntax Me.Parent!NoAutoInterface on the
subform to access the data needed. This part is working.

However now, my question has changed. What I can't get to work is the
reference to my public variable "gstrState" to pull the state's name from a
control on a form. I tried putting "gstrState" in the Criteria row, but
received an error. I suppose I could put a reference to the form in the
criteria, but I'd really like to use the variable. Any suggestions?

You can only reference a VBA variable from VBA code. If
necessary, you can define a Public Function that returns the
value of the variable. It seems like it would be simpler to
just use a reference to the form text box instead.

If all you want to do is filter the report's data when you
open the report, then the preferred approach is to leave the
criteria out of the query and use the OpenReport method's
WhereCondition argument to apply the criteria. The code to
open the report could be like:

DoCmd.OpenReport "report name", avViewPreview, _
WhereCondition:= "state=" & Me.txtState

 

[Susan L]

Thanks for the advice. I'll give it a shot.