Deleting a selection from a combobox

[Beany]

hi,

has any1 got any examples i can look at for the following:




[PHP]A delete button that deletes the item selected in the combobox [/PHP]




i need it urgently???????please

or even any code!!!!

[nico5038]

Assuming the combobox named "cmbX" has a recordsource like:

select code, description from tblX

You can code a button with:

'  check selection made
IF len(nz(me.cmbX)) > 0 then
    ' yes, delete
    currentdb.execute ("delete * from tblX where code=" & me.cmbX)
    me.cmbX.requery
endif
 

Getting the idea ?

Nic;o)

[Beany]

ive tried your idea Nico....

the combobox is called Combo2
the row source of Combo2 is:

[PHP]SELECT [tab_main].[username] FROM tab_main ORDER BY [username];[/PHP]

the code im using for the delete button is:

[PHP]Private Sub Command11_Click()
On Error GoTo Err_Command11_Click

If Len(Nz(Me.Combo2)) > 0 Then

CurrentDb.Execute ("delete * from tab_main where code=" & Me.Combo2)
Me.Combo2.Requery
End If
Exit_Command11_Click:
Exit Sub

Err_Command11_Click:
MsgBox Err.Description
Resume Exit_Command11_Click

End Sub[/PHP]

but im getting the following error message:

Syntax error (missing operator) in query expression 'code=Adam Beaky'.

Adam Beaky is an example selected from combobox

[nico5038]

Hmm, I assumed the code was numeric, for a string embedding in single quotes is mandatory like:

"SELECT * FROM x WHERE code='" & me.combobox & "'")

Or when you have names wih an embedded quote use:

"SELECT * FROM x WHERE code=" & chr(34) & me.combobox & chr(34))

Nic;o)

[Beany]

Ok Nico ive changed it to the following:

[PHP]Private Sub Command11_Click()
On Error GoTo Err_Command11_Click

If Len(Nz(Me.Combo2)) > 0 Then

CurrentDb.Execute ("SELECT * FROM tab_main WHERE code='" & Me.Combo2 & "'")

Me.Combo2.Requery
End If
Exit_Command11_Click:
Exit Sub

Err_Command11_Click:
MsgBox Err.Description
Resume Exit_Command11_Click

End Sub[/PHP]

am i doing sumat wrong?

im getting a error message saying Cannot execute a select query..

is it possible if you could please modify this code for me?

[NeoPa]

Ok Nico ive changed it to the following:

[PHP]Private Sub Command11_Click()
On Error GoTo Err_Command11_Click

If Len(Nz(Me.Combo2)) > 0 Then

CurrentDb.Execute ("SELECT * FROM tab_main WHERE code='" & Me.Combo2 & "'")

Me.Combo2.Requery
End If
Exit_Command11_Click:
Exit Sub

Err_Command11_Click:
MsgBox Err.Description
Resume Exit_Command11_Click

End Sub[/PHP]

am i doing sumat wrong?

im getting a error message saying Cannot execute a select query..

is it possible if you could please modify this code for me?

Yes Beany ;).
But it's a simple fix.

Private Sub Command11_Click()
On Error GoTo Err_Command11_Click
 
If Len(Nz(Me.Combo2)) > 0 Then
 
    CurrentDb.Execute ("DELETE * FROM tab_main WHERE code='" & Me.Combo2 & "'")
 
    Me.Combo2.Requery
End If
Exit_Command11_Click:
    Exit Sub
 
Err_Command11_Click:
    MsgBox Err.Description
    Resume Exit_Command11_Click
 
End Sub

(See post #2 for what Nico originally suggested.)

[Beany]

Neo ive inserted the code what you modified (select -> delete(thanks))....

but when i select a name from the combobox and click the delete button i get the following message:

[HTML]Too few parameters. Expected 1[/HTML]

why is that?

[nico5038]

This implies that you use an unknown fieldname.
You will have to change the "code" into the fieldname from your table.

Nic;o)

[Beany]

Sorry about this, but i dont really understand what your suggesting?


(i bet you must be sick of me):)

[MMcCarthy]

Sorry about this, but i dont really understand what your suggesting?


(i bet you must be sick of me):)

In your table tab_main do you have a field named code?

[Beany]

No Mary, there is no field named 'code'..........

ive got the following fields:

[PHP]userID, number, username,cost_centre,phone_model,imei,sim_no,puk_c ode,date_issued,notes,tariff, roam, call_int[/PHP]

[MMcCarthy]

No Mary, there is no field named 'code'..........

ive got the following fields:

[PHP]userID, number, username,cost_centre,phone_model,imei,sim_no,puk_c ode,date_issued,notes,tariff, roam, call_int[/PHP]

Which one corresponds to the value in the Combo2 combo box?

[Beany]

IT WORKS.........................

i changed code to username................................ and it works...........

can i kiss you Mary? :)
thank you very much............

last thing.... is there a way of adding a warning message to this?

ie are you sure you want to delete this user?

[MMcCarthy]

Try this ...

Private Sub Command11_Click()
On Error GoTo Err_Command11_Click
Dim rslt As Integer
 
   If Len(Nz(Me.Combo2)) > 0 Then
      rslt=Msgbox("Are you sure you wish to delete " & Me.Combo2 & " from the table?", vbYesNo)
      If rslt=vbYes Then
         CurrentDb.Execute ("DELETE * FROM tab_main WHERE username='" & Me.Combo2 & "'")
         Me.Combo2.Requery
      End If
   End If
 
Exit_Command11_Click:
 
    Exit Sub
 
Err_Command11_Click:
 
    MsgBox Err.Description
    Resume Exit_Command11_Click
 
End Sub 
 

Mary

[Beany]

perfecto............

THANK YOU VERY MUCH............... GOD Bless you........

you know ur 5hit!!!!!!

[MMcCarthy]

perfecto............

THANK YOU VERY MUCH............... GOD Bless you........

you know ur 5hit!!!!!!

You can also use a message box like this around your insert statement in your other question to allow the user to cancel the operation.

Mary

[Beany]

excellent..... is it just a simple copy and paste job lol (with the obvious changes?)

[NeoPa]

excellent..... is it just a simple copy and paste job lol (with the obvious changes?)

That really depends on what's 'obvious'.
The answer should be yes, but try it and see.
Repost if you can't get it to work for yourself. You should have picked up quite a lot about all this by now :).

[Beany]

true true lol lol im genious now :) :)