syntax for creating a QBF from a tab control on a subform

[Guest]

I have an unboound navigation form -- a tab control -- (frmMainTab) that has
a subform (sfrmTab2) on one of its tabs, that is itself a tab control
(TabCtrl2). On one of it's tabs (StateReports, index #2), I have placed a
combo box (cboSelectState) to select a US state. I want to put a criteria
statement into several queries that will pull that state name, and allow the
users to generate reports listed on the StateReports tab.

I've tried Forms![frmMainTab]![sfrmTab2]![cboSelectState]
Forms![frmMainTab]![TabCtrl2]!Forms![cboSelectState]

and several other varations. The first one brings up an empty report with an
"Error" showing where the state name appears, the other variations generate a
parameter message. If I type a state name in the parameter box, the report
does appear, with the correct information. I've searched, but have not found
the answer to my question. I have a feeling that the problem is something to
do with the tab control. Any help you can give would be appreciated.

 

[Rod]

To reference a control on a subform you have to use the syntax

Forms!MainForm.SubformName.Form.ControlName

(You can probably use ! instead of ., the dot syntax seems to work for me.
I think the 'proper' usage is to use ! preceding object names you have
created and . preceding object names native to Access.)

So in this case try

Forms!frmMainTab.sfmTab2.Form.cboSelectState or perhaps properly
Forms!frmMainTab!sfmTab2.Form!cboSelectState

If this doesn't work you may have to construct the RecordSource for the
Report(s) dynamically.

Rod.

 

[Guest]

Thanks for your response, Rod. I tried the syntax you suggested with the form
name and got a parameter box with the statement in it. When I typed a state
name there, the report came up as it should. Then I tried it with the name of
the control that holds the subform. That time -- no parameter message -- but
the report opened blank with "Error" where the state name should appear.

So now I suppose I'll need to try running the query dynamically. By that I
suppose you mean put the query in the code for the command button that calls
up the report. I'm a little "green" at this -- what syntax would I use for
the WHERE clause to pull the state name from the combo box. Since the command
button is on the subform, there should be no need to refer to the main form.
Would it be something like WHERE MyTableName.State = Me!cboSelectState?
--
susan

To reference a control on a subform you have to use the syntax

Forms!MainForm.SubformName.Form.ControlName

(You can probably use ! instead of ., the dot syntax seems to work for me.
I think the 'proper' usage is to use ! preceding object names you have
created and . preceding object names native to Access.)

So in this case try

Forms!frmMainTab.sfmTab2.Form.cboSelectState or perhaps properly
Forms!frmMainTab!sfmTab2.Form!cboSelectState

If this doesn't work you may have to construct the RecordSource for the
Report(s) dynamically.

Rod.

I have an unboound navigation form -- a tab control -- (frmMainTab) that
has
a subform (sfrmTab2) on one of its tabs, that is itself a tab control
(TabCtrl2). On one of it's tabs (StateReports, index #2), I have placed a
combo box (cboSelectState) to select a US state. I want to put a criteria
statement into several queries that will pull that state name, and allow
the
users to generate reports listed on the StateReports tab.

I've tried Forms![frmMainTab]![sfrmTab2]![cboSelectState]
Forms![frmMainTab]![TabCtrl2]!Forms![cboSelectState]

and several other varations. The first one brings up an empty report with
an
"Error" showing where the state name appears, the other variations
generate a
parameter message. If I type a state name in the parameter box, the report
does appear, with the correct information. I've searched, but have not
found
the answer to my question. I have a feeling that the problem is something
to
do with the tab control. Any help you can give would be appreciated.

 

[Guest]

Found information in other posts re referring to tab control's page
collection. So here's what I now have (but which is not working). This is in
the criteria line of the query underlying the report that is to be generated.
These are the actual names. I've tried moving "Form" from before "Pages" to
after the "Pages" reference, but get the same error. What happens is that I
get a parameter dialog showing the statement below.

[Forms]![frm_Menu_Main]![frm_Menu_Sub_Reports]![Form]![Pages
(0)].[cboSelectState]

If someone can straighten me out, I would reallly appreciate it. Thanks.
--
susan

Thanks for your response, Rod. I tried the syntax you suggested with the form
name and got a parameter box with the statement in it. When I typed a state
name there, the report came up as it should. Then I tried it with the name of
the control that holds the subform. That time -- no parameter message -- but
the report opened blank with "Error" where the state name should appear.

So now I suppose I'll need to try running the query dynamically. By that I
suppose you mean put the query in the code for the command button that calls
up the report. I'm a little "green" at this -- what syntax would I use for
the WHERE clause to pull the state name from the combo box. Since the command
button is on the subform, there should be no need to refer to the main form.
Would it be something like WHERE MyTableName.State = Me!cboSelectState?
--
susan

To reference a control on a subform you have to use the syntax

Forms!MainForm.SubformName.Form.ControlName

(You can probably use ! instead of ., the dot syntax seems to work for me.
I think the 'proper' usage is to use ! preceding object names you have
created and . preceding object names native to Access.)

So in this case try

Forms!frmMainTab.sfmTab2.Form.cboSelectState or perhaps properly
Forms!frmMainTab!sfmTab2.Form!cboSelectState

If this doesn't work you may have to construct the RecordSource for the
Report(s) dynamically.

Rod.

I have an unboound navigation form -- a tab control -- (frmMainTab) that
has
a subform (sfrmTab2) on one of its tabs, that is itself a tab control
(TabCtrl2). On one of it's tabs (StateReports, index #2), I have placed a
combo box (cboSelectState) to select a US state. I want to put a criteria
statement into several queries that will pull that state name, and allow
the
users to generate reports listed on the StateReports tab.

I've tried Forms![frmMainTab]![sfrmTab2]![cboSelectState]
Forms![frmMainTab]![TabCtrl2]!Forms![cboSelectState]

and several other varations. The first one brings up an empty report with
an
"Error" showing where the state name appears, the other variations
generate a
parameter message. If I type a state name in the parameter box, the report
does appear, with the correct information. I've searched, but have not
found
the answer to my question. I have a feeling that the problem is something
to
do with the tab control. Any help you can give would be appreciated.

 

[Rod]

Susan, I tried to send this a couple of days ago but my ISP server didn't
forward it.

You have just about got it right.

I am surprised that the reference to the control didn't work. I would be
interested to see the recordsource of the report as it is at present. But
to the other method ...

Dont be put off by the length of the response .Please forgive me when I
write things you already know. I figured it is better to assume too little
than assume too much.

There are a number of ways to dynamically construct the RecordSouce.

I think the easiest way to do this is create a global variable to hold the
state name. A global variable is accessible from all parts of the database
code. You do this by adding it to a code module. If you don't have one
already select Modules from the list of Objects in the database window and
click New.

This will open a code window named Module1.

On a new line enter the code

Public gstrState As String

(the g reminds us that it is a global variable and the str reminds us it
contains a string)

Save the module by clicking the Save icon. You can name it anything, even
leave it as Module1.

The variable strState now can be used in any Form or Report and will keep
the same value in any of them.

Open the subform sfmTab2 in Design view, select the combobox cboSelectState,
right click and select Properties. (You can also click the pointing hand
icon on the Menu Bar.)

Select the Event tab and find the row labelled After Update. Click in that
row and you will see a button appear at the end of the row with three dots
(elipsis) in it. Click that button. You will probably get a dialog box
with three options, one of which is Code. Select this option.

You will then be taken to the form module code where

Private Sub cboSelectState_AfterUpdate()

End Sub

should appear.

Within those lines enter the statement

gstrState = cboSelectState

Save the changes.

This assumes that the only data that occurs in cboSelectState is the State
name. (If this isn't true then cboSelectState may not have the value of the
State at all - it would explain why the earlier code didn't work.) To check
this select cboSelectState, right click and open the Properties dialog.
Select the format tab. The value in Column Count should be one. Then
select the Data tab. The value in Bound Column should also be one. If
either of these are not one then we need to look at the RowSource for the
cboSelectState combobox.

Assuming the Bound Column contains the State the code entered above will set
the variable to the value of the selected state whenever that is changed.

Now we have to set the RecordSource of the report.

Open the report in Design View.

Open the Properties dialog. (Just the way you did above.) The properties
dialog should have the word Report in the Title bar. If not click the
little grey square in the top left corner of the report form.

Select the Event tab of the Properties dialog.

Click in the On Open row and click the button with the elipsis. Once again
select Code. You will see:

Private Sub Report_Open(Cancel As Integer)

End Sub

This is where you have to put the code to 'filter' the report.

We set the report's recordsource.

Me.RecordSource = "SELECT field list FROM table/query"

You can see the existing RecordSource by selecting the Data tab of the
Properties dialog and looking at the contents of the Record Source row.

You need to add a WHERE clause to the SQL statement as you suggested in your
post.

Following the SELECT clause add " WHERE MyTableName.State = """ & gstrState
& """"

(& is the VBA operator to join strings..)

So the final statement will look something like

Me.RecordSource = "SELECT field list FROM table/query WHERE
MyTableName.State = """ & gstrState & """"

We need to enclose the state represented by gstrState in quotation marks.
Since these are themselves contained within quotation marks we have to use
double quotes "" to represent a single quotation mark, hence the three quote
marks after 'State =' and the four quote marks after 'gstrState &'.

Save the report. It should now give the desired results.

 

[Guest]

Rod: thanks ever so much for your detailed explanation. Not only good for me,
but for anyone else who is looking for solutions.

However, I’m getting a Syntax error in the SQL statement. Here’s what I did:
Created a module called GlobalVariables (I’ll want to put others in there
because I love your approach) and put gstrState in the module.

In the After Update of the State combo box, I put gstrState = cboSelectState.

And here’s the SQL in the form’s On Open event:
Me.RecordSource = "SELECT State FROM tbl_List_States WHERE _
State = """ & gstrState & """"

This is the line that generates the “Syntax error.†Any thoughts?

Also, this report has several subreports. Do I need to put that SQL in those
as well? Or will the Master/Child relationship take care of it?

 

[Rod]

Susan,

The code looks fine. Is State the only field that appears on the report?

Try adding

Dim strSQL as String

to the Form_Open sub and then

strSQL = "SELECT State FROM tbl_List_States WHERE State = """ &
gstrState & """"
Me.RecordSource = strSQL

Put a breakpoint on the Me.RecordSource line. (You do that by selecting the
line and pressing F9 or by clicking in the left hand border of the code
window next to the line. You will see a red-brown dot appear in the
border.)

Run the program and it will stop at the breakpoint.

You can use the bottom part of the code window (the intermediate pane) to
look at the value of any variables.

Type ? strSQL and press enter. This will give you the exact SQL that is
being executed in the particular case.

If you can't see a problem immediately:
copy the output and create a new query.
close the dialog offering the tables/queries
select the SQL view from the view dropdown on the left of the menu bar
paste the output from strSQL into the SQL window making sure that the
statement ends with a ';'
run the query.

If an error exists in the query it will show up here and indicate where the
problem lies.

If there is no problem in the query, close the query, don't save it and
return to the breakpoint.

Press F8 and you will step line by line through the code until the error
occurs.

This may give you more information about the error.

Good luck, let me know how you go.

Rod.

 

[Guest]

Once again, I so appreciate your input. You're also teaching me a bit about
using the features in VBA windows. Thanks! I'll do this now and report back.

 

[Guest]

It works!!!!!!!!!! oh, thank you so much. A virtual bouquet of thanks. And my
users will enjoy not having to type the state name. I will tell them that
it's a gift from Rod!

In answer to your question, the report, which is a "Master" has only two
fields, but there are a number of subreports, all keyed to the Master field
state.