deleting a table and record at the same time!

[bmwilson]

I have a form called EnterNewComponent with a combobox called NewComponentName (its control source is ComponentType (a table)).

This is just one little form that acts like a dialogue box. If the user presses the delete button (called DeleteComponent) it deletes the table with the name that matches the combobox value and the record in the ComponentType table.

Basically they pick a name from the combobox and press delete. This deletes the relevant table and record from the db.

Ive been stuck on this for ages and just putting it off all the time, if you can help it would be greatly appreciated.

Ben

PS: I hope it all makes sense.

 

[Fornatian]

'Delete table
Docmd.DeleteObject Me.ComboName

'Delete record
Docmd.RunSQL "Delete * FROM MyTable Where MyField = '"& Me.ComboName & "'"
'use single quotes if the combo is a text field, none if not.

HTH

Ian

 

[bmwilson]

Didnt work....

The code I have for deleting the table is...

------------------------
Dim strTableName As String
On Error GoTo Err_DeleteComponent_Click

strTableName = NewComponentName.Value
DoCmd.RunSQL "DROP TABLE " & strTableName
------------------------

Ive tried a delete query, and couldnt get it to work... this code (above) deletes the correct table every time... its just deleting the record from the table that sucks.

Ben

 

[Fornatian]

have you tried using a delete query to delete the record?

Docmd.Setwarnings False
Docmd.OpenQuery "DeleteQueryName"
Docmd.Setwarnings True

 

[bmwilson]

yeah i have tried a delete query... it would never delete the right record!

Could you help me with one?

I can set them up but the Condition is where it falls down for me I think... actually choosing the right record to delete!

 

[Fornatian]

Post the entire code sub you have so far..

 

[bmwilson]

Private Sub DeleteComponent_Click()
Dim strTableName As String
On Error GoTo Err_DeleteComponent_Click

strTableName = NewComponentName.Value
'MsgBox strTableName
DoCmd.RunSQL "DROP TABLE " & strTableName
'CurrentDb.Execute "DeleteComponentTypeQuery", acEdit
'Delete table

'DoCmd.DeleteObject Me.NewComponentName
'Delete record
DoCmd.RunSQL "Delete * FROM ComponentTypes Where ComponentType = '" & Me.NewComponentName & "'"
'use single quotes if the combo is a text field, none if not.

Me.NewComponentName.SetFocus
Me.NewComponentName.Value = ""
Me.NewComponentName.Requery

Err_DeleteComponent_Click:
Exit Sub
End Sub
---------------------------------------------
---------------------------------------------
The query no longer exists... ideally I want to do it in code, but if it has to be a query then thats ok too...

PS: sorry about the mess...!

 

[Fornatian]

'delete the table object
Dim strTableName as String
strTableName = NewComponentName.Value
MsgBox strTableName - DO THIS GIVE YOU THE CORRECT STRING?
docmd.DeleteObject acTable,strTableName

'delete the related query
Dim strQryName as String
strQRYName = "TheQueryName"
Docmd.DeleteObject acQuery,strQryName

'delete the record in the table:

DoCmd.RunSQL "Delete * FROM ComponentTypes Where ComponentTypes.ComponentType = '" & Me.NewComponentName & "';"

I can't see what's wrong with it, maybe I'm not seeing the full picture? If it doesn't work come back and tell me what you are trying to do.

Ian

 

[bmwilson]

thankyouthankyouthankyouthankyouthankyouthankyouthankyouthankyouthankyouthankyouthankyou

worked like a charm!

weeks of hair pulling has ended!