Hi,
I am trying to check if a particular record already exists in an Access
database through Excel vba code. Through code obtained at another
forum, I got the following:
*************** *************** *************** *************** ***********
Sub TheButton()
Dim db As Database, rs As DAO.Recordset, r As Long
Dim PolicyNum As Variant
Dim bFound As Boolean
Set db = OpenDatabase("E :Test.mdb")
Set rs = db.OpenRecordse t("Level Data", dbOpenTable)
bFound = False
rs.MoveFirst
Do
If ThisWorkbook.Wo rksheets("Loss Model").Cells(1 7, 3).Value =
rs.Fields("Poli cy/Quote Number") Then
bFound = True
MsgBox "Record Already Exist."
End If
rs.MoveNext
Loop Until rs.EOF Or bFound = True
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub
*************** *************** *************** *************** *********
The data is entered at cell (Q,3). But if I type in
If ThisWorkbook.Wo rksheets("Loss Model").Cells(Q , 3).Value =
rs.Fields("Poli cy/Quote Number")
it will give me a 1004 runtime error: application defined or object
defined.
If I use the code above
If ThisWorkbook.Wo rksheets("Loss Model").Cells(1 7, 3).Value =
rs.Fields("Poli cy/Quote Number")
The program cannot catch whether the record already exist and it does
not show me a MsgBox.
Please help.