RecordSource

[Guest]

Hi,

How do I set a subform record source to a query?

I have tried this..

Me.subForm.Form.RecordSource = Query![qryName]

Thanks

 

[Guest]

I have also tried this. I do not get the data.

Me.frmsubAppl.Form.RecordSource = "SELECT * FROM tblApp WHERE
(((tblApp.LocationID)=[Forms]![frmAPM]![cboLocation]) AND
((tblApp.AppIssue)=[Forms]![frmAPM]![cboIssuePlat]))"

 

[Marshall Barton]

How do I set a subform record source to a query?

I have tried this..

Me.subForm.Form.RecordSource = Query![qryName]

That should be:

Me.subForm.Form.RecordSource = "qryName"

 

[Marshall Barton]

This looks like its should work, however, I would resolve
the parameters in the query, instead of leaving them for the
query to resolve. If both values are numeric, it would be:

Me.frmsubAppl.Form.RecordSource = _
"SELECT * FROM tblApp WHERE (LocationID=" _
& Me.cboLocation & ") AND (AppIssue)=" _
& Me.cboIssuePlat & ")"

Just double check that frmsubAppl is the name of the subform
**control** on the main form, which might be different than
the name of the form the control is displaying.
--
Marsh
MVP [MS Access]

I have also tried this. I do not get the data.

Me.frmsubAppl.Form.RecordSource = "SELECT * FROM tblApp WHERE
(((tblApp.LocationID)=[Forms]![frmAPM]![cboLocation]) AND
((tblApp.AppIssue)=[Forms]![frmAPM]![cboIssuePlat]))"

How do I set a subform record source to a query?

I have tried this..
Me.subForm.Form.RecordSource = Query![qryName]

 

[Guest]

I have tried your suggestion. It errors telling me error #3075, syntax
error, missing operator in the where portion. The values are not numbers.
They are text.

This looks like its should work, however, I would resolve
the parameters in the query, instead of leaving them for the
query to resolve. If both values are numeric, it would be:

Me.frmsubAppl.Form.RecordSource = _
"SELECT * FROM tblApp WHERE (LocationID=" _
& Me.cboLocation & ") AND (AppIssue)=" _
& Me.cboIssuePlat & ")"

Just double check that frmsubAppl is the name of the subform
**control** on the main form, which might be different than
the name of the form the control is displaying.
--
Marsh
MVP [MS Access]

I have also tried this. I do not get the data.

Me.frmsubAppl.Form.RecordSource = "SELECT * FROM tblApp WHERE
(((tblApp.LocationID)=[Forms]![frmAPM]![cboLocation]) AND
((tblApp.AppIssue)=[Forms]![frmAPM]![cboIssuePlat]))"

How do I set a subform record source to a query?

I have tried this..
Me.subForm.Form.RecordSource = Query![qryName]

 

[Guest]

I tried this one too. It returns no values. If I run the subform
separately, it runs fine. I have double checked the name of the sub form
control and the names of the combo boxes.

How do I set a subform record source to a query?

I have tried this..

Me.subForm.Form.RecordSource = Query![qryName]

That should be:

Me.subForm.Form.RecordSource = "qryName"

 

[Guest]

I tried this as well. I don't get my data. It works fine if I run the
subform separately. I have double checked the names of the combo boxes. the
name of the subform control, the name of the query, etc.

How do I set a subform record source to a query?

I have tried this..

Me.subForm.Form.RecordSource = Query![qryName]

That should be:

Me.subForm.Form.RecordSource = "qryName"

 

[Marshall Barton]

The devil's in the details ;-)

For text type fields, it would be:

Me.frmsubAppl.Form.RecordSource = _
"SELECT * FROM tblApp WHERE (LocationID=""" _
& Me.cboLocation & """) AND (AppIssue)=""" _
& Me.cboIssuePlat & """)"

because the values are strings, they must be exclosed in
quotes (doubled up because they are inside other quotes).
--
Marsh
MVP [MS Access]

I have tried your suggestion. It errors telling me error #3075, syntax
error, missing operator in the where portion. The values are not numbers.
They are text.

This looks like its should work, however, I would resolve
the parameters in the query, instead of leaving them for the
query to resolve. If both values are numeric, it would be:

Me.frmsubAppl.Form.RecordSource = _
"SELECT * FROM tblApp WHERE (LocationID=" _
& Me.cboLocation & ") AND (AppIssue)=" _
& Me.cboIssuePlat & ")"

Just double check that frmsubAppl is the name of the subform
**control** on the main form, which might be different than
the name of the form the control is displaying.
--
Marsh
MVP [MS Access]

I have also tried this. I do not get the data.

Me.frmsubAppl.Form.RecordSource = "SELECT * FROM tblApp WHERE
(((tblApp.LocationID)=[Forms]![frmAPM]![cboLocation]) AND
((tblApp.AppIssue)=[Forms]![frmAPM]![cboIssuePlat]))"

:
How do I set a subform record source to a query?

I have tried this..
Me.subForm.Form.RecordSource = Query![qryName]

 

[Marshall Barton]

If the query works fine by itself or in the form not as a
subform, then the problem must be something in the main form
subform combination. The most likely culprit is the Link
Master/Child properties of the subform control. Double
check them to make sure they're what you want.
--
Marsh
MVP [MS Access]

I tried this one too. It returns no values. If I run the subform
separately, it runs fine. I have double checked the name of the sub form
control and the names of the combo boxes.

How do I set a subform record source to a query?

I have tried this..

Me.subForm.Form.RecordSource = Query![qryName]

That should be:

Me.subForm.Form.RecordSource = "qryName"

 

[Guest]

The subform works fine as a subform when I open the main form and navigate
through the records. The correct data is displayed just fine.

I am trying to get it to requery when I change the value of the combo box.
A previous post said it would requery if I set the record source. So that is
what I have been trying. In short, when I change the combo box value using
the recordsource statement setting it to the original query on "after
update", I lose the data that was in the subform and it does not present the
data based on the new value. The project is at work so I will try the quotes
tomorrow. Knowing more detail as to what I am trying to do, do you have any
other suggestions?

If the query works fine by itself or in the form not as a
subform, then the problem must be something in the main form
subform combination. The most likely culprit is the Link
Master/Child properties of the subform control. Double
check them to make sure they're what you want.
--
Marsh
MVP [MS Access]

I tried this one too. It returns no values. If I run the subform
separately, it runs fine. I have double checked the name of the sub form
control and the names of the combo boxes.

Karen53 wrote:
How do I set a subform record source to a query?

I have tried this..

Me.subForm.Form.RecordSource = Query![qryName]

That should be:

Me.subForm.Form.RecordSource = "qryName"

 

[Marshall Barton]

I'm getting lost here. What does "does not present the data
based on the new value" mean. Is there something going on
betond simply setting the subform's record source? Looking
at your other attempt (where you need additional quotes), I
think you're using the combo box(es?) to filter the
subform's data. If this whole thread is only about
requerying the subform, this sure is the long way around.

You can set the subform RecordSource to it's original query
with out having any idea what the query was:
Me.subForm.Form.RecordSource = Me.subForm.Form.RecordSource

But, even that is overkill to just get the subform's data
resynchronized with the new combo box value. The standard
line of code to do that is just:
Me.subForm.Form.Requery
--
Marsh
MVP [MS Access]

The subform works fine as a subform when I open the main form and navigate
through the records. The correct data is displayed just fine.

I am trying to get it to requery when I change the value of the combo box.
A previous post said it would requery if I set the record source. So that is
what I have been trying. In short, when I change the combo box value using
the recordsource statement setting it to the original query on "after
update", I lose the data that was in the subform and it does not present the
data based on the new value. The project is at work so I will try the quotes
tomorrow. Knowing more detail as to what I am trying to do, do you have any
other suggestions?

If the query works fine by itself or in the form not as a
subform, then the problem must be something in the main form
subform combination. The most likely culprit is the Link
Master/Child properties of the subform control. Double
check them to make sure they're what you want.

I tried this one too. It returns no values. If I run the subform
separately, it runs fine. I have double checked the name of the sub form
control and the names of the combo boxes.


Karen53 wrote:
How do I set a subform record source to a query?

I have tried this..

Me.subForm.Form.RecordSource = Query![qryName]


:
That should be:

Me.subForm.Form.RecordSource = "qryName"

 

[Guest]

Thank you. Short and sweet. I had posted my question before about
requerying the subform. One of my responses was to reset the record source.
I like your way better. But I am still having trouble and it is driving me
crazy. I have double checked the control name of my sub form. I even
deleted it and recreated it. I have double checked the names of my fields on
the combo boxes. I have double checked the Master/Child relationship.
Somehow it has got to be a problem with my requery. It comes up correctly
the first time, until I change the selection and requery. It is supposted to
be the subform's CONTROL name and not the form name, correct? I have the
requery placed in the afterupdate of the combo box. Is there anything else I
need to check?

Thanks

I'm getting lost here. What does "does not present the data
based on the new value" mean. Is there something going on
betond simply setting the subform's record source? Looking
at your other attempt (where you need additional quotes), I
think you're using the combo box(es?) to filter the
subform's data. If this whole thread is only about
requerying the subform, this sure is the long way around.

You can set the subform RecordSource to it's original query
with out having any idea what the query was:
Me.subForm.Form.RecordSource = Me.subForm.Form.RecordSource

But, even that is overkill to just get the subform's data
resynchronized with the new combo box value. The standard
line of code to do that is just:
Me.subForm.Form.Requery
--
Marsh
MVP [MS Access]

The subform works fine as a subform when I open the main form and navigate
through the records. The correct data is displayed just fine.

I am trying to get it to requery when I change the value of the combo box.
A previous post said it would requery if I set the record source. So that is
what I have been trying. In short, when I change the combo box value using
the recordsource statement setting it to the original query on "after
update", I lose the data that was in the subform and it does not present the
data based on the new value. The project is at work so I will try the quotes
tomorrow. Knowing more detail as to what I am trying to do, do you have any
other suggestions?

If the query works fine by itself or in the form not as a
subform, then the problem must be something in the main form
subform combination. The most likely culprit is the Link
Master/Child properties of the subform control. Double
check them to make sure they're what you want.


Karen53 wrote:
I tried this one too. It returns no values. If I run the subform
separately, it runs fine. I have double checked the name of the sub form
control and the names of the combo boxes.


Karen53 wrote:
How do I set a subform record source to a query?

I have tried this..

Me.subForm.Form.RecordSource = Query![qryName]


:
That should be:

Me.subForm.Form.RecordSource = "qryName"

 

[Marshall Barton]

Thank you. Short and sweet. I had posted my question before about
requerying the subform. One of my responses was to reset the record source.
I like your way better. But I am still having trouble and it is driving me
crazy. I have double checked the control name of my sub form. I even
deleted it and recreated it. I have double checked the names of my fields on
the combo boxes. I have double checked the Master/Child relationship.
Somehow it has got to be a problem with my requery. It comes up correctly
the first time, until I change the selection and requery. It is supposted to
be the subform's CONTROL name and not the form name, correct? I have the
requery placed in the afterupdate of the combo box. Is there anything else I
need to check?

Either I'm missing something here, or you've got something I
haven't seen yet that's out of whack?

The combox boxes' AfterUpdate is the right place to Requery
the subform. And, Yes, it is the name of the subform
**control** that you reference to do that:

Let's check the subform's record source query to make sure
the values of the combo boxes are used correctly. Post the
subform's recordsource query's SQL statement along with the
details of the combo box: its Row Source query, bound
column, column count, etc. Then we'll see where where we
are.

 

[Guest]

Thank you, Marsh.

Unfortunately, I was not in the office today nor will I be in the office
tomorrow which is where the project is. I will post it when I return next
week.

Thank you again!

 

[Guest]

Here is the query SQL:
SELECT tbl_AppLocations.LocApplicationID, tbl_AppLocations.LocationID,
tbl_AppLocations.ApplicationIssue, tbl_AppLocations.AppQuantity,
tbl_AppLocations.CMD, tbl_AppLocations.LoanAdmin, tbl_AppLocations.Balboa
FROM tbl_AppLocations
WHERE
(((tbl_AppLocations.LocationID)=[Forms]![frm_A_postmortem]![cbo_Location])
AND
((tbl_AppLocations.ApplicationIssue)=[Forms]![frm_A_postmortem]![cbo_Issue_Platform]));


cbo_Location / SELECT tbl_Location.Location FROM tbl_Location; / Bound
Column 1

cbo_Issue_Platform / SELECT tbl_Application_Issue.Application_Issue FROM
tbl_Application_Issue; / Bound Column 1

Thanks

 

[Marshall Barton]

On the surface, your queries look ok. Only a nonessential
thing thing jumps out at me is that you probably want the
Requery in both combo box's AfterUpdate event.

But the problem is most likely some other issue. Are you
absolutely certain that the tbl_AppLocations.LocationID
field is the same value/type as tbl_Location.Location?
Myabe one is a numeric key and the other is a text name???

Also, you just mentioned that you are using Link
Master/Child properties on the subform control, could these
be in conflict with the criteria in the query? It is often
the case that you would use either the Link Master/Child
properties or criteria in the query but not both.
--
Marsh
MVP [MS Access]

Here is the query SQL:
SELECT tbl_AppLocations.LocApplicationID, tbl_AppLocations.LocationID,
tbl_AppLocations.ApplicationIssue, tbl_AppLocations.AppQuantity,
tbl_AppLocations.CMD, tbl_AppLocations.LoanAdmin, tbl_AppLocations.Balboa
FROM tbl_AppLocations
WHERE
(((tbl_AppLocations.LocationID)=[Forms]![frm_A_postmortem]![cbo_Location])
AND
((tbl_AppLocations.ApplicationIssue)=[Forms]![frm_A_postmortem]![cbo_Issue_Platform]));


cbo_Location / SELECT tbl_Location.Location FROM tbl_Location; / Bound
Column 1

cbo_Issue_Platform / SELECT tbl_Application_Issue.Application_Issue FROM
tbl_Application_Issue; / Bound Column 1

Either I'm missing something here, or you've got something I
haven't seen yet that's out of whack?

The combox boxes' AfterUpdate is the right place to Requery
the subform. And, Yes, it is the name of the subform
**control** that you reference to do that:

Let's check the subform's record source query to make sure
the values of the combo boxes are used correctly. Post the
subform's recordsource query's SQL statement along with the
details of the combo box: its Row Source query, bound
column, column count, etc. Then we'll see where where we
are.

 

[Guest]

Yes, the tble_AppLocations.LocationID is the same data type as
tbl_Location.Location. I will try removing the Master/Child.

Yes, I do intend to requery on both, once I get it to work. Thanks for your
help! I'll let you know if it works.

On the surface, your queries look ok. Only a nonessential
thing thing jumps out at me is that you probably want the
Requery in both combo box's AfterUpdate event.

But the problem is most likely some other issue. Are you
absolutely certain that the tbl_AppLocations.LocationID
field is the same value/type as tbl_Location.Location?
Myabe one is a numeric key and the other is a text name???

Also, you just mentioned that you are using Link
Master/Child properties on the subform control, could these
be in conflict with the criteria in the query? It is often
the case that you would use either the Link Master/Child
properties or criteria in the query but not both.
--
Marsh
MVP [MS Access]

Here is the query SQL:
SELECT tbl_AppLocations.LocApplicationID, tbl_AppLocations.LocationID,
tbl_AppLocations.ApplicationIssue, tbl_AppLocations.AppQuantity,
tbl_AppLocations.CMD, tbl_AppLocations.LoanAdmin, tbl_AppLocations.Balboa
FROM tbl_AppLocations
WHERE
(((tbl_AppLocations.LocationID)=[Forms]![frm_A_postmortem]![cbo_Location])
AND
((tbl_AppLocations.ApplicationIssue)=[Forms]![frm_A_postmortem]![cbo_Issue_Platform]));


cbo_Location / SELECT tbl_Location.Location FROM tbl_Location; / Bound
Column 1

cbo_Issue_Platform / SELECT tbl_Application_Issue.Application_Issue FROM
tbl_Application_Issue; / Bound Column 1

Karen53 wrote:
Thank you. Short and sweet. I had posted my question before about
requerying the subform. One of my responses was to reset the record source.
I like your way better. But I am still having trouble and it is driving me
crazy. I have double checked the control name of my sub form. I even
deleted it and recreated it. I have double checked the names of my fields on
the combo boxes. I have double checked the Master/Child relationship.
Somehow it has got to be a problem with my requery. It comes up correctly
the first time, until I change the selection and requery. It is supposted to
be the subform's CONTROL name and not the form name, correct? I have the
requery placed in the afterupdate of the combo box. Is there anything else I
need to check?

Either I'm missing something here, or you've got something I
haven't seen yet that's out of whack?

The combox boxes' AfterUpdate is the right place to Requery
the subform. And, Yes, it is the name of the subform
**control** that you reference to do that:

Let's check the subform's record source query to make sure
the values of the combo boxes are used correctly. Post the
subform's recordsource query's SQL statement along with the
details of the combo box: its Row Source query, bound
column, column count, etc. Then we'll see where where we
are.

 

[Guest]

Removing the Master/Child relationship worked! Thank you! Thank you! Thank
you!

On the surface, your queries look ok. Only a nonessential
thing thing jumps out at me is that you probably want the
Requery in both combo box's AfterUpdate event.

But the problem is most likely some other issue. Are you
absolutely certain that the tbl_AppLocations.LocationID
field is the same value/type as tbl_Location.Location?
Myabe one is a numeric key and the other is a text name???

Also, you just mentioned that you are using Link
Master/Child properties on the subform control, could these
be in conflict with the criteria in the query? It is often
the case that you would use either the Link Master/Child
properties or criteria in the query but not both.
--
Marsh
MVP [MS Access]

Here is the query SQL:
SELECT tbl_AppLocations.LocApplicationID, tbl_AppLocations.LocationID,
tbl_AppLocations.ApplicationIssue, tbl_AppLocations.AppQuantity,
tbl_AppLocations.CMD, tbl_AppLocations.LoanAdmin, tbl_AppLocations.Balboa
FROM tbl_AppLocations
WHERE
(((tbl_AppLocations.LocationID)=[Forms]![frm_A_postmortem]![cbo_Location])
AND
((tbl_AppLocations.ApplicationIssue)=[Forms]![frm_A_postmortem]![cbo_Issue_Platform]));


cbo_Location / SELECT tbl_Location.Location FROM tbl_Location; / Bound
Column 1

cbo_Issue_Platform / SELECT tbl_Application_Issue.Application_Issue FROM
tbl_Application_Issue; / Bound Column 1

Karen53 wrote:
Thank you. Short and sweet. I had posted my question before about
requerying the subform. One of my responses was to reset the record source.
I like your way better. But I am still having trouble and it is driving me
crazy. I have double checked the control name of my sub form. I even
deleted it and recreated it. I have double checked the names of my fields on
the combo boxes. I have double checked the Master/Child relationship.
Somehow it has got to be a problem with my requery. It comes up correctly
the first time, until I change the selection and requery. It is supposted to
be the subform's CONTROL name and not the form name, correct? I have the
requery placed in the afterupdate of the combo box. Is there anything else I
need to check?

Either I'm missing something here, or you've got something I
haven't seen yet that's out of whack?

The combox boxes' AfterUpdate is the right place to Requery
the subform. And, Yes, it is the name of the subform
**control** that you reference to do that:

Let's check the subform's record source query to make sure
the values of the combo boxes are used correctly. Post the
subform's recordsource query's SQL statement along with the
details of the combo box: its Row Source query, bound
column, column count, etc. Then we'll see where where we
are.