RecordSource

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Karen53 - 03 May 2005 19:31 GMT

Hi,

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

I have tried this..

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

Thanks

Reply to this Message

Karen53 - 03 May 2005 20:07 GMT

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]))"

> Hi,
>
[quoted text clipped - 5 lines]
>
> Thanks

Reply to this Message

Marshall Barton - 03 May 2005 21:15 GMT

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.

Signature

Marsh
MVP [MS Access]

>I have also tried this. I do not get the data.
>
[quoted text clipped - 6 lines]
>> I have tried this..
>> Me.subForm.Form.RecordSource = Query![qryName]

Reply to this Message

Karen53 - 03 May 2005 21:50 GMT

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
[quoted text clipped - 18 lines]
> >> I have tried this..
> >> Me.subForm.Form.RecordSource = Query![qryName]

Reply to this Message

Marshall Barton - 04 May 2005 00:51 GMT

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).

Signature

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.
[quoted text clipped - 22 lines]
>> >> I have tried this..
>> >> Me.subForm.Form.RecordSource = Query![qryName]

Reply to this Message

Marshall Barton - 03 May 2005 21:05 GMT

>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"

Signature

Marsh
MVP [MS Access]

Reply to this Message

Karen53 - 03 May 2005 21:52 GMT

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?
> >
[quoted text clipped - 5 lines]
>
> Me.subForm.Form.RecordSource = "qryName"

Reply to this Message

Marshall Barton - 04 May 2005 00:55 GMT

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.

Signature

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
[quoted text clipped - 9 lines]
>>
>> Me.subForm.Form.RecordSource = "qryName"

Reply to this Message

Karen53 - 04 May 2005 04:43 GMT

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
[quoted text clipped - 14 lines]
> >>
> >> Me.subForm.Form.RecordSource = "qryName"

Reply to this Message

Marshall Barton - 04 May 2005 14:06 GMT

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

Signature

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.
[quoted text clipped - 27 lines]
>> >>
>> >> Me.subForm.Form.RecordSource = "qryName"

Reply to this Message

Karen53 - 04 May 2005 23:38 GMT

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
[quoted text clipped - 43 lines]
> >> >>
> >> >> Me.subForm.Form.RecordSource = "qryName"

Reply to this Message

Marshall Barton - 05 May 2005 22:46 GMT

>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.
[quoted text clipped - 7 lines]
>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.

Signature

Marsh
MVP [MS Access]

Reply to this Message

Karen53 - 06 May 2005 06:30 GMT

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!

> >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.
[quoted text clipped - 21 lines]
> column, column count, etc. Then we'll see where where we
> are.

Reply to this Message

Karen53 - 09 May 2005 15:14 GMT

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

Reply to this Message

Marshall Barton - 09 May 2005 16:33 GMT

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.

Signature

Marsh
MVP [MS Access]

>Here is the query SQL:
>SELECT tbl_AppLocations.LocApplicationID, tbl_AppLocations.LocationID,
[quoted text clipped - 37 lines]
>> column, column count, etc. Then we'll see where where we
>> are.

Reply to this Message

Karen53 - 09 May 2005 17:41 GMT

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
[quoted text clipped - 51 lines]
> >> column, column count, etc. Then we'll see where where we
> >> are.

Reply to this Message

Karen53 - 09 May 2005 17:44 GMT

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

Reply to this Message

Karen53 - 03 May 2005 22:19 GMT

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?
> >
[quoted text clipped - 5 lines]
>
> Me.subForm.Form.RecordSource = "qryName"

Reply to this Message