deleting records

[kenjones]

I have two related tables, company is the one table, areas the many

I want to be able to delete records in the many table that match the criteria of a field in company

I have no problems selecting the records with the following;

-----------
Private Sub areas_find_empty_records_Click()

Dim stDocName As String
Dim strSQL As String

stDocName = "areas_find_empty_records"

strSQL = "SELECT * FROM areas INNER JOIN company ON areas.[areas#] = company.[record#]" _
& "WHERE company.main_name= 'empty'"

Me.RecordSource = strSQL

End Sub

-------------------------

I then replace SELECT with DELETE and I am asked to specify the table I want to delete the records from. So I have tried areas, [areas], [areas.*] but I keep getting the same error message

Any thoughts?

 

[YNWA]

Try listing the field names you want to delete? Instead of using *

 

[kenjones]

OK I now have the following to try to delete the one field

--------------------
Private Sub areas_delete_empty_records_Click()

Dim stDocName As String
Dim strSQL As String

stDocName = "areas_delete_empty_records"

strSQL = "DELETE areas.[country] FROM areas INNER JOIN company ON areas.[areas#] = company.[record#]" _
& "WHERE company.main_name= 'empty'"

Me.RecordSource = strSQL

End Sub
------------------
And I still get an error message;

Run-time error '3128':
Specify the table containing the records you want to delete

 

[jdraw]

Delete queries delete records not fields.

What does 'empty' mean?
Your 1 to many relationship requires that you have a field to identify company in both tables. Suppose that your Company Table has CompanyID, and your Areas table has
AreaId, and CompanyID.
It is the CompanyID that relates these Tables.

What, in plain English, is the "factor" that identifies which Areas records to DELETE?

Your delete query would have this general format

DELETE FROM Areas Inner JOIN Company ON
Area.CompanyID = Company.CompanyID
WHERE Company.main_name meets your 'empty' criteria

In this set up you are deleting all Areas records for those Companies that have no Main_name. Is that what you intend?

 

[kenjones]

'empty' is the value of the field company.main_name.

I dont think there is a problem with the relationship. If you see my first post SELECT works exactly as I would expect it to. The problem arises when I try to delete the records

I have tried your suggestion as follows;

strSQL = "DELETE FROM areas INNER JOIN company ON areas.[areas#] = company.[record#]" _ & "WHERE company.main_name= 'empty'"

I get the same error message;

Run-time error '3128':
Specify the table containing the records you want to delete