There must be a way, surely?

Anyone know why I cannot delete a record when it is related to another table?

I get the run-time error:
'The record cannot be deleted or changed because table 'songs' includes related records'

How can I solve this? (If it helps, when I delete the record, I don't mind if it deletes all data related to that record.)

Cheers.

Hi,

Are you working in Access? If so you might want to set relationships between the tables. Then make it sure that Enforce Referential Integrity/Cascade Delete Related records options are checked.

Regards,
Shurik.

Hey cheers, that did it, but one major problem now.
(I have a list box with my records in)

It deletes the top record in my list, not the one I select.

Any thoughts?

My crystal ball is broken, maybe you could show us the code?

rs.Close
rs.LockType = adLockOptimistic


Dim cnn As New ADODB.Connection
cnn.CursorLocation = adUseClient
cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & App.Path & "\music_data.mdb"
cnn.Open
rs.CursorType = adOpenDynamic
rs.ActiveConnection = cnn
rs.Open "Bands"
BandList.ListField = "Band_name"
BandList.BoundColumn = "band_id"
Set BandList.RowSource = rs

With rs
rs.Delete (adAffectCurrent)
rs.MoveNext
If rs.EOF Then rs.MoveLast

End With

End Sub

lol; i no the feeling

If anyone doesn't know a solution to the above, then how about this:

How would I make it so that If I wanted to add a record, add the record from another form?

I.e. One option for the user is to add a 'song' to an already existing 'band'. (These are related as a one to many). I want to use a combo box for the user to select a band (I have done this) and a text box for the user to input the new song. So has anyone any ideas on the code for this? so for the newly inputted song to be related to the band selected from the combo box.

Any ideas?

and option 2 being the user inputs a NEW band and inputs all the songs that that band has got. I.e:

Input band:
__________

Input song 1
__________

Song 2
__________

etc. Then these populate the listbox on the main form which holds the band and related is the combo box populated wiht the assigned songs. Phew...

Any ideas?

Look for the Band index in the songs table and remove them before you remove the band record. This will therefore stop any of the errors that you are getting.

ok, but my problem is that when I delete a band, it doesn't delete the band I select, it just deletes the top record in my listbox.

and the above is what I am eventually going to want to achieve

any other ideas on that would be appriciated, otherwise thanks for giving your though.

Why don't you try running an action query, such as:


"DELETE * FROM tbBand WHERE (BandID_PK = " & intBandID & ")"


all you need is to get the index of the selected band from your listbox.

When I use that code, all i get is the error message that there is a compile error; where do I need to put it or do i need to decalre anything first?

Forget that, but i am now getting the error that the variable is not defined. in the code:

rs "DELETE * FROM bands WHERE (band_id_PK = " & intband_id & ")"

The undifined variable being:
intband

put


cnn.execute "DELETE * FROM bands WHERE (band_id_PK = " & intband_id & ")"

What is that variable meant to be?:

intband_id

Maybe I have changed it to somehting it is not in my project.

all you need is to get the index of the selected band from your listbox.


intband_id is the index of the selected band from your listbox.