Deleting a Record from a List Box Selection

[martinrobson]

Hi, please can you help?

I currently have a form which contains a list box, listing all the records in my database tables. I would like to be able to delete a record from the table using the listbox to hughlight it and a delete button to delete it.

I have tried a few different methods which i thought should work but havent managed it. It will get complicated as i need to think about foreign keys, but can someone give me the basic code i would need to allow this.

I have the ablility to ammend records from the listbox at the moment, so know how to get the selected item displayed, but would like to be able to delete rather than disply, if that makes sense.

Thanks in advance for your time.
Martin

 

[JamieDeVry]

From in the click event of your delete control, you will need to capture the record that was selected. In order to do that, you will need to create a variable. You will also need a variable to delete the record using a SQL statement. For example:

Sub cmdDelete_Click()
Dim intValue As Integer 'Assuming that
Dim strSQL As String

intValue = lstCustomer.Value
strSQL = "DELETE * FROM tblCustomer WHERE CustomerID=" intValue

With DoCmd
.SetWarnings False
.RunSQL = strSQL
.SetWarnings True
End With

End Sub

'God Bless

Jamie

http://nj.devry.edu/~jbates

 

[Kherozen]

hi here's my code and I can't figure why it doesn't work


[tt]
Dim intValue As Integer
Dim strSQL As String

intValue = Me.Project
strSQL = "DELETE * FROM dbo.[Project Info] WHERE dbo.[Project info].[Project]=" & intValue


DoCmd.RunSQL strSQL, 0
With DoCmd
.SetWarnings False
.RunSQL = strSQL
.SetWarnings True
End With
[/tt]

I get a "Invalid Syntax near *


jul ^_^
"Computer Science is no more about computers than astronomy is about telescopes"
E. W. Dijkstra.

 

[Kherozen]

2 Bugs resolves , here's the solution
[tt]
Dim intValue As Integer
Dim strSQL As String

intValue = Me.Project
strSQL = "DELETE FROM dbo.[Project Info] WHERE dbo.[Project info].[Project]=" & intValue


DoCmd.RunSQL strSQL, 0
With DoCmd
.SetWarnings False
.RunSQL strSQL
.SetWarnings True
End With


jul ^_^
"Computer Science is no more about computers than astronomy is about telescopes"
E. W. Dijkstra.

 

[Carthesis]

On a similar note, can anyone have a look at this for me and tell me why it doesn't work? I'll be jiggered if I can figure out why.

Private Sub cmdDeleteRecord_Click()

Dim strSQL As String

If Me![Table] = "Incoming" Then
    
     strSQL = "DELETE [tblIncoming].* FROM [tblIncoming] "
     strSQL = strSQL & "WHERE [tblIncoming].[OWCRefNo]='" & Me![OWCRefNo] & "';"
    
ElseIf Me![Table] = "Outgoing" Then
     
     strSQL = "DELETE [tblOutgoing].* FROM [tblOutgoing] "
     strSQL = strSQL & "WHERE [tblOutgoing].[OWCRefNo]='" & Me![OWCRefNo] & "';"
     
ElseIf Me![Table] = "Drawings" Then
    
     strSQL = "DELETE [tblDrawings].* FROM [tblDrawings] "
     strSQL = strSQL & "WHERE [tblDrawings].[DwgNo]='" & Me![OWCRefNo] & "';"
     
End If

DoCmd.RunSQL strSQL
With DoCmd
     .SetWarnings True
     .RunSQL strSQL
End With

End Sub

Me![Table]

is just a drop-down box, and

Me![OWCRefNo]

is just a text box that I enter the number of the record I want to delete into.

Help?

 

[Carthesis]

Just realised... I should really put in the error I'm getting... d'oh!

When I hit the button that this code sits behind, I get "data type mismatch in criteria expression" relating to the

DoCmd.RunSQL strSQL

line.

 

[Norris68]

Your SQL strings are trying to delete based on a string field - are they numbers? If so, omit the single quotes.

BTW: The semi-colon is optional, you don't have to put the tablename in front of the * ... in fact you don't even need the *! "DELETE FROM Tablename" will do