locked
Received an Action Query 1qry22CGInRange' cannot be used as a row source RRS feed

  • Question

  • I received the above error when generating a series of queries to produce labels. There is a recordsource on the form label called 'Rpr22Labels5160SelectedCGs' and is as follows:

    SELECT qry22CGInRangeSelected.DOB, qryPersonAddress.PersonName, qryPersonAddress.Street, qryPersonAddress.CSZ, qryPersonAddress.LastName, qryPersonAddress.FirstName
    FROM qry22CGInRangeSelected INNER JOIN qryPersonAddress ON qry22CGInRangeSelected.CGID = qryPersonAddress.PersonID;

    In turn qry22CGInRangeSelected looks as follows:

    SELECT hld22CGInRangeSelected.*

    FROM hld22CGInRangeSelected INNER JOIN qry22CGInRangeSelectedUnderlyingB ON hld22CGInRangeSelected.CGID = qry22CGInRangeSelectedUnderlyingB.CGID;

    The qry22CGInRangeSelectedUnderlyingB is as follows:

    SELECT qry22CGInRange.*

    FROM (((qry22CGInRange INNER JOIN qry22CGInRangeMaritalStatus ON qry22CGInRange.CGID = qry22CGInRangeMaritalStatus.CGID) INNER JOIN qry22CGInRangeProgram ON qry22CGInRange.CGID = qry22CGInRangeProgram.CGID) INNER JOIN qry22CGInRangeRelationshipToCaregiver ON qry22CGInRange.CGID = qry22CGInRangeRelationshipToCaregiver.CGID) INNER JOIN qry22CGInRangeZipcode ON qry22CGInRange.CGID = qry22CGInRangeZipcode.CGID

    WHERE (((qry22CGInRange.DataSource)=GetDataSourceNumber()));

    This contains the qury22CGInRange (query in question) as follows:

    SELECT DISTINCT tblCase.*, tblPerson.LastName, tblPerson.FirstName, tblPerson.DOB, Int(([Forms]![frmReportSubmenuParametersCG]![StartDate]-[DOB])/365.25) AS AgeAtStart, tblPerson.Gender, tblPerson.Ethnicity1, tblPerson.Ethnicity2, tblPerson.Ethnicity3, tblPerson.PrimaryLanguage, tblPerson.HomeAddressZip, qry22CaseOpenInRange.CaseOpenDate, qry22CaseOpenInRange.CaseCloseDate INTO hld22Labels5160SelectedCGs

    FROM tblPerson INNER JOIN (tblCase INNER JOIN qry22CaseOpenInRange ON tblCase.CaseID = qry22CaseOpenInRange.CaseID) ON tblPerson.PersonID = tblCase.CGID

    WHERE (((tblCase.DataSource)=GetDataSourceNumber()));

    I don't understand how I can be getting this error as I have similar layered sqls in another version that used the same recordsource and produce the labels.  Would appreciate any help on I can correct this and go on from here.

    Thanks again.

    L. Staley

    Friday, September 16, 2016 10:08 PM

Answers

  • This contains the qury22CGInRange (query in question) as follows:

    SELECT DISTINCT tblCase.*, tblPerson.LastName, tblPerson.FirstName, tblPerson.DOB, Int(([Forms]![frmReportSubmenuParametersCG]![StartDate]-[DOB])/365.25) AS AgeAtStart, tblPerson.Gender, tblPerson.Ethnicity1, tblPerson.Ethnicity2, tblPerson.Ethnicity3, tblPerson.PrimaryLanguage, tblPerson.HomeAddressZip, qry22CaseOpenInRange.CaseOpenDate, qry22CaseOpenInRange.CaseCloseDate INTO hld22Labels5160SelectedCGs

    FROM tblPerson INNER JOIN (tblCase INNER JOIN qry22CaseOpenInRange ON tblCase.CaseID = qry22CaseOpenInRange.CaseID) ON tblPerson.PersonID = tblCase.CGID

    WHERE (((tblCase.DataSource)=GetDataSourceNumber()));

    I don't understand how I can be getting this error as I have similar layered sqls in another version that used the same recordsource and produce the labels.  Would appreciate any help on I can correct this and go on from here.

    If you look closely, you'll see that qry22CGInRange is a make-table query (by virtue of having the SELECT ... INTO structure).  A make-table query is a type of action query, and action queries do not return recordsets (though they may create one, as in this case where a recordset is created and then stuffed into a table).  You can't use a make-table, or any action query, in a situation where you want the query to return records, because it won't.

    You'll either have to change the make-table query qry22CGInRange into a simple select query, or else create a new query that selects the same records (but without the INTO clause) and use that query to build your higher-level query.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Saturday, September 17, 2016 2:20 AM