refering to a subform control

[Terri]

Main form-frmCheckRequest
Sub form-subfrmCheckRequestPayments

On my subform I have 2 fields FundCode and ClassNumber. Both are comboboxes.
I'm trying to filter ClassNumber based on FundCode.

When I create a separate query it returns the records I expect based on the
record I select in the subform. Here's the query code.

SELECT dbo_FundClasses.ClassNumber, dbo_FundClasses.FundCode
FROM dbo_FundClasses
WHERE
(((dbo_FundClasses.FundCode)=[Forms]![frmCheckRequest]![subfrmCheckRequestPa
yments].[Form]![FundCode]));

When I set the rowsource of ClassNumber to the same query I get the same
records no matter which subform record I select. The list presented is
always being filtered from the first subform record even if I select another
record with a different fundcode.

Any ideas?

[Salad]

Terri wrote:

Main form-frmCheckRequest
Sub form-subfrmCheckRequestPayments

On my subform I have 2 fields FundCode and ClassNumber. Both are comboboxes.
I'm trying to filter ClassNumber based on FundCode.

When I create a separate query it returns the records I expect based on the
record I select in the subform. Here's the query code.

SELECT dbo_FundClasses.ClassNumber, dbo_FundClasses.FundCode
FROM dbo_FundClasses
WHERE
(((dbo_FundClasses.FundCode)=[Forms]![frmCheckRequest]![subfrmCheckRequestPa
yments].[Form]![FundCode]));

When I set the rowsource of ClassNumber to the same query I get the same
records no matter which subform record I select. The list presented is
always being filtered from the first subform record even if I select another
record with a different fundcode.
What I would do is copy the SQL code and paste it into a new query.
Query/new/design/close/viewsql and paste. Now open the form. now get
to the query and attempt to open/run. If it blows up, it will show you
where it does.

I'm not sure the .[Form] is necessary. I might do something like this

Dim strSQL As STring
strSQL = "SELECT ClassNumber, FundCode " & _
"FROM dbo_FundClasses " & _
"WHERE dbo_FundClasses.FundCode = " & _
[Forms]![frmCheckRequest]![subfrmCheckRequestPayments]![FundCode]

Me.ComboNameInYourForm.Rowsource = strSQL

I'll assume FundCode is a numeric value, otherwise you need quotes.
"WHERE dbo_FundClasses.FundCode = '" & _
[Forms]![frmCheckRequest]![subfrmCheckRequestPayments]![FundCode] & "'"

Any ideas?

[Terri]

What I would do is copy the SQL code and paste it into a new query.
Query/new/design/close/viewsql and paste. Now open the form. now get
to the query and attempt to open/run. If it blows up, it will show you
where it does.
When I copy into a new query I get my expected results. I switch between
subform records and the separate query works fine.

I'm not sure the .[Form] is necessary. I might do something like this

It doesn't seem to be necessary but removing this does not solve the
problem.

Thanks for your reply

[Terri]

If I put a refresh on the OnCurrent event of the subform it works. Is this
normal?

"Terri" <te***@cybernets.com> wrote in message
news:dd**********@reader2.nmix.net...

What I would do is copy the SQL code and paste it into a new query.
Query/new/design/close/viewsql and paste. Now open the form. now get
to the query and attempt to open/run. If it blows up, it will show you
where it does.

When I copy into a new query I get my expected results. I switch between
subform records and the separate query works fine.

I'm not sure the .[Form] is necessary. I might do something like this

It doesn't seem to be necessary but removing this does not solve the
problem.

Thanks for your reply

[Salad]

Terri wrote:

If I put a refresh on the OnCurrent event of the subform it works. Is this
normal?
I don't know. I usually requery. But if it works, that's what's important.

"Terri" <te***@cybernets.com> wrote in message
news:dd**********@reader2.nmix.net...

What I would do is copy the SQL code and paste it into a new query.
Query/new/design/close/viewsql and paste. Now open the form. now get
to the query and attempt to open/run. If it blows up, it will show you
where it does.

When I copy into a new query I get my expected results. I switch between
subform records and the separate query works fine.

I'm not sure the .[Form] is necessary. I might do something like this

It doesn't seem to be necessary but removing this does not solve the
problem.

Thanks for your reply