How to delete a record from a list box?

[JenniferM]

I'm a newbie at this, so bear with me...

Background:
I've got a table of patients (TblPtDemographics) with an associated form (FrmPtDemographics). The primary key is PtID.
Each PtID has a list of surgeries in TblKneeSxInfo, with a primary key of SxKneeID.

Right now, I have a list box (LbxKneeSx) set up in FrmPtDemographics that shows the surgeries for the particular patient; my bound column is 2 (SxKneeID).
I've figured out how to make buttons to edit selected and add records to TblKneeSxInfo. I'm trying to have a delete button to remove the selected record, but have run into some problems.
I have a delete query (qryDeleteKneeSxInfo), but when I set the query to run upon a button click, I'm asked to enter a parameter value. I don't know how to make it recognize the specific list box selection here.... Please help! Thanks!

[NeoPa]

Deleting a record via a form involves the command :

Call DoCmd.RunCommand(Command:=acCmdDeleteRecord)

Rather than running a Delete query.

It's possible to do it via a query, but unnecessarily complicated.

JenniferM:
so bear with me...

PS. It's so rare to find this request properly spelt. Normally it involves a request to get nekkid, so kudos for that ;-)

[JenniferM]

The problem I'm running into with this is that when I place this on the click event on my button, it deletes the current record in TblPtDemographics.
The record I want to delete is located in TblKneeSxInfo, which is displayed in a listbox (It's shown in FrmPtDemographics, but is pulled via the listbox query).

I can manage to delete a record using my delete query, but it's just a pain to have to type in the parameter value in when I've already selected it from the list box.




Naked is just naked, and nekkid means you're up to no good!

[NeoPa]

Interesting. You're probably best off recreating the SQL of the delete query within the code of your form and including the selected value from the ListBox control as part of that SQL.

JenniferM:
Naked is just naked, and nekkid means you're up to no good!

Well, maybe I don't understand the terminology quite properly, but I've never found it to be no good!

[NeoPa]

By the way, I'm not trying to be unhelpful here. Creating and running SQL from a form is quite common fare here, but if you need help with that, you simply have to say (and post the relevant details we'll need) and we will be happy to help.

[JenniferM]

I do appreciate your help. I'm trying to teach myself, I can't say I have a strong grasp on it yet. I've never seen SQL incorporated into a form.... I know I'd have to change the command on my delete button, but I'm not sure to what.

Here's what I'm looking at:

On my button's click event, the only code I have is:

Private Sub BtnDeleteKneeSxForm_Click()
 
DoCmd.OpenQuery "qryDeleteKneeSxInfo"
 
End Sub

For the SQL in my query, I have:

DELETE TblKneeSurgeryInfo.SxKneeID, (etc, etc, all fields in table)
FROM TblKneeSurgeryInfo
WHERE (((TblKneeSurgeryInfo.SxKneeID)=[Forms]![FrmPtDemographics]![LbxKneeSx]));

Since this is functional (minus the inconvenience of having to enter the SxKneeID), I'd rather not make any drastic changes. I'm really new at this and afraid I would royally screw something up.
I really just don't know how to make my delete button realize what record I've clicked. The bound column is my value for SxKneeID, so I want it to find that value in the table and delete it.

Let me know if there's any more info I can provide! Thanks again for your patience and help.

[NeoPa]

I would actually expect the following to work in your query assuming the names for all the objects are correct :

WHERE (TblKneeSurgeryInfo.SxKneeID = Forms("FrmPtDemographics").LbxKneeSx)

This is a bit of a clumsy approach though. Let's look at an alternative (but before we do, be certain to make a copy of whatever you have that is working.) :

Private Sub BtnDeleteKneeSxForm_Click()
    Dim strSQL As String
    Dim db As DAO.Database
 
    Set db = CurrentDb
    strSQL = "DELETE " & _
             "FROM   [TblKneeSurgeryInfo] " & _
             "WHERE  ([SxKneeID] = "
    strSQL = strSQL & Me.LbxKneeSx & ")"
    Call db.Execute(strSQL)
End Sub

[JenniferM]

I'm getting a "Compile Error: Invalid use of property."

I entered all the fields from TblKneeSurgeryInfo after DELETE, and put the name of the table in the brackets after FROM.

I get what the aim is in the code here, but I'm not sure why I'm returning this error.

*Also, since I'm nonexperienced, is there a way to shorthand all the fields in a table instead of having them listed one by one?

[NeoPa]

Jennifer,

I don't know how I let that post slip out. It was unfinished :-(

I fixed it now. The table name was missing as you spotted. However, there is no requirement to include field names. It is not fields that are being deleted from the table but records. To answer your question though, should it be necessary to (in other scenarios), an asterisk (*) can be used to indicate all available fields. To be clear though, in this instance even the asterisk is not required.

Sorry to have wasted your time, but could you try again with the updated code and then tell me again clearly what happens. Thank you.

PS. Just noticed even line #5 was wrong - which almost certainly explains your error. Remember, if an error occurs, give full details including the message and the line it occurs on.

[JenniferM]

I'm getting an error message after making the following changes:

"Run-time error '3061':
Too few parameters. Expected 1."

It's highlighting Line 12 of the code.

Private Sub BtnDeleteKneeSxButton_Click()
 
Dim strSQL As String
Dim db As DAO.Database
 
Set db = CurrentDb
 
strSQL = "DELETE " & _
    "FROM [TblKneeSurgeryInfo] " & _
    "WHERE ([SxKneeID] = "
strSQL = strSQL & Me.LbxKneeSx & ")"
Call db.Execute(strSQL)
 
End Sub

[NeoPa]

Jennifer,

That indicates that it is failing to execute the SQL. I don't know what the SQL is as you use a variable I have no information about. Can you post a copy of the actual SQL in strSQL at the point just prior to line #12 please. That way I can consider what is happening.

I include some basic instructions below for getting the string in case you need help with that :

One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.

The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.

Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That's assuming you're not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.

[JenniferM]

The SQL that's being generated is:

DELETE FROM [TblKneeSurgeryInfo] WHERE ([SxKneeID] = Left)

I've attached a screen shot of what I'm looking at.

** Edit **

[NeoPa]

That's a lot clearer now. Would I be right in deducing that the string "Left" is the value you are looking for in [SxKneeID]?

Or is it more complicated than that?

If it is that simple then you are setting up your SQL incorrectly. If the value is textual (as opposed to numeric) it needs quotes (See Quotes (') and Double-Quotes (") - Where and When to use them).

On that assumption I've prepared an alternative routine, with the sole difference being the quotes in lines #8 & #9 :

Private Sub BtnDeleteKneeSxForm_Click()
    Dim strSQL As String
    Dim db As DAO.Database
 
    Set db = CurrentDb
    strSQL = "DELETE " & _
             "FROM   [TblKneeSurgeryInfo] " & _
             "WHERE  ([SxKneeID] = '"
    strSQL = strSQL & Me.LbxKneeSx & "')"
    Call db.Execute(strSQL)
End Sub

[JenniferM]

Good Lord!
The value "left" was not was what I was looking for at all. The bound column on my list box was one off (was 2 and it should have been 3). The value for that should have been "LK888888S2." Then I added the quotes in lines 8 & 9 and it worked beautifully.... Thank you so much!!

[NeoPa]

A pleasure to help Jennifer :-)

I guess now post #11 has a whole new understanding for you ;-)