469,292 Members | 1,318 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,292 developers. It's quick & easy.

How to delete a record from a list box?

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!
Oct 22 '10 #1

✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub BtnDeleteKneeSxForm_Click()
  2.     Dim strSQL As String
  3.     Dim db As DAO.Database
  4.  
  5.     Set db = CurrentDb
  6.     strSQL = "DELETE " & _
  7.              "FROM   [TblKneeSurgeryInfo] " & _
  8.              "WHERE  ([SxKneeID] = '"
  9.     strSQL = strSQL & Me.LbxKneeSx & "')"
  10.     Call db.Execute(strSQL)
  11. End Sub

14 6250
NeoPa
32,173 Expert Mod 16PB
Deleting a record via a form involves the command :
Expand|Select|Wrap|Line Numbers
  1. 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 ;-)
Oct 22 '10 #2
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!
Oct 22 '10 #3
NeoPa
32,173 Expert Mod 16PB
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!
Oct 22 '10 #4
NeoPa
32,173 Expert Mod 16PB
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.
Oct 22 '10 #5
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub BtnDeleteKneeSxForm_Click()
  2.  
  3. DoCmd.OpenQuery "qryDeleteKneeSxInfo"
  4.  
  5. End Sub
For the SQL in my query, I have:

Expand|Select|Wrap|Line Numbers
  1. DELETE TblKneeSurgeryInfo.SxKneeID, (etc, etc, all fields in table)
  2. FROM TblKneeSurgeryInfo
  3. 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.
Oct 22 '10 #6
NeoPa
32,173 Expert Mod 16PB
I would actually expect the following to work in your query assuming the names for all the objects are correct :
Expand|Select|Wrap|Line Numbers
  1. 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.) :
Expand|Select|Wrap|Line Numbers
  1. Private Sub BtnDeleteKneeSxForm_Click()
  2.     Dim strSQL As String
  3.     Dim db As DAO.Database
  4.  
  5.     Set db = CurrentDb
  6.     strSQL = "DELETE " & _
  7.              "FROM   [TblKneeSurgeryInfo] " & _
  8.              "WHERE  ([SxKneeID] = "
  9.     strSQL = strSQL & Me.LbxKneeSx & ")"
  10.     Call db.Execute(strSQL)
  11. End Sub
Oct 25 '10 #7
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?
Oct 28 '10 #8
NeoPa
32,173 Expert Mod 16PB
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.
Oct 28 '10 #9
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub BtnDeleteKneeSxButton_Click()
  2.  
  3. Dim strSQL As String
  4. Dim db As DAO.Database
  5.  
  6. Set db = CurrentDb
  7.  
  8. strSQL = "DELETE " & _
  9.     "FROM [TblKneeSurgeryInfo] " & _
  10.     "WHERE ([SxKneeID] = "
  11. strSQL = strSQL & Me.LbxKneeSx & ")"
  12. Call db.Execute(strSQL)
  13.  
  14. End Sub
Nov 1 '10 #10
NeoPa
32,173 Expert Mod 16PB
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.
Nov 1 '10 #11
The SQL that's being generated is:

Expand|Select|Wrap|Line Numbers
  1. DELETE FROM [TblKneeSurgeryInfo] WHERE ([SxKneeID] = Left)
I've attached a screen shot of what I'm looking at.

** Edit **

Attached Images
File Type: jpg DeleteBtnCodeScrSht.jpg (31.1 KB, 2262 views)
Nov 1 '10 #12
NeoPa
32,173 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub BtnDeleteKneeSxForm_Click()
  2.     Dim strSQL As String
  3.     Dim db As DAO.Database
  4.  
  5.     Set db = CurrentDb
  6.     strSQL = "DELETE " & _
  7.              "FROM   [TblKneeSurgeryInfo] " & _
  8.              "WHERE  ([SxKneeID] = '"
  9.     strSQL = strSQL & Me.LbxKneeSx & "')"
  10.     Call db.Execute(strSQL)
  11. End Sub
Nov 2 '10 #13
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!!
Nov 2 '10 #14
NeoPa
32,173 Expert Mod 16PB
A pleasure to help Jennifer :-)

I guess now post #11 has a whole new understanding for you ;-)
Nov 2 '10 #15

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Johan | last post: by
2 posts views Thread by Chris Taylor | last post: by
reply views Thread by RAllsopp | last post: by
reply views Thread by New to Perl | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.