How to code my Delete Button ???

[JaeBrett]

I have a List Box that that returns a recall_date and recall_type based on the value of a text field where users put in pat_num. It is returned from a query. Now, how could I code my delete button so that when a user clicks a date/type in the list box, it knows what pat_num to delete. And then, I need it to update the box for me?

Any ideas anyone??

 

[sunshine19]

If you are not already bringing the pat_num into your list box, bring it as a column to your list box. You can hide the column for your pat_num so it doesn't show in the list box. In my code below I am assuming that your list box has 3 columns and the first column is the pat_num.

On the click event of the delete button:
Dim intSelected as Integer
Dim rsMyRecordset as Recordset
Dim strCriteria as String

For intSelected = 0 To lstYourListBox.ListCount - 1
If lstYourListBox.Selected(intSelected) Then
strCriteria = "pat_num = " lstYourListBox.Column(0)
'assuming you have table that you pulled your earlier query
Set rsMyRecordset = Currentdb.OpenRecordset("YourTableName",dbOpenDynaset)
With rsMyRecordset
'populate recordset
.MoveLast
.FindFirst strCriteria
.delete
End With
End If
Next intSelected
lstYourListBox.Requery

I am assuming above that you are going to allow your user to be able to select 1 to N in your list box to delete.

Hope this helps.

 

[JaeBrett]

Thanks for the swift response. However, it isn't and I suspect it is because I sometimes have more than one pat_num in the table. So when I use .Find, can I search using pat_num, recall_date, recall_type?

 

[sunshine19]

try this for strcriteria

strCriteria = "[recall_date] = #" & lstYourListBox.Column(1) & "# AND [pat_num] = '" & lstYourListBox.Column(0) & "' AND [recall_type] = '" & lstYourListBox.column(2) & "'"

I am assuming that your list box columns are pat_num, recall_date, & recall_type. I am assuming that pat_num & recall_type are text.

Hope this helps.

 

[JaeBrett]

I am getting an 'Invalid Argument' error. The error is stemming from the "Set rsMyRecordset = " line. Recalls is a valid table name ....

Any thoughts???

Dim intSelected As Integer
Dim strCriteria As String
Dim rsMyRecordset As Recordset

For intSelected = 0 To lstSavedRecalls.ListCount - 1
If lstSavedRecalls.Selected(intSelected) Then
strCriteria = "[recall_date] = #" & lstSavedRecalls.Column(0) & "# AND [pat_num] = '" & lstSavedRecalls.Column(2) & "' AND [recall_type] = '" & lstSavedRecalls.Column(1) & "'"
'assuming you have table that you pulled your earlier query
Set rsMyRecordset = CurrentDb.OpenRecordset("Recalls", dbOpenDynaset)
With rsMyRecordset
'populate recordset
.MoveFirst
.Find (strCriteria)
.Delete
.Save
End With
End If
Next intSelected
lstSavedRecalls.Requery

 

[sunshine19]

change .Find to .FindFirst and remove .Save

If you fix that it should work. .Save is not necessary.

 

[JaeBrett]

I changed .Find to .FindFirst and I get an error on ".FindFirst'

Method of Data member not found

 

[sunshine19]

Should have asked this earlier. What version of Access are you working with?

 

[JaeBrett]

Access 2002 with SP-2

 

[sunshine19]

I don't have that version. I have 97. Try it with .Find and not .save and see what results you get. The code I give you works for 97 but obviously does't for 2002. Maybe it is a conversion thing to ADO from DAO and the syntax has changed. Sorry that I am not much help. Maybe someone else can step up to the plate and answer your question.

 

[JaeBrett]

Thanks for your time just the same.

Anyone else out there wanna step up? Hehe!