View Full Version : Solved: Delete Record from Database
petedw
07-04-2005, 04:04 AM
Help!
I have a database setup in access. I need word to open the database, search for a record and delete that record. The record to search for will be the text in a formfield. This only needs to happen when the result of another formfield is true.
Table name is "Customer"
Field to search in is called "CustomerRef"
Text to search for is in a formfield called "CustRef"
The code should only activate when formfields("BillStatus") = YOUR FINAL GAS ACCOUNT
I hope you can help me with this
Many Thanks
Pete
MOS MASTER
07-04-2005, 08:26 AM
Hi Pete, :yes
What do you have so far? :whistle:
petedw
07-04-2005, 08:44 AM
I've got this so far...... It's probably a load of rubbish, so you can have a laugh at my expense. Hope you can help me out though
Sub Delentry()
Dim cn As New ADODB.Connection:
Dim rst As New ADODB.Recordset
Dim oCtl As MSForms.Control:
Dim sPath As String
If ActiveDocument.FormFields("BillStatus").Result = _
"YOUR FINAL GAS ACCOUNT" Then
ActiveDocument.FormFields("CustRef").Select
sPath = ThisDocument.Path & "\DATABASETEST.mdb"
cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.Open sPath
rst.Open "Customer", cn, adOpenKeyset, adLockOptimistic
'rst.Find Selection.Text, , adSearchForward, Start
'THIS LINE COMES BACK WITH AN ERROR ABOUT PROTECTION
End Sub
MOS MASTER
07-04-2005, 08:54 AM
Ok have to finish someting and then I'll post some code. :yes
MOS MASTER
07-04-2005, 09:32 AM
Hi Pete, :yes
Ok I'm not using the Find method cause I'm counting on the fact that your CustomerRef field contains unique values only. (That seams normal to me...in my example it's a primary key)
I use SQL to select the right record and delete it like:
Option Explicit
Sub FillDatabase()
Dim cn As New ADODB.Connection: Dim rst As New ADODB.Recordset
Dim sPath As String: Dim sSQL As String: Dim sField As String
On Error GoTo FillDatabase_Error
If ActiveDocument.FormFields("BillStatus").Result <> _
"YOUR FINAL GAS ACCOUNT" Then Exit Sub
sField = ActiveDocument.FormFields("CustRef").Result
If sField = vbNullString Then Exit Sub
sSQL = "SELECT CustomerRef From Customer WHERE (((CustomerRef)='" & sField & "'));"
sPath = ThisDocument.Path & "\Customers.mdb"
cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.Open sPath
rst.Open sSQL, cn, adOpenKeyset, adLockOptimistic
If Not rst.EOF = True And rst.RecordCount = 1 Then
rst.Delete
MsgBox "Deleted succesfully"
Else
MsgBox "Not deleted search criteria not found"
End If
rst.Close
cn.Close
Set rst = Nothing
Set cn = Nothing
Exit Sub
FillDatabase_Error:
If Not rst Is Nothing Then
If rst.State = adStateOpen Then rst.Close
End If
Set rst = Nothing
If Not cn Is Nothing Then
If cn.State = adStateOpen Then cn.Close
End If
Set cn = Nothing
If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Onbekende fout!"
End If
End Sub
See attachment for working sample.
When you leave the last field the macro will execute and delete the field.
Enjoy! :whistle:
petedw
07-04-2005, 09:38 AM
Thanks alot for this Joost, i will check it out properly tomorrow morning. Im off home from work now. I will probably post in the morning singing your praises! Thanks again
Pete
MOS MASTER
07-04-2005, 09:39 AM
You're welcome! :beerchug:
petedw
07-05-2005, 01:29 AM
Joost, :bow: I told you that i would be back to sing your praises!
Thanks for your help once again!!:thumb
Pete
MOS MASTER
07-05-2005, 10:25 AM
It's a shame my pc speakers are broken...
Like always it was my pleasure..:yes
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.