check for duplicates before update

[thomasks]

Hi, I have some code in a spreadsheet on the before close event that asks the user with a vbyes/no msgbox if they would like to update the master database. If they select yes then I run a routine to take data from the worksheet and append a new record into my database. If they select no then I exit the sub and just close the worksheet.
What I would like to do is check the database to make sure I am not creating a duplicate record before I run the update. Here is the code I use to update the database:

Sub UpdateMasterDb()
    Dim cn As ADODB.Connection, rs As ADODB.Recordset
    Dim intResale As Integer
    Dim intHours As Integer
    intHours = Worksheets("Project pricing summary").Range("G60").Value + Worksheets("Project pricing summary").Range("G61").Value _
    + Worksheets("Project pricing summary").Range("G62").Value
    intResale = Worksheets("Project information").Range("D26").Value + Worksheets("Project information").Range("D28").Value
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=T:\Quot\CONTROLS\Formulas\Estworksheet\Master Quote List\Controls Estimating Database.mdb;"
    Set rs = New ADODB.Recordset
    rs.Open "tblMasterQuoteList", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    rs.AddNew
    rs.Fields("Project") = Worksheets("Project information").Range("L6").Value
    rs.Fields("Customer") = Worksheets("Project information").Range("D6").Value
    rs.Fields("Location") = Worksheets("Project information").Range("D7").Value
    rs.Fields("Quote_Type") = Worksheets("Project information").Range("L7").Value
    rs.Fields("Estimator") = Worksheets("Project information").Range("D8").Value
    rs.Fields("Date_Quoted") = Worksheets("Project information").Range("L5").Value
    rs.Fields("Comments_Scope") = Worksheets("Project pricing summary").Range("C15").Value
    rs.Fields("File_Path_Hyperlink") = Application.ActiveWorkbook.FullName
    rs.Fields("Resale") = intResale
    rs.Fields("Hardware") = Worksheets("Project information").Range("D27").Value
    rs.Fields("Eng_Hours") = intHours
    rs.Fields("Travel") = Worksheets("Project pricing summary").Range("L59").Value
    rs.Update
    MsgBox ("Database has been updated")
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

Thanks for any help you all can provide. This site has been a life saver!

KT

 

[PHV]

Why not simply create a compound unique index on tblMasterQuoteList ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[thomasks]

Please forgive my ignorance when it comes to Access and relational databases (newbie). But I don't understand your answer. How would this unique index keep from duplicating entries? And how do you create it? Does it have to have relations?

 

[PHV]

What is your definition of duplicate entries in tblMasterQuoteList ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[thomasks]

Well, these are quotes, that are done by customer, project number, scope of work, estimator, date quoted, and quote type (firm, budget etc.) It is possible for an estimator to go in and update a quote after sales has added some information, or defined the scope. I just don't want the same exact quote being updated more than once (which would put multiple records for the same quote). So I guess the definition of duplicate would be "exact duplicate" except for date, or estimator name.
Hope this is understandable......

 

[Golom]

You may be looking at a design issue here.

Your definition of "unique" (i.e. everything except date and estimator) has the effect of making ANY change to the other fields an effectively new quote ... not a revision to an existing quote.

If, for example, someone changed the "scope of work" or "quote type", how would you know that the record so created should be regarded as a revision to an existing quote rather than as a new one unrelated to any other quote?

Systems like this usually assign a unique "QuoteID" to each quotation and then build a multi-field primary key involving QuoteID + QuoteDate. With that structure it's fairly easy to pull all the records for a particular quote.

You can avoid duplication of the other fields by building a unique index (but not the primary key) for all the other fields with something like

ALTER TABLE myTable ADD CONSTRAINT
UNIQUE (Project, Customer, Location, Quote_Type, etc...)

You can also do this with the Table Design view in Access. The attempt to create such an index will fail if there are already duplicates in the table.

 

[thomasks]

Thankyou Golom!
I took your advice, and in the table design view of Access, set up a unique index based on Project, Quote_Type, Customer, Location, and Comment_Scope. The estimator will usually enter in the comment_scope area "Rev 2",3 etc. this way it is no longer an exact duplicate of the quote.