Lisbox Need Delete Function

[DracKewl]

Hey All,

This is what I need to do.
1. Load a list box using ADO.
2. I do not want the recordset to be associated to the form. With my
present routine I have to associate it so I can delete items.
3. For simplistic purposes I want the recordset deleted from the list as it
is picked, I do not what that recordset deleted from the datasource.

I'm connecting to postgreSQL database. My intention is that when a selected
item is removed from the list, I will take one of the field values from the
recordset and pass it into a stored procedure and let the backend delete the
record for me.

So far I can delete items as long as they are not the first record;
otherwise the whole form goes blank. The other thing I noticed is that the
rs.RecordCount returns zero once one record is deleted even though there are
many.

The other thing is that I’m not sure that filtering out the record is the
best approach before deleting. But it’s the only way I can get it to remove
the record.

Here is my code. It’s pretty simple.

Option Compare Database
Option Explicit
Private conn As New ADODB.Connection
Private rs As New ADODB.Recordset

Private Sub cmdLoadBox_Click()
' Initializaition
Set conn = New ADODB.Connection
conn.Open "DRIVER={PostgreSQL
Unicode};SERVER=localhost;port=5432;DATABASE=PDM_Dev;UID=postgres;PWD=ajberlyn;"

Set rs = New ADODB.Recordset
With rs
.ActiveConnection = conn
.LockType = adLockBatchOptimistic
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.Open "SELECT * FROM customer", options:=adCmdText
.ActiveConnection = Nothing
End With

'Add recordsets to Listbox
With List0
.RowSourceType = "Table/Query"
.ColumnCount = rs.Fields.Count
.ColumnHeads = True
End With
Set List0.Recordset = rs ' Assign data into the listbox

Set Me.Recordset = rs ' Assign RS as the recordset for the form

conn.Close

End Sub

Private Sub List0_Click()
' Delete the record that matches the selection
rs.Filter = "CUSTOMER_ID = '" & Me![List0] & "'"
rs.Delete

End Sub

 

[Douglas J Steele]

What version of Access? Assuming Access 2002 or newer, you could try using
the .AddItem method rather than binding to a recordset. That way, you can
use .DeleteItem to remove what you no longer need in the listbox.

However, if you're saying that the record will be deleted frm the back-end,
perhaps all you need to do is require the list box once the record has been
deleted.

 

[DracKewl]

I'm using 2003. I'll give your solution a try and I'll post back my results.
Thanks.

What version of Access? Assuming Access 2002 or newer, you could try using
the .AddItem method rather than binding to a recordset. That way, you can
use .DeleteItem to remove what you no longer need in the listbox.

However, if you're saying that the record will be deleted frm the back-end,
perhaps all you need to do is require the list box once the record has been
deleted.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hey All,

This is what I need to do.
1. Load a list box using ADO.
2. I do not want the recordset to be associated to the form. With my
present routine I have to associate it so I can delete items.
3. For simplistic purposes I want the recordset deleted from the list as it
is picked, I do not what that recordset deleted from the datasource.

I'm connecting to postgreSQL database. My intention is that when a selected
item is removed from the list, I will take one of the field values from the
recordset and pass it into a stored procedure and let the backend delete the
record for me.

So far I can delete items as long as they are not the first record;
otherwise the whole form goes blank. The other thing I noticed is that the
rs.RecordCount returns zero once one record is deleted even though there are
many.

The other thing is that I'm not sure that filtering out the record is the
best approach before deleting. But it's the only way I can get it to remove
the record.

Here is my code. It's pretty simple.

Option Compare Database
Option Explicit
Private conn As New ADODB.Connection
Private rs As New ADODB.Recordset

Private Sub cmdLoadBox_Click()
' Initializaition
Set conn = New ADODB.Connection
conn.Open "DRIVER={PostgreSQL
Unicode};SERVER=localhost;port=5432;DATABASE=PDM_Dev;UID=postgres;PWD=ajberl
yn;"

Set rs = New ADODB.Recordset
With rs
.ActiveConnection = conn
.LockType = adLockBatchOptimistic
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.Open "SELECT * FROM customer", options:=adCmdText
.ActiveConnection = Nothing
End With

'Add recordsets to Listbox
With List0
.RowSourceType = "Table/Query"
.ColumnCount = rs.Fields.Count
.ColumnHeads = True
End With
Set List0.Recordset = rs ' Assign data into the listbox

Set Me.Recordset = rs ' Assign RS as the recordset for the form

conn.Close

End Sub

Private Sub List0_Click()
' Delete the record that matches the selection
rs.Filter = "CUSTOMER_ID = '" & Me![List0] & "'"
rs.Delete

End Sub

 

[DracKewl]

Hi,

I'm finding that to use additem the listbox has to be in Value List mode.
The recordset that I'm returning has as many as 30 columns.

---I wish this code worked! LOL---
rs.MoveFirst
While Not rs.EOF
List0.AddItem rs
rs.MoveNext
Wend

What version of Access? Assuming Access 2002 or newer, you could try using
the .AddItem method rather than binding to a recordset. That way, you can
use .DeleteItem to remove what you no longer need in the listbox.

However, if you're saying that the record will be deleted frm the back-end,
perhaps all you need to do is require the list box once the record has been
deleted.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hey All,

This is what I need to do.
1. Load a list box using ADO.
2. I do not want the recordset to be associated to the form. With my
present routine I have to associate it so I can delete items.
3. For simplistic purposes I want the recordset deleted from the list as it
is picked, I do not what that recordset deleted from the datasource.

I'm connecting to postgreSQL database. My intention is that when a selected
item is removed from the list, I will take one of the field values from the
recordset and pass it into a stored procedure and let the backend delete the
record for me.

So far I can delete items as long as they are not the first record;
otherwise the whole form goes blank. The other thing I noticed is that the
rs.RecordCount returns zero once one record is deleted even though there are
many.

The other thing is that I'm not sure that filtering out the record is the
best approach before deleting. But it's the only way I can get it to remove
the record.

Here is my code. It's pretty simple.

Option Compare Database
Option Explicit
Private conn As New ADODB.Connection
Private rs As New ADODB.Recordset

Private Sub cmdLoadBox_Click()
' Initializaition
Set conn = New ADODB.Connection
conn.Open "DRIVER={PostgreSQL
Unicode};SERVER=localhost;port=5432;DATABASE=PDM_Dev;UID=postgres;PWD=ajberl
yn;"

Set rs = New ADODB.Recordset
With rs
.ActiveConnection = conn
.LockType = adLockBatchOptimistic
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.Open "SELECT * FROM customer", options:=adCmdText
.ActiveConnection = Nothing
End With

'Add recordsets to Listbox
With List0
.RowSourceType = "Table/Query"
.ColumnCount = rs.Fields.Count
.ColumnHeads = True
End With
Set List0.Recordset = rs ' Assign data into the listbox

Set Me.Recordset = rs ' Assign RS as the recordset for the form

conn.Close

End Sub

Private Sub List0_Click()
' Delete the record that matches the selection
rs.Filter = "CUSTOMER_ID = '" & Me![List0] & "'"
rs.Delete

End Sub

 

[Douglas J Steele]

Try the following:

rs.MoveFirst
While Not rs.EOF
For intLoop = 0 To (rs.Fields.Count - 1)
strItem = strItem & rs.Fields(intLoop) & ";"
Next intLoop
strItem = Left(strItem, Len(strItem)-1)
List0.AddItem strItem
rs.MoveNext
Wend


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi,

I'm finding that to use additem the listbox has to be in Value List mode.
The recordset that I'm returning has as many as 30 columns.

---I wish this code worked! LOL---
rs.MoveFirst
While Not rs.EOF
List0.AddItem rs
rs.MoveNext
Wend

What version of Access? Assuming Access 2002 or newer, you could try using
the .AddItem method rather than binding to a recordset. That way, you can
use .DeleteItem to remove what you no longer need in the listbox.

However, if you're saying that the record will be deleted frm the back-end,
perhaps all you need to do is require the list box once the record has been
deleted.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hey All,

This is what I need to do.
1. Load a list box using ADO.
2. I do not want the recordset to be associated to the form. With my
present routine I have to associate it so I can delete items.
3. For simplistic purposes I want the recordset deleted from the list

as
it

is picked, I do not what that recordset deleted from the datasource.

I'm connecting to postgreSQL database. My intention is that when a selected
item is removed from the list, I will take one of the field values

from
the

recordset and pass it into a stored procedure and let the backend

delete
the

record for me.

So far I can delete items as long as they are not the first record;
otherwise the whole form goes blank. The other thing I noticed is

that
the

rs.RecordCount returns zero once one record is deleted even though

there
are

many.

The other thing is that I'm not sure that filtering out the record is the
best approach before deleting. But it's the only way I can get it to remove
the record.

Here is my code. It's pretty simple.

Option Compare Database
Option Explicit
Private conn As New ADODB.Connection
Private rs As New ADODB.Recordset

Private Sub cmdLoadBox_Click()
' Initializaition
Set conn = New ADODB.Connection
conn.Open "DRIVER={PostgreSQL

Unicode};SERVER=localhost;port=5432;DATABASE=PDM_Dev;UID=postgres;PWD=ajberl
yn;"

Set rs = New ADODB.Recordset
With rs
.ActiveConnection = conn
.LockType = adLockBatchOptimistic
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.Open "SELECT * FROM customer", options:=adCmdText
.ActiveConnection = Nothing
End With

'Add recordsets to Listbox
With List0
.RowSourceType = "Table/Query"
.ColumnCount = rs.Fields.Count
.ColumnHeads = True
End With
Set List0.Recordset = rs ' Assign data into the listbox

Set Me.Recordset = rs ' Assign RS as the recordset for the form

conn.Close

End Sub

Private Sub List0_Click()
' Delete the record that matches the selection
rs.Filter = "CUSTOMER_ID = '" & Me![List0] & "'"
rs.Delete

End Sub

 

[RoyVidar]

Hi,

I'm finding that to use additem the listbox has to be in Value List
mode. The recordset that I'm returning has as many as 30 columns.

---I wish this code worked! LOL---
rs.MoveFirst
While Not rs.EOF
List0.AddItem rs
rs.MoveNext
Wend

I think that if you assign through .GetString, you'll be able to use
the .RemoveItem method

With List0
.RowSourceType = "Value List"
.ColumnCount = rs.Fields.Count
.ColumnHeads = True
.RowSource = rs.GetString(adClipString, , ";", ";")
End With

 

[datAdrenaline]

I feel that your list box loading is fine.... however, you should not need to
bind your form to the recordset (at least it seem that way from the info
given).

My intial inclination is to do this for your list click is this: (note AIR
CODE!!)

Private Sub List0_Click()

' Delete the record that matches the selection
If Not (rs.EOF And rs.BOF) And Not IsNull(Me.List0) Then
conn.Execute "YourStoredProc '" & Me.List0 & "'", ,adCmdStoredproc
End If

'Requery the recordset and reset the list box
rs.Requery
Set Me.List0.Recordset = rs

'Make sure no other list items are selected
Me.List0 = Null

End Sub

HTH ...

 

[DracKewl]

This worked perfect! It's just what I was trying to do. The only thing that
I didn't have to add was rs.requery

Thanks Dude!

I feel that your list box loading is fine.... however, you should not need to
bind your form to the recordset (at least it seem that way from the info
given).

My intial inclination is to do this for your list click is this: (note AIR
CODE!!)

Private Sub List0_Click()

' Delete the record that matches the selection
If Not (rs.EOF And rs.BOF) And Not IsNull(Me.List0) Then
conn.Execute "YourStoredProc '" & Me.List0 & "'", ,adCmdStoredproc
End If

'Requery the recordset and reset the list box
rs.Requery
Set Me.List0.Recordset = rs

'Make sure no other list items are selected
Me.List0 = Null

End Sub

HTH ...

--
Regards,
Brent Spaulding
datAdrenaline

Hey All,

This is what I need to do.
1. Load a list box using ADO.
2. I do not want the recordset to be associated to the form. With my
present routine I have to associate it so I can delete items.
3. For simplistic purposes I want the recordset deleted from the list as it
is picked, I do not what that recordset deleted from the datasource.

I'm connecting to postgreSQL database. My intention is that when a selected
item is removed from the list, I will take one of the field values from the
recordset and pass it into a stored procedure and let the backend delete the
record for me.

So far I can delete items as long as they are not the first record;
otherwise the whole form goes blank. The other thing I noticed is that the
rs.RecordCount returns zero once one record is deleted even though there are
many.

The other thing is that I’m not sure that filtering out the record is the
best approach before deleting. But it’s the only way I can get it to remove
the record.

Here is my code. It’s pretty simple.

Option Compare Database
Option Explicit
Private conn As New ADODB.Connection
Private rs As New ADODB.Recordset

Private Sub cmdLoadBox_Click()
' Initializaition
Set conn = New ADODB.Connection
conn.Open "DRIVER={PostgreSQL
Unicode};SERVER=localhost;port=5432;DATABASE=PDM_Dev;UID=postgres;PWD=ajberlyn;"

Set rs = New ADODB.Recordset
With rs
.ActiveConnection = conn
.LockType = adLockBatchOptimistic
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.Open "SELECT * FROM customer", options:=adCmdText
.ActiveConnection = Nothing
End With

'Add recordsets to Listbox
With List0
.RowSourceType = "Table/Query"
.ColumnCount = rs.Fields.Count
.ColumnHeads = True
End With
Set List0.Recordset = rs ' Assign data into the listbox

Set Me.Recordset = rs ' Assign RS as the recordset for the form

conn.Close

End Sub

Private Sub List0_Click()
' Delete the record that matches the selection
rs.Filter = "CUSTOMER_ID = '" & Me![List0] & "'"
rs.Delete

End Sub