Make sure every record has a sub record 1

[xyle]

I have 2 tables, joined in a one to many relationship. Is there any way I can test to see if every record in the main table has at least one record in the subtable before allowing the user to run the forms procedures?

 

[PHV]

To get a list of main records without child:
SELECT M.PrimaryKey, Count(C.ForeignKey) As CountOf Childs
FROM [Main table] AS M LEFT JOIN [Child Table] AS C ON M.PrimaryKey = C.ForeignKey
GROUP BY M.PrimaryKey
HAVING Count(C.ForeignKey) = 0

Another way:
SELECT M.*
FROM [Main table] AS M LEFT JOIN [Child Table] AS C ON M.PrimaryKey = C.ForeignKey
WHERE C.ForeignKey Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lupins46]

You can run a query to see if any records have no related records.

Modify this for your own tables and fieldnames.

Select table1.id from table1 left join table2
on table1.id = table2.table1ID
group by table1.id
having count(table2.id) = 0

 

[xyle]

Thats what I had in mind, but can I do this without setting the forms record source to a query like that.

 

[PHV]

You may play with the RowSource of a ListBox.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lupins46]

I think you need to decide what your question is:
' Is there any way I can test to see if every record in the main table has at least one record in the subtable before allowing the user to run the forms procedures?'

That is the question that has been answered and has nothing to do with form recordsources.

You now appear tbe saying that you want to open the form first. Please clarify your requirements.

 

[xyle]

Sorry let me give the a better explenation. Somewhere between opening the form and running a procedure under a command button I need to make sure every record in the main table has a subrecord.

 

[PHV]

Simply test the RecordCount property of a Recordset opened with one of the already posted SQL code.
Or, if you're not confident with recordsetset, have an hidden Listbox with RowSource set to the SQL code you prefer, and then in the Click event procedure of your button you may test the ListCount property and make the ListBox visible if not empty.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886