Delete a record in Access based on a combo box selection

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Jack_Maloney

Thread Starter
Joined
Dec 21, 2010
Messages
4
Hi,

I am new to access and am struggling on trying to delete a record. Basically, I want the user to select an option from a drop down list (Select_Project_Type_cbx) and then click on a 'Delete' button which will delete the record associated with the drop down option selected.

This is the code I have so far:

Private Sub Delete_Project_Type_btn_Click()
Dim sDeleteRecordSQL As String
sDeleteRecordSQL = "Delete * From Project_Type_tbl " & _
" Where Project_Type_Name.Name = " & Select_Project_Type_cbx.Value
Me.Select_Project_Type_cbx.RowSource = sDeleteRecordSQL
Me.Select_Project_Type_cbx.Requery

End Sub


When the user clicks on the delete button it just removes ALL options from the combo box. Nothing is deleted from the table "Project_Type_tbl".

If someone can point me in the right direction on where I am going wrong here it would be much appreciated!

Thanks,
Jack
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Jack_Maloney, welcome to the forum.
This line is your problem
Me.Select_Project_Type_cbx.RowSource = sDeleteRecordSQL
it should be
set rs = sDeleteRecordSQL
where rs is dimmed as
dim rs as object
You could also run a delete query using the combo as a Criteria, or have the form's record source based on the table and convert the combo to a "Find Combo" and then delete the rcord on the form.
You should also have a Confiming Message something like
response = msgbox("Are you sure you want to Delete " & Select_Project_Type_cbx & "", vbYesNo + vbExclamation + vbDefaultButton2)
 

Jack_Maloney

Thread Starter
Joined
Dec 21, 2010
Messages
4
Thanks for the reply OBP.

I now get a message saying 'type mismatch' on the 'Set rs = sDeleteRecordSQL' line.

Code is:
Dim sDeleteRecordSQL As String
Dim rs As Object
sDeleteRecordSQL = "Delete * From Project_Type_tbl " & _
"Where Project_Type_Name.Name = " & Select_Project_Type_cbx.Value
Set rs = sDeleteRecordSQL
Me.Select_Project_Type_cbx.Requery

Also, where do you enter the code for the confirmation delete message? Is this entered below the code already entered in VB?

Thanks,
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Sorry , I should have checked what I was writing instead of relying on my old memory.
It should be
Set rs = CurrentDb.OpenRecordset(sDeleteRecordSQL)

The Message box should go before you actually doe the deletion like this

Dim response
response = MsgBox("Are You Really Sure You Want to Delete Record?", vbYesNo + vbExclamation + vbDefaultButton2)
If response = vbNo Then Exit Sub ' User chose No.
'User Chose Yes
 

Jack_Maloney

Thread Starter
Joined
Dec 21, 2010
Messages
4
Im now getting a run time error '3219' when it executes that line. Says 'Invalid Operation'. Any ideas why this might be occuring?

Thanks,
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Perhaps it does not like the Delete, you could try changing it to a Select query and see if it returns a Record for RS Recordset based on the SQL statement.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
OK, try the Run SQL version instead
DoCmd.RunSQL sDeleteRecordSQL
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top