There must be a way, surely?

Chris_lead
12-21-2003, 05:47 AM
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.

Shurik12
12-21-2003, 08:51 AM
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.

Chris_lead
12-21-2003, 09:00 AM
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?

reboot
12-21-2003, 09:07 AM
My crystal ball is broken, maybe you could show us the code?

Chris_lead
12-21-2003, 09:09 AM
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

Chris_lead
12-21-2003, 10:08 AM
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?

davie
12-21-2003, 10:18 AM
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.

Chris_lead
12-21-2003, 10:22 AM
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.

davie
12-21-2003, 10:27 AM
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.

Chris_lead
12-21-2003, 10:31 AM
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

davie
12-21-2003, 10:36 AM
put


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

Chris_lead
12-21-2003, 10:38 AM
What is that variable meant to be?:

intband_id

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

davie
12-21-2003, 10:40 AM
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.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum