Subdatasheet problem

[Mute]

hello all,

I've come across another little problem in my database.

I have a subdatasheet in my form - this holds the boiler details for clients.

ie. clients form has within it details subdatasheet containg boiler details for that client.

I can add/edit boiler details without any problems but ive noticed that when i delete a client's records the boiler details are still there (although you cant access it through the form).

How do i delete the boiler details along with the client?

Thanx in advance.

 

[maxmangion]

do a search on this forum for referential integrity.

 

[Mute]

Tried to enforce referential integrity but i get the error msg:

Data in the table 'BoilerDet' violates referential integrity rules.

BoilerDet being the table used for the subdatasheet

 

[maxmangion]

you are getting that error message because you already have some boiler details, which you deleted their clients. These are known as orphans. Get rid of those first and then set the referential integrity again.

 

[Mute]

Great thanx,

That worked.

Is there a way to change the confirmation message to one of my own because the one which pops up when deleting a record can be confusing for users?

 

[Mute]

Just noticed sumthing,

my combobox which worked perfectly before now causes problems

when scrolling through the records.

I get a runtime error saying "the value you entered isnt valid for this field"

Whats gone wrong?

 

[maxmangion]

i do not know how you are deleting records, but by using a command button you can avoid getting the confirmation box.

Private Sub Command11_Click()
If MsgBox("Are you sure you want to delete the record", vbYesNo, "test") = vbYes Then
DoCmd.SetWarnings warningsoff
DoCmd.RunCommand acCmdDeleteRecord
Else
DoCmd.CancelEvent
MsgBox "Record not deleted", vbOKOnly
End If
End Sub

 

[Mute]

any ideas why i am now getting a runtime error msg?

 

[Mute]

This is what my current delete button has:

Private Sub DelRec_Click()
On Error GoTo Err_DelRec_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_DelRec_Click:
Exit Sub

 

[maxmangion]

the wizard creates that code, which unfortunately is obsolete. Try the code which i posted in my previous post, it should do the trick.

 

[Mute]

I've tried your code and i get an error.

Compile error: Variable not defined

and the WarningsOff is highlighted in the line

DoCmd.SetWarnings (WarningsOff)

any ideas y that happens?

 

[Mute]

PLZ PLZ PLZ help

I think this is the last thing wrong with my database and i hope someone can help me sort it out.

 

[maxmangion]

try removing the brackets between warningsoff

 

[Rich]

DoCmd.SetWarnings (WarningsOff)
should be

DoCmd.SetWarnings False,
don't forget to turn them back on with
DoCmd.SetWarnings True
at the end of your code

 

[maxmangion]

Rich suggestion is more accurate, however, on my version of Access (2000) with DoCmd.SetWarnings warningsoff, it deleted the record without confirmation.

 

[Mod]

Rich suggestion is more accurate, however, on my version of Access (2000) with DoCmd.SetWarnings warningsoff, it deleted the record without confirmation.

I've noticed this undefined behaviour myself. It doesn't through up any kind of error when using the constant warningsoff, but it will for warningson or any other. wierd.

Relying on the tips from typing is what got me in that trap in the first place

 

[maxmangion]

thank you for the information, therefore it will be better doing it the right way i.e. DoCmd.SetWarnings False

 

[Mute]

Woooo hooooo

Thank u all,

Rich's suggestion worked.

 

[maxmangion]

Good to hear that finally you sorted out your problem