Record count based on criteria

[Lute]

Hi,

I have two linked tables (header and detail) and want to update the detail
table with pre defined records if the detail record count for the detail
table (based on the header record) is zero. My problem is that is there are
no detail records relating to a header record then the recordcount returns an
error.

Any suggestions, the sql query that i'm using is below and I am linking this
to a recordcount in vba.

SELECT tbl_reviews.[Review ID]
FROM tbl_opportunity LEFT JOIN tbl_reviews ON tbl_opportunity.[Opportunity
ID] = tbl_reviews.[Opportunity ID]
GROUP BY tbl_reviews.[Review ID], tbl_opportunity.[Opportunity ID]
HAVING (((tbl_opportunity.[Opportunity
ID])=[Forms]![frm_opportunities]![Opportunity ID]));

Thanks

Lute

 

[Smartin]

Hi,

I have two linked tables (header and detail) and want to update the detail
table with pre defined records if the detail record count for the detail
table (based on the header record) is zero. My problem is that is there are
no detail records relating to a header record then the recordcount returns an
error.

Any suggestions, the sql query that i'm using is below and I am linking this
to a recordcount in vba.

SELECT tbl_reviews.[Review ID]
FROM tbl_opportunity LEFT JOIN tbl_reviews ON tbl_opportunity.[Opportunity
ID] = tbl_reviews.[Opportunity ID]
GROUP BY tbl_reviews.[Review ID], tbl_opportunity.[Opportunity ID]
HAVING (((tbl_opportunity.[Opportunity
ID])=[Forms]![frm_opportunities]![Opportunity ID]));

Thanks

Lute

In your VBA, try testing for {YourRecordset}.EOF before you try to tap
in to it (e.g., to get a record count).