Subform problem

A

Andy Bailey

I have a subform that works okay by itself but gives me some problems when in
the main form.

The subform has a combo box to filter the contents of another combo box. The
code attached to the update/current/open events is:

Private Sub Category_AfterUpdate()
Me.Category.Requery
Me.CodeLookup.Requery
End Sub

Private Sub Form_Current()
Me.Category.Requery
Me.CodeLookup.Requery
End Sub

Private Sub Form_Open(Cancel As Integer)
Me.Category = Me.Category.ItemData(0)
Call Category_AfterUpdate
End Sub

and I have some criteria in the query behind the second combo:-

SELECT tblProducts.ProductID, tblProducts.Code, tblProducts.Name,
tblProducts.MiscText, tblProducts.Category
FROM tblProducts
WHERE (((tblProducts.Category)=[Forms]![frmProposalsSubform]![Category])) OR
((([Forms]![frmProposalsSubform]![Category])=1))
ORDER BY tblProducts.Code, tblProducts.Name;

This is fine when the subform is opened by itself but gives me four requests
for a parameter value Forms!frmProposalsSubform!Category when I open the main
form.

I know I've got my grammar wrong in the code and the query (which I
extracted from other posts as I'm a complete novice) - can anybody help
please? I've tried a number of ideas from other posts but I can't quite grasp
where I'm going wrong.

The idea of the first combo is to firstly allow all items in the second
combo to be displayed or filter them by a category.

Thanks - Andy
 
N

Nikos Yannacopoulos

Andy,

The WHERE clause in the combo's recordsource is referencing the other
combo correctly when the form is opened stand-alone, but not when it's a
subform in another form... the reference should be something like:

[Forms]![MainFormName]![frmProposalsSubform]![Category]

(in which case, of course, it will no longer work as a stand-alone form).

Also, I'm not sure the logic of the WHERE clause is what it was intended
to be; any chance you meant:

WHERE
(((tblProducts.Category)=[Forms]![frmProposalsSubform]![Category])) OR
(((tblProducts.Category)=1))

???

(again, fix the reference).

HTH,
Nikos
 
A

Andy Bailey

Nikos

Thanks for your reply; appreciate help. I tried the syntax including the
name of the main form which is frmProposal but I still get the same problem.

I "rem'd" out all the code that starts when the subform is opened/ updated
etc and I get just one parameter value request for:-

Forms!frmProposal!frmProposalsSubform!Category

The SQL is now:-

SELECT tblProducts.ProductID, tblProducts.Code, tblProducts.Name,
tblProducts.MiscText, tblProducts.Category
FROM tblProducts
WHERE
(((tblProducts.Category)=[Forms]![frmProposal]![frmProposalsSubform]![Category])) OR ((([Forms]![frmProposal]![frmProposalsSubform]![Category])=1))
ORDER BY tblProducts.Code, tblProducts.Name;

The logic (if there is any!) behind the where clause is to give an option to
show all records in the second combo - I have a category "All" which is "1"
in the lookup.

As I said I'm a complete novice so I may be doing something else
fundamentally wrong. Appreciate any help.


Andy
 
N

Nikos Yannacopoulos

Andy,

We're getting somewhere now. The parameter request is coming from the
WHERE clause, which is not right for what you want it to do. Try:

SELECT tblProducts.ProductID, tblProducts.Code, tblProducts.Name,
tblProducts.MiscText, tblProducts.Category
FROM tblProducts
WHERE tblProducts.Category LIKE
IIf([Forms]![frmProposal]![frmProposalsSubform]![Category] = 1, "*",
[Forms]![frmProposal]![frmProposalsSubform]![Category])
ORDER BY tblProducts.Code, tblProducts.Name;

HTH,
Nikos

Andy said:
Nikos

Thanks for your reply; appreciate help. I tried the syntax including the
name of the main form which is frmProposal but I still get the same problem.

I "rem'd" out all the code that starts when the subform is opened/ updated
etc and I get just one parameter value request for:-

Forms!frmProposal!frmProposalsSubform!Category

The SQL is now:-

SELECT tblProducts.ProductID, tblProducts.Code, tblProducts.Name,
tblProducts.MiscText, tblProducts.Category
FROM tblProducts
WHERE
(((tblProducts.Category)=[Forms]![frmProposal]![frmProposalsSubform]![Category])) OR ((([Forms]![frmProposal]![frmProposalsSubform]![Category])=1))
ORDER BY tblProducts.Code, tblProducts.Name;

The logic (if there is any!) behind the where clause is to give an option to
show all records in the second combo - I have a category "All" which is "1"
in the lookup.

As I said I'm a complete novice so I may be doing something else
fundamentally wrong. Appreciate any help.


Andy



:

Andy,

The WHERE clause in the combo's recordsource is referencing the other
combo correctly when the form is opened stand-alone, but not when it's a
subform in another form... the reference should be something like:

[Forms]![MainFormName]![frmProposalsSubform]![Category]

(in which case, of course, it will no longer work as a stand-alone form).

Also, I'm not sure the logic of the WHERE clause is what it was intended
to be; any chance you meant:

WHERE
(((tblProducts.Category)=[Forms]![frmProposalsSubform]![Category])) OR
(((tblProducts.Category)=1))

???

(again, fix the reference).

HTH,
Nikos
Click to expand...
Click to expand...
 
A

Andy Bailey

Nikos,

Again thanks for your help. I think I ended up with my original SQL by trial
and error as it allowed all records in the second combo if the item wasn't
assigned a category.

I tried the SQL you kindly posted but still get the parameter request for:-

Forms!frmProposal!frmProposalsSubform!Category

I copied the subform and used your SQL with the first Form reference removed
and it worked perfectly when opened standalone - I don't know what I'm doing
wrong when it's used within the main form. Is there anything I have to do
elsewhere to get the two to "talk" (forgive my terminology).

I still have the requerying code on the subform disabled so even when it's
opened with the main form my filter doesn't work. If I enable it I get two
parameter requests (as above) when it's opened and one when I try the first
combo to filter the second.

Many thanks,

Andy
 
N

Nikos Yannacopoulos

Andy,

My best guess is it's all in the WHERE clause, and the fact that when
you enable the code it occurs repeatedly, goes to show just that; the
reason is the Requery actually forces the query to run again. Are you
certain there is no typo in the reference (vs. the actual form/control
names)?
Long shot: is the query saved as an independent query, outside the form?
If yes, any chance you have declared a parameter in there (query >
parameters)? If yes, remove it.

HTH,
Nikos


Andy said:
Nikos,

Again thanks for your help. I think I ended up with my original SQL by trial
and error as it allowed all records in the second combo if the item wasn't
assigned a category.

I tried the SQL you kindly posted but still get the parameter request for:-

Forms!frmProposal!frmProposalsSubform!Category

I copied the subform and used your SQL with the first Form reference removed
and it worked perfectly when opened standalone - I don't know what I'm doing
wrong when it's used within the main form. Is there anything I have to do
elsewhere to get the two to "talk" (forgive my terminology).

I still have the requerying code on the subform disabled so even when it's
opened with the main form my filter doesn't work. If I enable it I get two
parameter requests (as above) when it's opened and one when I try the first
combo to filter the second.

Many thanks,

Andy


:

Andy,

We're getting somewhere now. The parameter request is coming from the
WHERE clause, which is not right for what you want it to do. Try:

SELECT tblProducts.ProductID, tblProducts.Code, tblProducts.Name,
tblProducts.MiscText, tblProducts.Category
FROM tblProducts
WHERE tblProducts.Category LIKE
IIf([Forms]![frmProposal]![frmProposalsSubform]![Category] = 1, "*",
[Forms]![frmProposal]![frmProposalsSubform]![Category])
ORDER BY tblProducts.Code, tblProducts.Name;

HTH,
Nikos
Click to expand...
Click to expand...
 
A

Andy Bailey

Nikos

I'll give it a real good looking at as I'm pretty good at making typos. I've
been staring at it for a few days now so am probably screen-shocked.

Thanks for all your help. I'll rebuild the combos and try again.
 
A

Andy Bailey

Nikos,

Got there - it was where you pointed me to. I rebuilt the two combos and
added the SQL bit by bit til I got what I wanted.

Thanks again for your kind help; I appreciate it.


Andy
 
Top