Solved: Delete Record from Database

[petedw]

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]

Hi Pete,

What do you have so far?

[petedw]

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
[VBA] 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 [/VBA]

[MOS MASTER]

Ok have to finish someting and then I'll post some code.

[MOS MASTER]

Hi Pete,

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:[VBA]
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
[/VBA]

See attachment for working sample.
When you leave the last field the macro will execute and delete the field.

Enjoy!

[petedw]

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]

You're welcome!

[petedw]

Joost, I told you that i would be back to sing your praises!
Thanks for your help once again!!

Pete

[MOS MASTER]

It's a shame my pc speakers are broken...

Like always it was my pleasure..