Hi Karah,
> I`m new to this group...
Welcome aboard!
> ...where to look or any help on deleting records from
> [access] databases.
>
> I want to then be able to highlight an item in ListBox1
> and then delete this from the database and from the
> listbox.
>
> I have managed to delete the item in ListBox1, and
> then delete a record from the database. The problem is
> that the record deleted from the database is simply the
> first record there, and not the record highlighted in
> the listbox.
You need to have some form of key or identity tied to the listbox
items. In an HTML list you'd have the hidden value portion be the ID
for that particular record from the table. Assuming a "myTable" like
this:
ID Value
0 Shawn
1 Karah
2 Zachary
You would generate a list that looked like this (in the HTML):
<select name="whatever">
<option value="0">Shawn</option>
<option value="1">Karah</option>
<option value="2">Zachary</option>
</select>
The ASP code would then create a connection object and execute the
code to delete that record:
'// ========================================================
sValue = Request.QueryString("whatever")
Dim cn
Set cn = Server.CreateObject("adodb.connection")
cn.open "your connection string"
cn.execute "DELETE * FROM [myTable] WHERE [ID]=" & sValue
cn.close
set cn = nothing
'// ========================================================
To make it work for VBA you'd change it a bit as so:
'// ========================================================
sValue = ListBox1.List(ListBox1.ListIndex)
Dim cn as adodb.connection ' I'm not fond of "new"
Set cn = new adodb.connection
cn.open "your connection string"
cn.execute "DELETE * FROM [myTable] WHERE [ID]=" & sValue
cn.close
set cn = nothing
'// ========================================================
This assumes that the value tied to that listbox is a number. If
it's a string then you'll need to double-up the apostrophes and wrap
it in them as well.
sSql = "DELETE * FROM [myTable] WHERE [ID]='"
sSql = sSql & Replace(sValue, "'", "''") & "'"
When possible you can also tie the list values directly to the table
within the listbox, so you can display the "value" column (which
might duplicate) but take actions based on the ID column (hidden,
but unique), but that's better for the WordVBA group (right Dian?
;)). To obtain that column data you use the second parameter of the
listbox.List() property, which is the column of the query tied to
the listbox.
Regards,
Shawn K. Hall
http://12PointDesign.com/
http://ReliableAnswers.com/
(+- Syndicate Local Homeschool Groups on your website
(+- http://LocalHS.com/remote/
---------------------------------------------------------------------
Home : http://groups.yahoo.com/group/active-server-pages
---------------------------------------------------------------------
Post : [email protected]
Subscribe : [EMAIL PROTECTED]
Unsubscribe: [EMAIL PROTECTED]
---------------------------------------------------------------------
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/active-server-pages/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
