Macro not working when subform is added to main form

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

Guest

Hello,

I know this has to be a really easy fix, but I've fought with it all morning
and I just can't figure it out.

I have a subform that has four fields (PRODUCT, AREA, SPACE, RACK). The
rack and space are based on queries that use the AREA as the parameter, so
that the rack and space choices change when the area changes. This all works
just fine when I work in the subform itself. But when I put the subform in
a form that links the two by product, I get an error message that says I'm
trying to apply a filter and haven't selected an object to apply it to. Can
you help me with this???

Thanks!
 
Afg,

I would assume that the queries that you are using for the Row Source of
the Space and Rack (presumably) comboboxes are using Criteria that
reference the value of the other controls on the form. Is that right?
So when the form is used as a subform, the form itself is not open. So
the references in the queries have to use the correct syntax to refer to
the subform control on the main form. Hope that makes sense. If you
need more explicit help, please post back with more details. In fact,
the SQL view of the queries would probably help to give us an idea of
what you have there.
 
Thank you for responding! I think you're right in that I just need the
correct syntax to reference the form that's not open, but I've tried
everything I can think of, and nothing works.

Here's how I have it set up:
My main form [frm Cooler Inventory Selected Product] simply holds the
Product Name.
Then my subform [tbl Inventory subform] is linked to the main form by
Product Name. Then in the detail are three combo boxes: Area, Rack and Space.
To populate the choices in these combo boxes, I have set up the following.
The Area simply pulls from a query of Area choices, which is called [qry
Areas].
Then for the Rack and Space, I have a main query called [qry Area Selection]
that has the following SQL setup: SELECT [tbl Cooler Rack Inventory].Area,
[tbl Cooler Rack Inventory].[Rack Number], [tbl Cooler Rack Inventory].Space
FROM [tbl Cooler Rack Inventory] WHERE ((([tbl Cooler Rack
Inventory].Area)=[Forms]![tbl Inventory subform]![Area]));
Then to pull just the rack numbers for the Rack combo box, I used a query
called [qry Rack List] which is based on the above query and has this SQL
setup: SELECT [qry Area Selection].[Rack Number] FROM [qry Area Selection]
GROUP BY [qry Area Selection].[Rack Number];
The same for the Space - to pull just the space numbers, I used a query
called [qry Space List] which is based on the same Area Selection query and
has the following SQL setup: SELECT [qry Area Selection].Space FROM [qry Area
Selection] GROUP BY [qry Area Selection].Space;

And in my main form, the control name for the subform is the same as the
name of the subform itself.

Does this help at all?



Thanks,

Kimberley
 
Kimberly,

The [qry Area Selection] query should be like this...

SELECT [tbl Cooler Rack Inventory].Area, [tbl Cooler Rack
Inventory].[Rack Number], [tbl Cooler Rack Inventory].Space
FROM [tbl Cooler Rack Inventory]
WHERE (([tbl Cooler Rack Inventory].Area)=[Forms]![frm Cooler Inventory
Selected Product]![tbl Inventory subform].[Form]![Area]);

While you're at it, change [qry Rack List] to:
SELECT DISTINCT [qry Area Selection].[Rack Number] FROM [qry Area
Selection];

.... and [qry Space List] to:
SELECT DISTINCT [qry Area Selection].Space FROM [qry Area Selection];
 
Thank you so much!!! I'll give it a try.

Steve Schapel said:
Kimberly,

The [qry Area Selection] query should be like this...

SELECT [tbl Cooler Rack Inventory].Area, [tbl Cooler Rack
Inventory].[Rack Number], [tbl Cooler Rack Inventory].Space
FROM [tbl Cooler Rack Inventory]
WHERE (([tbl Cooler Rack Inventory].Area)=[Forms]![frm Cooler Inventory
Selected Product]![tbl Inventory subform].[Form]![Area]);

While you're at it, change [qry Rack List] to:
SELECT DISTINCT [qry Area Selection].[Rack Number] FROM [qry Area
Selection];

.... and [qry Space List] to:
SELECT DISTINCT [qry Area Selection].Space FROM [qry Area Selection];

--
Steve Schapel, Microsoft Access MVP
Thank you for responding! I think you're right in that I just need the
correct syntax to reference the form that's not open, but I've tried
everything I can think of, and nothing works.

Here's how I have it set up:
My main form [frm Cooler Inventory Selected Product] simply holds the
Product Name.
Then my subform [tbl Inventory subform] is linked to the main form by
Product Name. Then in the detail are three combo boxes: Area, Rack and Space.
To populate the choices in these combo boxes, I have set up the following.
The Area simply pulls from a query of Area choices, which is called [qry
Areas].
Then for the Rack and Space, I have a main query called [qry Area Selection]
that has the following SQL setup: SELECT [tbl Cooler Rack Inventory].Area,
[tbl Cooler Rack Inventory].[Rack Number], [tbl Cooler Rack Inventory].Space
FROM [tbl Cooler Rack Inventory] WHERE ((([tbl Cooler Rack
Inventory].Area)=[Forms]![tbl Inventory subform]![Area]));
Then to pull just the rack numbers for the Rack combo box, I used a query
called [qry Rack List] which is based on the above query and has this SQL
setup: SELECT [qry Area Selection].[Rack Number] FROM [qry Area Selection]
GROUP BY [qry Area Selection].[Rack Number];
The same for the Space - to pull just the space numbers, I used a query
called [qry Space List] which is based on the same Area Selection query and
has the following SQL setup: SELECT [qry Area Selection].Space FROM [qry Area
Selection] GROUP BY [qry Area Selection].Space;

And in my main form, the control name for the subform is the same as the
name of the subform itself.
Click to expand...
Click to expand...
 
Hi.

Everything seems to be working, except that when I go to a new record, I get
a message that says I can't apply the filter to this window. It doesn't give
me the macro halt error, and once I close the error, it moves on to the next
record and works fine. But how can I get this error to stop popping up?

Steve Schapel said:
Kimberly,

The [qry Area Selection] query should be like this...

SELECT [tbl Cooler Rack Inventory].Area, [tbl Cooler Rack
Inventory].[Rack Number], [tbl Cooler Rack Inventory].Space
FROM [tbl Cooler Rack Inventory]
WHERE (([tbl Cooler Rack Inventory].Area)=[Forms]![frm Cooler Inventory
Selected Product]![tbl Inventory subform].[Form]![Area]);

While you're at it, change [qry Rack List] to:
SELECT DISTINCT [qry Area Selection].[Rack Number] FROM [qry Area
Selection];

.... and [qry Space List] to:
SELECT DISTINCT [qry Area Selection].Space FROM [qry Area Selection];

--
Steve Schapel, Microsoft Access MVP
Thank you for responding! I think you're right in that I just need the
correct syntax to reference the form that's not open, but I've tried
everything I can think of, and nothing works.

Here's how I have it set up:
My main form [frm Cooler Inventory Selected Product] simply holds the
Product Name.
Then my subform [tbl Inventory subform] is linked to the main form by
Product Name. Then in the detail are three combo boxes: Area, Rack and Space.
To populate the choices in these combo boxes, I have set up the following.
The Area simply pulls from a query of Area choices, which is called [qry
Areas].
Then for the Rack and Space, I have a main query called [qry Area Selection]
that has the following SQL setup: SELECT [tbl Cooler Rack Inventory].Area,
[tbl Cooler Rack Inventory].[Rack Number], [tbl Cooler Rack Inventory].Space
FROM [tbl Cooler Rack Inventory] WHERE ((([tbl Cooler Rack
Inventory].Area)=[Forms]![tbl Inventory subform]![Area]));
Then to pull just the rack numbers for the Rack combo box, I used a query
called [qry Rack List] which is based on the above query and has this SQL
setup: SELECT [qry Area Selection].[Rack Number] FROM [qry Area Selection]
GROUP BY [qry Area Selection].[Rack Number];
The same for the Space - to pull just the space numbers, I used a query
called [qry Space List] which is based on the same Area Selection query and
has the following SQL setup: SELECT [qry Area Selection].Space FROM [qry Area
Selection] GROUP BY [qry Area Selection].Space;

And in my main form, the control name for the subform is the same as the
name of the subform itself.
Click to expand...
Click to expand...
 
Ok, so everything seems to be working fine. Except that when I go to a new
record in the subform, I still get an error message saying that I can't apply
the filter to the window. Once I click OK, the message goes away and the new
record works just fine. Is there any way to stop this message?

Thanks!

Steve Schapel said:
Kimberly,

The [qry Area Selection] query should be like this...

SELECT [tbl Cooler Rack Inventory].Area, [tbl Cooler Rack
Inventory].[Rack Number], [tbl Cooler Rack Inventory].Space
FROM [tbl Cooler Rack Inventory]
WHERE (([tbl Cooler Rack Inventory].Area)=[Forms]![frm Cooler Inventory
Selected Product]![tbl Inventory subform].[Form]![Area]);

While you're at it, change [qry Rack List] to:
SELECT DISTINCT [qry Area Selection].[Rack Number] FROM [qry Area
Selection];

.... and [qry Space List] to:
SELECT DISTINCT [qry Area Selection].Space FROM [qry Area Selection];

--
Steve Schapel, Microsoft Access MVP
Thank you for responding! I think you're right in that I just need the
correct syntax to reference the form that's not open, but I've tried
everything I can think of, and nothing works.

Here's how I have it set up:
My main form [frm Cooler Inventory Selected Product] simply holds the
Product Name.
Then my subform [tbl Inventory subform] is linked to the main form by
Product Name. Then in the detail are three combo boxes: Area, Rack and Space.
To populate the choices in these combo boxes, I have set up the following.
The Area simply pulls from a query of Area choices, which is called [qry
Areas].
Then for the Rack and Space, I have a main query called [qry Area Selection]
that has the following SQL setup: SELECT [tbl Cooler Rack Inventory].Area,
[tbl Cooler Rack Inventory].[Rack Number], [tbl Cooler Rack Inventory].Space
FROM [tbl Cooler Rack Inventory] WHERE ((([tbl Cooler Rack
Inventory].Area)=[Forms]![tbl Inventory subform]![Area]));
Then to pull just the rack numbers for the Rack combo box, I used a query
called [qry Rack List] which is based on the above query and has this SQL
setup: SELECT [qry Area Selection].[Rack Number] FROM [qry Area Selection]
GROUP BY [qry Area Selection].[Rack Number];
The same for the Space - to pull just the space numbers, I used a query
called [qry Space List] which is based on the same Area Selection query and
has the following SQL setup: SELECT [qry Area Selection].Space FROM [qry Area
Selection] GROUP BY [qry Area Selection].Space;

And in my main form, the control name for the subform is the same as the
name of the subform itself.
Click to expand...
Click to expand...
 
Kimberly,

I am not 100% sure, but I think this must be because of referring back
to the qry Area Selection query in the selector queries. Try changing
the [qry Rack List] query to:
SELECT DISTINCT [Rack Number] FROM [tbl Cooler Rack Inventory] WHERE
([Area]=[Forms]![frm Cooler Inventory Selected Product]![tbl Inventory
subform].[Form]![Area]);

.... and [qry Space List] to:
SELECT DISTINCT [Space] FROM [tbl Cooler Rack Inventory] WHERE
([Area]=[Forms]![frm Cooler Inventory Selected Product]![tbl Inventory
subform].[Form]![Area]);

Let us know if that fixes it.
 
It works perfectly now!!!! :) Thank you so much for all of your help!!!


Kimberley

Steve Schapel said:
Kimberly,

I am not 100% sure, but I think this must be because of referring back
to the qry Area Selection query in the selector queries. Try changing
the [qry Rack List] query to:
SELECT DISTINCT [Rack Number] FROM [tbl Cooler Rack Inventory] WHERE
([Area]=[Forms]![frm Cooler Inventory Selected Product]![tbl Inventory
subform].[Form]![Area]);

.... and [qry Space List] to:
SELECT DISTINCT [Space] FROM [tbl Cooler Rack Inventory] WHERE
([Area]=[Forms]![frm Cooler Inventory Selected Product]![tbl Inventory
subform].[Form]![Area]);

Let us know if that fixes it.

--
Steve Schapel, Microsoft Access MVP
Hi.

Everything seems to be working, except that when I go to a new record, I get
a message that says I can't apply the filter to this window. It doesn't give
me the macro halt error, and once I close the error, it moves on to the next
record and works fine. But how can I get this error to stop popping up?
Click to expand...
Click to expand...
 
Back
Top