Query using values from a Form

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

Guest

I have a tabbed form within a tabbed form. On one of the tabs, I have a
sub-form. I'd like to know the syntax to be used for printing a report based
on the data displayed in the form.

This is what I've done -
1. Designed a form [form1] to display the data.
2. Built a query using [forms].[form1].[somefield]
3. Designed the report [report1] with the query as the data source.
4. Designed a tabbed form [form2] with [form1] as the subform.
5. Designed a second tabbed form with [form2] as the subform.

How do I design the query to recognize [form1].[somefield]?
 
Brian,

Use this in the query...
[Forms]![form3]![form2].[Form]![form1].[Form]![somefield]
 
That didn't work. Any other ideas?

Steve Schapel said:
Brian,

Use this in the query...
[Forms]![form3]![form2].[Form]![form1].[Form]![somefield]

--
Steve Schapel, Microsoft Access MVP


brian said:
I have a tabbed form within a tabbed form. On one of the tabs, I have a
sub-form. I'd like to know the syntax to be used for printing a report based
on the data displayed in the form.

This is what I've done -
1. Designed a form [form1] to display the data.
2. Built a query using [forms].[form1].[somefield]
3. Designed the report [report1] with the query as the data source.
4. Designed a tabbed form [form2] with [form1] as the subform.
5. Designed a second tabbed form with [form2] as the subform.

How do I design the query to recognize [form1].[somefield]?
Click to expand...
Click to expand...
 
Brian,

The most likely cause of the "didn't work" is that you have made a
spelling error.

Please post back with the SQL view of the query, and also let us know
what result you are actually getting.
 
I beleive you're right. Instead of using the db I want this for, I built one
based on the Form1, Form2 scenerio in my post. I copied and pasted your code
into the query and it worked. From the form, I added this to a command
button:

If Me.Dirty = True Then Me.Dirty = False
DoCmd.OpenReport "report1", acViewPreview, ,
"[somefield]=[Forms]![form3]![form2].[Form]![form1].[Form]![somefield]"
 
Brian,

Depending which form the command button running this code is on, it
could be simplified. For example, if it's the main form (form3), the
code may be...

DoCmd.OpenReport "report1", acViewPreview, , "[somefield]=" &
Me.form2.[Form]![form1].[Form]![somefield]
....or, if somefiled is a text data type...
DoCmd.OpenReport "report1", acViewPreview, , "[somefield]='" &
Me.form2.[Form]![form1].[Form]![somefield] & "'"

However, if the code is on form2, the fact that form2 is a subfrom on
form3 becomes irrelevant...
DoCmd.OpenReport "report1", acViewPreview, , "[somefield]=" &
Me.form1.[Form]![somefield]
 
Back
Top