delete a row from a listbox

  • Thread starter Thread starter Miranda
  • Start date Start date
M

Miranda

hi,

i've got a listbox with 3 columns: sheetNumber, equipmentType and Number.
The user adds rows to the list box via some text fields ect. I want the user
to be able to click on a row that they have added and be able to delete it
using a onClick procedure ( in case of a mistake). Is this possible? if so,
how do i refer to the selected row in the listbox?

FORMS!dataEntry!Listbox.....???

thanks!

miranda
 
Assuming that sheetNumber is the primary key of the query's table, and that
it's the bound column for the listbox:

Private Sub ListBoxName_Click()
Dim strSQL As String
If vbYes = MsgBox("Do you wish to delete this item?", _
vbQuestion + vbYesNo, "Delete This Item?") Then

strSQL = "DELETE * FROM TableName WHERE " & _
"[sheetNumber]=" & Me.ListBoxName.Value & ";"
CurrentDb.Execute strSQL, dbFailOnError
Me.ListBoxName.Requery
End If
End Sub

If my assumptions are wrong, then post back with more info about the list
box's row source query and the query's structure.
 
hi ken,

the primary key of the table is sheetnumber and equipmentType. But the
sheetNumber column in the listbox is bound. can i perform this using a
button click event?

thanks for your help,
miranda

Ken Snell said:
Assuming that sheetNumber is the primary key of the query's table, and that
it's the bound column for the listbox:

Private Sub ListBoxName_Click()
Dim strSQL As String
If vbYes = MsgBox("Do you wish to delete this item?", _
vbQuestion + vbYesNo, "Delete This Item?") Then

strSQL = "DELETE * FROM TableName WHERE " & _
"[sheetNumber]=" & Me.ListBoxName.Value & ";"
CurrentDb.Execute strSQL, dbFailOnError
Me.ListBoxName.Requery
End If
End Sub

If my assumptions are wrong, then post back with more info about the list
box's row source query and the query's structure.

--
Ken Snell
<MS ACCESS MVP>



Miranda said:
hi,

i've got a listbox with 3 columns: sheetNumber, equipmentType and Number.
The user adds rows to the list box via some text fields ect. I want the user
to be able to click on a row that they have added and be able to delete it
using a onClick procedure ( in case of a mistake). Is this possible? if so,
how do i refer to the selected row in the listbox?

FORMS!dataEntry!Listbox.....???

thanks!

miranda
Click to expand...
Click to expand...
 
figured it out, thanks alot for your help!


Ken Snell said:
Assuming that sheetNumber is the primary key of the query's table, and that
it's the bound column for the listbox:

Private Sub ListBoxName_Click()
Dim strSQL As String
If vbYes = MsgBox("Do you wish to delete this item?", _
vbQuestion + vbYesNo, "Delete This Item?") Then

strSQL = "DELETE * FROM TableName WHERE " & _
"[sheetNumber]=" & Me.ListBoxName.Value & ";"
CurrentDb.Execute strSQL, dbFailOnError
Me.ListBoxName.Requery
End If
End Sub

If my assumptions are wrong, then post back with more info about the list
box's row source query and the query's structure.

--
Ken Snell
<MS ACCESS MVP>



Miranda said:
hi,

i've got a listbox with 3 columns: sheetNumber, equipmentType and Number.
The user adds rows to the list box via some text fields ect. I want the user
to be able to click on a row that they have added and be able to delete it
using a onClick procedure ( in case of a mistake). Is this possible? if so,
how do i refer to the selected row in the listbox?

FORMS!dataEntry!Listbox.....???

thanks!

miranda
Click to expand...
Click to expand...
 
actually, i haven't figured it out. I seem to be having problems because of
the primary key being sheetNumber and equipmentType....any suggestions?!?


Ken Snell said:
Assuming that sheetNumber is the primary key of the query's table, and that
it's the bound column for the listbox:

Private Sub ListBoxName_Click()
Dim strSQL As String
If vbYes = MsgBox("Do you wish to delete this item?", _
vbQuestion + vbYesNo, "Delete This Item?") Then

strSQL = "DELETE * FROM TableName WHERE " & _
"[sheetNumber]=" & Me.ListBoxName.Value & ";"
CurrentDb.Execute strSQL, dbFailOnError
Me.ListBoxName.Requery
End If
End Sub

If my assumptions are wrong, then post back with more info about the list
box's row source query and the query's structure.

--
Ken Snell
<MS ACCESS MVP>



Miranda said:
hi,

i've got a listbox with 3 columns: sheetNumber, equipmentType and Number.
The user adds rows to the list box via some text fields ect. I want the user
to be able to click on a row that they have added and be able to delete it
using a onClick procedure ( in case of a mistake). Is this possible? if so,
how do i refer to the selected row in the listbox?

FORMS!dataEntry!Listbox.....???

thanks!

miranda
Click to expand...
Click to expand...
 
Change the SQL statement to this (so that both primary keys are being used):

strSQL = "DELETE * FROM TableName WHERE " & _
"[sheetNumber]=" & Me.ListBoxName.Value & _
" And [equipmentType]=" & Me.ListBoxName.Column(1) _
& ";"

Above assumes that both sheetNumber and equipmentType are numeric values,
not text values.
--
Ken Snell
<MS ACCESS MVP>

Miranda said:
actually, i haven't figured it out. I seem to be having problems because of
the primary key being sheetNumber and equipmentType....any suggestions?!?


Ken Snell said:
Assuming that sheetNumber is the primary key of the query's table, and that
it's the bound column for the listbox:

Private Sub ListBoxName_Click()
Dim strSQL As String
If vbYes = MsgBox("Do you wish to delete this item?", _
vbQuestion + vbYesNo, "Delete This Item?") Then

strSQL = "DELETE * FROM TableName WHERE " & _
"[sheetNumber]=" & Me.ListBoxName.Value & ";"
CurrentDb.Execute strSQL, dbFailOnError
Me.ListBoxName.Requery
End If
End Sub

If my assumptions are wrong, then post back with more info about the list
box's row source query and the query's structure.

--
Ken Snell
<MS ACCESS MVP>



Miranda said:
hi,

i've got a listbox with 3 columns: sheetNumber, equipmentType and Number.
The user adds rows to the list box via some text fields ect. I want
Click to expand...
the
user
to be able to click on a row that they have added and be able to
Click to expand...
Click to expand...
delete
it if
so,
Click to expand...
Click to expand...
 
hi,

i would like to use a macro instead of using code, should i be able to use
ListBoxName.Column(1) in a runSQL statement?
ta

Ken Snell said:
Change the SQL statement to this (so that both primary keys are being used):

strSQL = "DELETE * FROM TableName WHERE " & _
"[sheetNumber]=" & Me.ListBoxName.Value & _
" And [equipmentType]=" & Me.ListBoxName.Column(1) _
& ";"

Above assumes that both sheetNumber and equipmentType are numeric values,
not text values.
--
Ken Snell
<MS ACCESS MVP>

Miranda said:
actually, i haven't figured it out. I seem to be having problems because of
the primary key being sheetNumber and equipmentType....any suggestions?!?


Ken Snell said:
Assuming that sheetNumber is the primary key of the query's table, and that
it's the bound column for the listbox:

Private Sub ListBoxName_Click()
Dim strSQL As String
If vbYes = MsgBox("Do you wish to delete this item?", _
vbQuestion + vbYesNo, "Delete This Item?") Then

strSQL = "DELETE * FROM TableName WHERE " & _
"[sheetNumber]=" & Me.ListBoxName.Value & ";"
CurrentDb.Execute strSQL, dbFailOnError
Me.ListBoxName.Requery
End If
End Sub

If my assumptions are wrong, then post back with more info about the list
box's row source query and the query's structure.

--
Ken Snell
<MS ACCESS MVP>



hi,

i've got a listbox with 3 columns: sheetNumber, equipmentType and Number.
The user adds rows to the list box via some text fields ect. I want the
user
to be able to click on a row that they have added and be able to
Click to expand...
delete
it
using a onClick procedure ( in case of a mistake). Is this possible? if
so,
how do i refer to the selected row in the listbox?

FORMS!dataEntry!Listbox.....???

thanks!

miranda
Click to expand...
Click to expand...
Click to expand...
 
Almost...

Instead of Me.ListBoxName.Column(1) syntax, use one of the following:

(1) if the macro is being called from the form that contains the list box:
[ListBoxName].[Column](1)

or

(2) if the macro is being called from another location (or even if it's
being called from the form that contains the list box):
[Forms]![FormName]![ListBoxName].[Column](1)

replacing FormName and ListBoxName with the real names, of course.

--
Ken Snell
<MS ACCESS MVP>

Miranda said:
hi,

i would like to use a macro instead of using code, should i be able to use
ListBoxName.Column(1) in a runSQL statement?
ta

Ken Snell said:
Change the SQL statement to this (so that both primary keys are being used):

strSQL = "DELETE * FROM TableName WHERE " & _
"[sheetNumber]=" & Me.ListBoxName.Value & _
" And [equipmentType]=" & Me.ListBoxName.Column(1) _
& ";"

Above assumes that both sheetNumber and equipmentType are numeric values,
not text values.
--
Ken Snell
<MS ACCESS MVP>

Miranda said:
actually, i haven't figured it out. I seem to be having problems
Click to expand...
because
of
the primary key being sheetNumber and equipmentType....any suggestions?!?


Assuming that sheetNumber is the primary key of the query's table, and
that
it's the bound column for the listbox:

Private Sub ListBoxName_Click()
Dim strSQL As String
If vbYes = MsgBox("Do you wish to delete this item?", _
vbQuestion + vbYesNo, "Delete This Item?") Then

strSQL = "DELETE * FROM TableName WHERE " & _
"[sheetNumber]=" & Me.ListBoxName.Value & ";"
CurrentDb.Execute strSQL, dbFailOnError
Me.ListBoxName.Requery
End If
End Sub

If my assumptions are wrong, then post back with more info about the list
box's row source query and the query's structure.

--
Ken Snell
<MS ACCESS MVP>



hi,

i've got a listbox with 3 columns: sheetNumber, equipmentType and
Number.
The user adds rows to the list box via some text fields ect. I
Click to expand...
want
the
user
to be able to click on a row that they have added and be able to delete
it
using a onClick procedure ( in case of a mistake). Is this
Click to expand...
possible?
if
so,
how do i refer to the selected row in the listbox?

FORMS!dataEntry!Listbox.....???

thanks!

miranda
Click to expand...
Click to expand...
Click to expand...
 
hi,
i tried using the format [ListBoxName].[Column](1) in my macro, however i
get an error at run time that says the function is undefined?


Ken Snell said:
Almost...

Instead of Me.ListBoxName.Column(1) syntax, use one of the following:

(1) if the macro is being called from the form that contains the list box:
[ListBoxName].[Column](1)

or

(2) if the macro is being called from another location (or even if it's
being called from the form that contains the list box):
[Forms]![FormName]![ListBoxName].[Column](1)

replacing FormName and ListBoxName with the real names, of course.

--
Ken Snell
<MS ACCESS MVP>

Miranda said:
hi,

i would like to use a macro instead of using code, should i be able to use
ListBoxName.Column(1) in a runSQL statement?
ta

Ken Snell said:
Change the SQL statement to this (so that both primary keys are being used):

strSQL = "DELETE * FROM TableName WHERE " & _
"[sheetNumber]=" & Me.ListBoxName.Value & _
" And [equipmentType]=" & Me.ListBoxName.Column(1) _
& ";"

Above assumes that both sheetNumber and equipmentType are numeric values,
not text values.
--
Ken Snell
<MS ACCESS MVP>

actually, i haven't figured it out. I seem to be having problems because
of
the primary key being sheetNumber and equipmentType....any suggestions?!?


Assuming that sheetNumber is the primary key of the query's table, and
that
it's the bound column for the listbox:

Private Sub ListBoxName_Click()
Dim strSQL As String
If vbYes = MsgBox("Do you wish to delete this item?", _
vbQuestion + vbYesNo, "Delete This Item?") Then

strSQL = "DELETE * FROM TableName WHERE " & _
"[sheetNumber]=" & Me.ListBoxName.Value & ";"
CurrentDb.Execute strSQL, dbFailOnError
Me.ListBoxName.Requery
End If
End Sub

If my assumptions are wrong, then post back with more info about the
list
box's row source query and the query's structure.

--
Ken Snell
<MS ACCESS MVP>



hi,

i've got a listbox with 3 columns: sheetNumber, equipmentType and
Number.
The user adds rows to the list box via some text fields ect. I want
the
user
to be able to click on a row that they have added and be able to
delete
it
using a onClick procedure ( in case of a mistake). Is this possible?
if
so,
how do i refer to the selected row in the listbox?

FORMS!dataEntry!Listbox.....???

thanks!

miranda
Click to expand...
Click to expand...
Click to expand...
 
Replace ListBoxName with the real name of your listbox. I used a generic
name as a representative example.

--
Ken Snell
<MS ACCESS MVP>

Miranda said:
hi,
i tried using the format [ListBoxName].[Column](1) in my macro, however i
get an error at run time that says the function is undefined?


Ken Snell said:
Almost...

Instead of Me.ListBoxName.Column(1) syntax, use one of the following:

(1) if the macro is being called from the form that contains the list box:
[ListBoxName].[Column](1)

or

(2) if the macro is being called from another location (or even if it's
being called from the form that contains the list box):
[Forms]![FormName]![ListBoxName].[Column](1)

replacing FormName and ListBoxName with the real names, of course.

--
Ken Snell
<MS ACCESS MVP>

Miranda said:
hi,

i would like to use a macro instead of using code, should i be able to use
ListBoxName.Column(1) in a runSQL statement?
ta

Change the SQL statement to this (so that both primary keys are being
used):

strSQL = "DELETE * FROM TableName WHERE " & _
"[sheetNumber]=" & Me.ListBoxName.Value & _
" And [equipmentType]=" & Me.ListBoxName.Column(1) _
& ";"

Above assumes that both sheetNumber and equipmentType are numeric values,
not text values.
--
Ken Snell
<MS ACCESS MVP>

actually, i haven't figured it out. I seem to be having problems because
of
the primary key being sheetNumber and equipmentType....any
suggestions?!?


Assuming that sheetNumber is the primary key of the query's
Click to expand...
table,
and
that
it's the bound column for the listbox:

Private Sub ListBoxName_Click()
Dim strSQL As String
If vbYes = MsgBox("Do you wish to delete this item?", _
vbQuestion + vbYesNo, "Delete This Item?") Then

strSQL = "DELETE * FROM TableName WHERE " & _
"[sheetNumber]=" & Me.ListBoxName.Value & ";"
CurrentDb.Execute strSQL, dbFailOnError
Me.ListBoxName.Requery
End If
End Sub

If my assumptions are wrong, then post back with more info about the
list
box's row source query and the query's structure.

--
Ken Snell
<MS ACCESS MVP>



hi,

i've got a listbox with 3 columns: sheetNumber, equipmentType and
Number.
The user adds rows to the list box via some text fields ect. I want
the
user
to be able to click on a row that they have added and be able to
delete
it
using a onClick procedure ( in case of a mistake). Is this possible?
if
so,
how do i refer to the selected row in the listbox?

FORMS!dataEntry!Listbox.....???

thanks!

miranda
Click to expand...
Click to expand...
Click to expand...
 
Hi ken,

i did use the name of my listbox. However, i still get an error.

Ken Snell said:
Replace ListBoxName with the real name of your listbox. I used a generic
name as a representative example.

--
Ken Snell
<MS ACCESS MVP>

Miranda said:
hi,
i tried using the format [ListBoxName].[Column](1) in my macro, however i
get an error at run time that says the function is undefined?


Ken Snell said:
Almost...

Instead of Me.ListBoxName.Column(1) syntax, use one of the following:

(1) if the macro is being called from the form that contains the list box:
[ListBoxName].[Column](1)

or

(2) if the macro is being called from another location (or even if it's
being called from the form that contains the list box):
[Forms]![FormName]![ListBoxName].[Column](1)

replacing FormName and ListBoxName with the real names, of course.

--
Ken Snell
<MS ACCESS MVP>

hi,

i would like to use a macro instead of using code, should i be able
Click to expand...
to
use
ListBoxName.Column(1) in a runSQL statement?
ta

Change the SQL statement to this (so that both primary keys are being
used):

strSQL = "DELETE * FROM TableName WHERE " & _
"[sheetNumber]=" & Me.ListBoxName.Value & _
" And [equipmentType]=" & Me.ListBoxName.Column(1) _
& ";"

Above assumes that both sheetNumber and equipmentType are numeric
values,
not text values.
--
Ken Snell
<MS ACCESS MVP>

actually, i haven't figured it out. I seem to be having problems
because
of
the primary key being sheetNumber and equipmentType....any
suggestions?!?


Assuming that sheetNumber is the primary key of the query's table,
and
that
it's the bound column for the listbox:

Private Sub ListBoxName_Click()
Dim strSQL As String
If vbYes = MsgBox("Do you wish to delete this item?", _
vbQuestion + vbYesNo, "Delete This Item?") Then

strSQL = "DELETE * FROM TableName WHERE " & _
"[sheetNumber]=" & Me.ListBoxName.Value & ";"
CurrentDb.Execute strSQL, dbFailOnError
Me.ListBoxName.Requery
End If
End Sub

If my assumptions are wrong, then post back with more info
Click to expand...
about
the
list
box's row source query and the query's structure.

--
Ken Snell
<MS ACCESS MVP>



hi,

i've got a listbox with 3 columns: sheetNumber,
Click to expand...
equipmentType
and
Number.
The user adds rows to the list box via some text fields ect. I
want
the
user
to be able to click on a row that they have added and be
Click to expand...
Click to expand...
able
to
Click to expand...
Click to expand...
 
Dummy me..... my mind just remembered one of those things that you learn on
the way to doing something else, and I'd forgotten it.

You cannot use the Column property as a reference in the query. Sorry....

Here're two other ways to do what you want.

(1) Put an invisible textbox on the form; call it txtName. Set its control
source to this:
=[ListBoxName].[Column](1)
In the SQL statement, replace the [ListBoxName].[Column](1) stuff
with this:
[txtName]

(2) Use a public function to read the value of the listbox's second column.
Put this function in a regular module (be sure you name the module something
other than the function's name):
Public Function GetListBoxValue() As Variant
GetListBoxValue = Forms!FormName!ListBoxName.Column(1)
End Function

In the SQL statement, replace the [ListBoxName].[Column](1) stuff
with this:
GetListBoxValue()


--
Ken Snell
<MS ACCESS MVP>



Miranda said:
Hi ken,

i did use the name of my listbox. However, i still get an error.

Ken Snell said:
Replace ListBoxName with the real name of your listbox. I used a generic
name as a representative example.

--
Ken Snell
<MS ACCESS MVP>

Miranda said:
hi,
i tried using the format [ListBoxName].[Column](1) in my macro,
Click to expand...
Click to expand...
however
i
get an error at run time that says the function is undefined?


Almost...

Instead of Me.ListBoxName.Column(1) syntax, use one of the following:

(1) if the macro is being called from the form that contains the
Click to expand...
list
box:
[ListBoxName].[Column](1)

or

(2) if the macro is being called from another location (or even if it's
being called from the form that contains the list box):
[Forms]![FormName]![ListBoxName].[Column](1)

replacing FormName and ListBoxName with the real names, of course.

--
Ken Snell
<MS ACCESS MVP>

hi,

i would like to use a macro instead of using code, should i be
Click to expand...
Click to expand...
able
to
use
ListBoxName.Column(1) in a runSQL statement?
ta

Change the SQL statement to this (so that both primary keys are being
used):

strSQL = "DELETE * FROM TableName WHERE " & _
"[sheetNumber]=" & Me.ListBoxName.Value & _
" And [equipmentType]=" & Me.ListBoxName.Column(1) _
& ";"

Above assumes that both sheetNumber and equipmentType are numeric
values,
not text values.
--
Ken Snell
<MS ACCESS MVP>

actually, i haven't figured it out. I seem to be having problems
because
of
the primary key being sheetNumber and equipmentType....any
suggestions?!?


Assuming that sheetNumber is the primary key of the query's table,
and
that
it's the bound column for the listbox:

Private Sub ListBoxName_Click()
Dim strSQL As String
If vbYes = MsgBox("Do you wish to delete this item?", _
vbQuestion + vbYesNo, "Delete This Item?") Then

strSQL = "DELETE * FROM TableName WHERE " & _
"[sheetNumber]=" & Me.ListBoxName.Value & ";"
CurrentDb.Execute strSQL, dbFailOnError
Me.ListBoxName.Requery
End If
End Sub

If my assumptions are wrong, then post back with more info about
the
list
box's row source query and the query's structure.

--
Ken Snell
<MS ACCESS MVP>



hi,

i've got a listbox with 3 columns: sheetNumber, equipmentType
and
Number.
The user adds rows to the list box via some text fields
Click to expand...
Click to expand...
ect.
I able
Click to expand...
Click to expand...
 
Back
Top