Why can't I add a new pNumber??

[hrprabhu]

Hello one and all,

My apologies I am still using Access 2003

Have a look at the small attached database. I can not understand why I can not add a new number!

on opening the database it opens form frmdetails. Click on add new record and it takes you to another form. here you click add new record and input any 7 digit number and it rejects.

Thanks in advance

Regards to all

Raghu Prabhu

[jimatqsi]

Most folks here are not going to download an attachment due to concerns about malware. Please list the code involved, and add more detail.

Also, why make your user click Add New Record if they already clicked to open the form to add a new record. Their intent is already clear.

Jim

[jforbes]

I pulled the problematic code out of your database for you:

Private Sub pNumber_BeforeUpdate(Cancel As Integer)
    If LookUp("pNumber", "tbldetails", Me.pNumber, eCount) > 0 Then
        MsgBox "this Pmkeys Already Exists, Try Again", vbExclamation, "Cannot Update"
            SendKeys "{ESC}"
            SendKeys "{ESC}"
    End If
End Sub

Function LookUp(sField As String, sTable As String, sCriteria As String, _
                Optional AggFunc As Integer = 0) As Variant '
On Error GoTo ErrorHandler
Dim sSQL As String
Dim sAggFunc As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
If sCriteria <> "" Then sCriteria = " WHERE (" & sCriteria & ")"
If AggFunc > 0 Then
    Select Case AggFunc
        Case 1
            sAggFunc = "Sum("
        Case 2
            sAggFunc = "Avg("
        Case 3
            sAggFunc = "Min("
        Case 4
            sAggFunc = "Max("
        Case 5
            sAggFunc = "Count("
        Case 6
            sAggFunc = "StDev("
        Case 7
            sAggFunc = "Var("
        Case 8
            sAggFunc = "First("
        Case 9
            sAggFunc = "Last("
    End Select
    sSQL = "SELECT " & sAggFunc & sField & ") AS LookUpField " _
        & "FROM " & sTable _
        & sCriteria & ";"
    Else
    sSQL = "SELECT " & sField & " AS LookUpField " _
        & "FROM " & sTable _
        & sCriteria & " " _
        & "GROUP BY " & sField & " " _
        & "ORDER BY " & sField & " DESC;"
End If
Debug.Print sSQL
Set db = CurrentDb
Set rs = db.OpenRecordset(sSQL)
    rs.MoveLast
    rs.MoveFirst
LookUp = rs.Fields("LookUpField")
rs.Close
GoTo ThatsIt
ErrorHandler:
    Select Case Err.Number
        Case 3021, 3061, 3075, 3079
        Case 3024, 3043, 3044 'exclude "Cannot find Table" so error propagates
        Err.Raise -65536, "Lookup", Err.Description
        Case Else
            MsgBox "Error " & Err.Number & ": " & Err.Description & vbCrLf & "in LookUp"
    End Select
    LookUp = Null
ThatsIt:
Set rs = Nothing
Set db = Nothing
End Function

On line 2 the code calls the custom function Lookup() and there is no need for this function and the extra code (that isn't quite working).

I would replace it with:

If DCount("pNumber", "tbldetails", "pNumber=" & Me.pNumber) > 0 Then

[hrprabhu]

Thanks jforbes. Just came to the same conclusion after posting yesterday.