Ok I have another scenario now it's a bit hard to explain but I will try my best. I am trying to add an estimate number to an existing contract number but it tells me the estimate number already exists. Obviously because let's say contract number S-888 has 10 estimates and contract number S-444 has 5 estimates. But my problem is how can I add an estimate number without giving me the duplicate error? Any help please would truly help thanks :). Here are the functions:

<%

' --- FUNCTIONS STARTS BELOW

Function searchContract()

    ' Local Recordset
    dim rsLocal
    set rsLocal = server.CreateObject("adodb.recordset")
    ' Search for a contract alone or contract AND estimate
    if strEstimateNum <> "" then
        ' Set rsLocal = FindEstimateContracts(strContractNum, strEstimateNum)
        SQL = "Select * from tblEstimates(nolock) where contno = '" & strContractNum & "' and estno='" & strEstimateNum & "' order by estno"
    else
        SQL = "Select top 1 * from tblEstimates(nolock) where contno = '" & strContractNum & "' order by estno"
    end if
    ' Open Recordset
    rsLocal.Open SQL, cn

    ' Check for end of file (eof)
    if rsLocal.EOF then
      'response.write "<script language=javascript>alert('Project Number Not Found');</script>"
      %>
        <script language=javascript>
            alert("Contract Number Not Found");
        </script>
      <%
    else
      do while not rsLocal.EOF
        contno = rsLocal("contno")
        estno = rsLocal("estno")
        contractor = rsLocal("contractor")
        estamt = iif(isnull(rsLocal("estamt")), 0, rsLocal("estamt"))
        retainage=iif(isnull(rsLocal("retainage")), 0, rsLocal("retainage"))
        otherdeduct=iif(isnull(rsLocal("otherdeduct")),0, rsLocal("otherdeduct"))
        netestamt=iif(isnull(rsLocal("netestamt")), 0, rsLocal("netestamt"))
        amttodate=iif(isnull(rsLocal("amttodate")),0, rsLocal ("amttodate"))
        rsvdcontamt=iif(isnull(rsLocal("rsvdcontamt")),0, rsLocal("rsvdcontamt"))
        estdate=rsLocal("estdate")
        perenddate=rsLocal("perenddate")
        eststatus=rsLocal("eststatus")
        prelimrsvd=rsLocal("prelimrsvd")
        projmgrapproval=rsLocal("projmgrapproval")
        contractorsigned=rsLocal("contractorsigned")
        assistdirector=rsLocal("assistdirector")
        cmdchief=rsLocal("cmdchief")
        staffdone=rsLocal("staffdone")
        contreviewapproval=rsLocal("contreviewapproval")
        receiveddate=rsLocal("receiveddate")
        submittoap=rsLocal("submittoap")
        checkdate=rsLocal("checkdate")
        checkno=rsLocal("checkno")
        recfromap=rsLocal("recfromap")
        comments=rsLocal("comments")
        paiddate=rsLocal("paiddate")
        trackingnum=rsLocal("trackingnum")
      rsLocal.movenext
      loop
    end if

    set rsLocal = nothing
End Function

Function addContract()
    ' Local Recordset
    dim rsLocal
    ' Add Project
    set rsLocal = server.CreateObject("adodb.recordset")
    SQL = "select * from tblEstimates where estno = '" & strEstimateNum & "'"
    'response.write "We are adding"
    'response.end
    ' Run Query
    rsLocal.open SQL, cn, 0, 3
    ' Check to see if project exists
    If not rsLocal.eof Then
        ' Estimate does not exist, add new record
        With rsLocal
            .addnew
            .Fields("estno") = strEstimateNum
            .update
            .close
        End With
    Else
        ' Project already exists, display error message
        Response.Write "Error: Estimate Number " & strEstimateNum & " already exists!"
    End If
    set rsLocal = nothing
End Function

Function updateContract()
    ' Local Recordset
    dim rsLocal
    ' Update Contract
    set rsLocal = server.CreateObject("adodb.recordset")
    SQL = "select * from tblEstimates where estno = '" & strEstimateNum & "'"
    'response.write "We are updating " & SQL
    With rsLocal
        .open SQL, cn, 0, 3
        If .recordcount = 1 then
            '.Fields("contno") = request("contno") <- never update key fields
            .Fields("estno") = request("estno")
            .Fields("contractor") = request("contractor")
            .Fields("estamt") = request("estamt")
            .Fields("retainage") = request("retainage")
            .Fields("otherdeduct") = request("otherdeduct")
            .Fields("netestamt") = request("netestamt")
            .Fields("amttodate") = request("amttodate")
            .Fields("rsvdcontamt")=request("rsvdcontamt")
            .Fields("estdate")=convertBlankToNull(request("est date"))
            .Fields("perenddate")=convertBlankToNull(request(" perenddate"))
            .Fields("eststatus")=request("eststatus")
            .Fields("prelimrsvd")=convertBlankToNull(request(" prelimrsvd"))
            .Fields("projmgrapproval") = convertBlankToNull(request("projmgrapproval"))
            .Fields("contractorsigned") = convertBlankToNull(request("contractorsigned"))
            .Fields("assistdirector") = convertBlankToNull(request("assistdirector"))
            .Fields("cmdchief") = convertBlankToNull(request("cmdchief"))
            .Fields("staffdone") = convertBlankToNull(request("staffdone"))
            .Fields("contreviewapproval")=convertBlankToNull(r equest("contreviewapproval"))
            .Fields("receiveddate")=convertBlankToNull(request ("receiveddate"))
            .Fields("submittoap")=convertBlankToNull(request(" submittoap"))
            .Fields("checkdate")=convertBlankToNull(request("c heckdate"))
            .Fields("checkno")=convertBlankToNull(request("che ckno"))
            .Fields("recfromap")=convertBlankToNull(request("r ecfromap"))
            .Fields("comments")=request("comments")
            .Fields("paiddate")=convertBlanktoNull(request("pa iddate"))
            .Fields("trackingnum")=request("trackingnum")
            .update
            .close
            '.movelast
        Else
            response.write "Error: Attempting to update " & .recordcount &" records is not allowed. Please contact system administrator."
        End If
    End With
    set rsLocal = nothing

End Function

Function deleteContract()
    ' Local Recordset
    dim rsLocal
    ' Delete Contract
    Set rsLocal = server.CreateObject("adodb.recordset")
    SQL = "select * from tblEstimates where estno = '" & strEstimateNum & "'"
    'response.write "We are deleting"
    'response.end
    ' Run Query
    rsLocal.open SQL, cn, 0, 3
    ' Check to see if project exists
    If not rsLocal.eof Then
        ' deletes record
        With rsLocal
            .delete
            .movenext
            .close
        End With
    Else
        ' Contract does not exists, display error message
        ' Response.Write "Error: Contract " & strContractNum & " no longer exists!"
    End If
    set rsLocal = nothing
End Function

' --- PROCESSING LOGIC STARTS BELOW

dim strContractNum, strEstimateNum, rsContr, contno, estno, contractor, estamt, retainage, otherdeduct, netestamt, amttodate, rsvdcontamt, estdate, perenddate, eststatus, prelimrsvd, projmgrapproval, contractorsigned, assistdirector, cmdchief, staffdone, contreviewapproval, receiveddate, submittoap, checkdate, checkno, recfromap, comments, paiddate, trackingnum, btnAction

' check if we are searching for a contract
strContractNum = request("contractnumhidden") ' from hidden variable
' check if we are searching for an estimate number
strEstimateNum = request("estimatenumhidden") ' from hidden variable

' Process the Add/Update/Delete/Search functions
btnAction = request("btnAction")
select case btnAction
    case "AddEstimate"
        ' Add the contract
        if strEstimateNum <> "" then ' Make sure user entered a value
            addContract()
            response.write "in add function add estimate " & strEstimateNum
            'response.end
            ' After the add, perform a search to reload contract :)
            searchContract
        end if
    case "UpdateEstimate"
        ' Update the estimate
        if strEstimateNum <> "" then
            updateContract()
            response.write "in update function update estimate " & strEstimateNum
            response.end
            ' After the update, perform a search to reload contract :)
            searchContract
        end if
    case "DeleteEstimate"
        'Delete the estimate
        if strEstimateNum <> "" then
            deleteContract()
            response.write "in delete function delete estimate " & strEstimateNum
            response.end
            ' After the delete, perform a search to reload contract :)
            'searchContract
        end if
    case ""
        ' Process the search
        if strEstimateNum <> "" then
            searchContract
        end if
   end select
%>



slypunk

__________________
slypunk