Delete From Combobox

[ddrew]

I think this is fairly simple,rolleyes: ), I want to be able to delete names from a combobox, Im going to do it on a button and I know I need to do:

Me.Comboboxname.Requery

But what bomes before that?

Thanks

 

[Juan]

The opposite of the NotInList Event - I'm interested too! I guess that the query that fills the combobox (assuming you've done it that way) will also have to be reset to give the new values.

 

[ddrew]

Yes I would imagine so, its not something ive seen on here before though, so any help from the guru's would be appreciated.

 

[RuralGuy]

What is the RowSource and RowSourceType of the ComboBox?

 

[ddrew]

Row Source is:


SELECT ExerciseNametbl.ExerciseName FROM ExerciseNametbl ORDER BY [ExerciseName];

And Type is:

Table/Query

Thanks!

 

[RuralGuy]

CurrentDb.Execute "DELETE FROM ExerciseNametbl WHERE ExerciseName = " & Chr(34) & Me.CboboxName & Chr(34), dbFailOnError
Me.CboboxName.Requery

in the CboboxName AfterUpdate event should do it. Using your CboboxName of course and you will want to put some error handling code in there. It will permanently delete the ExerciseName from the table! Is that what you want to do?

 

[ddrew]

Thanks RG, thats what I need, but I'd rather do this on a button with a bit of "Are you sure you want to delete this Exercise Namefrom your Database, Yes No etc, could that be done?

 

[RuralGuy]

The same code will work under a button but you can surround this code with the "Are you sure" MsgBox where it is or under your button.

 

[ddrew]

Is it possible to delete records from another/other table(s) that are associated with the record that is being deleted?

 

[RuralGuy]

Of course. If you set up your relationships correctly and turn on Referential Integrity then you can use Cascading deletes if you wish. Just be careful because a lot of records can dissappear in a flash.

 

[ddrew]

OK now Im lost, Referential Integrity , never heard of it. How would I go about doing this?

 

[RuralGuy]

Maybe this link will be helpful. Referential Integrity can't be that easy! Also look around on Allen's site. There's a ton of useful information there.

 

[ddrew]

OK, ive had a look at Referential Integrity and I think my DB is within the criteria. The way it all works is I have a table, (ExerciseNametbl), this is unique and Indexed. All of my other tables have a field of the same name and are fed by the ExerciseNametb, (One to Many), so as far as I can see, it should work, I just dont know how to go about it!

 

[ddrew]

OK I did the Referential Integrity thing and pasted your code in an on_click event of a button, but when I pressed it I got this error message:

Run-time erreo '3200'
The record cannot be deleted or changed because table 'Armour_Selection' includes related records.

'Armour_Selection' is one of the tables in my DB of course. It only showed this error on the table because that was the only one that I had a related record in.

 

[RuralGuy]

Did you enable Cascade Deletes?

 

[ddrew]

I have now, thanks. Thats a pretty powerful button, as you rightly said. So I need to put a message box around it with a Yes No Option. Sorry for being a pain, but I know how to right the message box and how to get the Yes/No pop up, but Im unsure what code to put if the user chooses Yes or No. Can you help please?

 

[ddrew]

Cracked it! I think that will do for tonight, Thanks for all your help RG!

 

[RuralGuy]

Glad I could help.