Updating subform with changes from parent

  • Thread starter Thread starter Jonathan Blitz
  • Start date Start date
J

Jonathan Blitz

I have a form with 3 layers of subforms.

On the top form I have a combo box which supplies a value in order to filter
the rows at the lowest level.
How do I limit the subform's contents based on the value in the parent form?
How do I make sure the displayed values are changed when I select a new
entry in the combo?


--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."
 
How do I limit the subform's contents based on the value in the parent form?
Click to expand...

Base it on a Query using

=Forms![YourFormName]![ComboBoxName]

as a criterion.
How do I make sure the displayed values are changed when I select a new
entry in the combo?
Click to expand...

Requery the sub-subform in the combo's afterupdate event:

Me!subFirst.Form!subSecond.Requery

where subFirst is the name of the outer subform control, subSecond the
name of the nested subform control. Note that the subform control's
name isn't necessarily the same as the name of the Form in that
control!
 
I changed the Record Source to

select * from tblCommissions where MonthInternal =
Forms![frmCarrierCommisionsPerMonth]![Month]

I get and error : Incorrect syntax near '!'.

What did I do wrong?

--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."
John Vinson said:
How do I limit the subform's contents based on the value in the parent
Click to expand...
form?

Base it on a Query using

=Forms![YourFormName]![ComboBoxName]

as a criterion.
How do I make sure the displayed values are changed when I select a new
entry in the combo?
Click to expand...

Requery the sub-subform in the combo's afterupdate event:

Me!subFirst.Form!subSecond.Requery

where subFirst is the name of the outer subform control, subSecond the
name of the nested subform control. Note that the subform control's
name isn't necessarily the same as the name of the Form in that
control!
Click to expand...
 
I changed the Record Source to

select * from tblCommissions where MonthInternal =
Forms![frmCarrierCommisionsPerMonth]![Month]

I get and error : Incorrect syntax near '!'.

What did I do wrong?
Click to expand...

I suspect that it's getting confused because Month is a reserved word;
try changing the name of the form control to txtMonth or cboMonth and
the criterion accordingly.
 
Nope.
Didn't help.

Anythiong else it could be?

--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."
John Vinson said:
I changed the Record Source to

select * from tblCommissions where MonthInternal =
Forms![frmCarrierCommisionsPerMonth]![Month]

I get and error : Incorrect syntax near '!'.

What did I do wrong?
Click to expand...

I suspect that it's getting confused because Month is a reserved word;
try changing the name of the form control to txtMonth or cboMonth and
the criterion accordingly.
Click to expand...
 
Nope.
Didn't help.

Anythiong else it could be?
Click to expand...

If that's the SQL you're actually using, I have no idea. Try creating
a new Query, and copy and paste the SQL into the SQL window of the
query; open the form so that there is a criterion and see if the query
works. If it does, just use the stored query name as the Recordsource.
 
I created a query and entered the following:
SELECT *
FROM tblCommissions
WHERE MonthInternal = Forms![frmCarrierCommisionsPerMonth]![cboMonth]

It doesn't even let me save the command. Complains about the ! mark.

--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."
 
I created a query and entered the following:
SELECT *
FROM tblCommissions
WHERE MonthInternal = Forms![frmCarrierCommisionsPerMonth]![cboMonth]

It doesn't even let me save the command. Complains about the ! mark.
Click to expand...

That is VERY VERY peculiar! You're saving this in the SQL view of a
new Query?? It's perfectly legal SQL.
 
How is the command interpreted?
Is it Access that processes the command or SQL?

Can you try inserting the command in a Query and see if it works for you?
See if it passes syntax check.

--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."
John Vinson said:
I created a query and entered the following:
SELECT *
FROM tblCommissions
WHERE MonthInternal = Forms![frmCarrierCommisionsPerMonth]![cboMonth]

It doesn't even let me save the command. Complains about the ! mark.
Click to expand...

That is VERY VERY peculiar! You're saving this in the SQL view of a
new Query?? It's perfectly legal SQL.
Click to expand...
 
How is the command interpreted?
Is it Access that processes the command or SQL?
Click to expand...

Access uses its own dialect of SQL - not the same as that in
SQL/Server - as the language of queries against JET databases. Are you
using Access? Or are you using SQL/Server? It DOES make a
difference...!
Can you try inserting the command in a Query and see if it works for you?
See if it passes syntax check.
Click to expand...

Yes. It works fine for me. I created a tblCommissions and a Form and
it works perfectly.

I think there must either be some other context in which you are
creating the Query (I used the query design SQL window), or perhaps
you have a corrupted installation of Access.
 
I am using SQL Server as the database. So the problem must be there.
Is that correct?

If so - what can I do?

--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."
 
I am using SQL Server as the database. So the problem must be there.
Is that correct?
Click to expand...

<medium frustration> Thank you for saying so after six messages and a
lot of perplexity. Yes. It *does* make a difference.

SQL/Server, as a client-server system, has no direct access to the
controls on your forms; therefore the Forms criterion won't work.
You'll probably need to write VBA code to extract the actual value of
the form control and create a SQL string containing it, and use *that*
as the rowsource of the combo.
 
Sorry.

I didn't think it made a difference.
I will remember in future.

I did what you said and now it works.

Thanks for your patience.

--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."
 
Back
Top