By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,963 Members | 1,798 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,963 IT Pros & Developers. It's quick & easy.

Why can't I add a new pNumber??

P: 83
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
Attached Files
File Type: zip TestToSeeIfNumberPresent.zip (26.8 KB, 50 views)
Apr 14 '15 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 1,240
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
Apr 14 '15 #2

jforbes
Expert 100+
P: 1,107
I pulled the problematic code out of your database for you:
Expand|Select|Wrap|Line Numbers
  1. Private Sub pNumber_BeforeUpdate(Cancel As Integer)
  2.     If LookUp("pNumber", "tbldetails", Me.pNumber, eCount) > 0 Then
  3.         MsgBox "this Pmkeys Already Exists, Try Again", vbExclamation, "Cannot Update"
  4.             SendKeys "{ESC}"
  5.             SendKeys "{ESC}"
  6.     End If
  7. End Sub
  8.  
  9. Function LookUp(sField As String, sTable As String, sCriteria As String, _
  10.                 Optional AggFunc As Integer = 0) As Variant '
  11. On Error GoTo ErrorHandler
  12. Dim sSQL As String
  13. Dim sAggFunc As String
  14. Dim db As DAO.Database
  15. Dim rs As DAO.Recordset
  16. If sCriteria <> "" Then sCriteria = " WHERE (" & sCriteria & ")"
  17. If AggFunc > 0 Then
  18.     Select Case AggFunc
  19.         Case 1
  20.             sAggFunc = "Sum("
  21.         Case 2
  22.             sAggFunc = "Avg("
  23.         Case 3
  24.             sAggFunc = "Min("
  25.         Case 4
  26.             sAggFunc = "Max("
  27.         Case 5
  28.             sAggFunc = "Count("
  29.         Case 6
  30.             sAggFunc = "StDev("
  31.         Case 7
  32.             sAggFunc = "Var("
  33.         Case 8
  34.             sAggFunc = "First("
  35.         Case 9
  36.             sAggFunc = "Last("
  37.     End Select
  38.     sSQL = "SELECT " & sAggFunc & sField & ") AS LookUpField " _
  39.         & "FROM " & sTable _
  40.         & sCriteria & ";"
  41.     Else
  42.     sSQL = "SELECT " & sField & " AS LookUpField " _
  43.         & "FROM " & sTable _
  44.         & sCriteria & " " _
  45.         & "GROUP BY " & sField & " " _
  46.         & "ORDER BY " & sField & " DESC;"
  47. End If
  48. Debug.Print sSQL
  49. Set db = CurrentDb
  50. Set rs = db.OpenRecordset(sSQL)
  51.     rs.MoveLast
  52.     rs.MoveFirst
  53. LookUp = rs.Fields("LookUpField")
  54. rs.Close
  55. GoTo ThatsIt
  56. ErrorHandler:
  57.     Select Case Err.Number
  58.         Case 3021, 3061, 3075, 3079
  59.         Case 3024, 3043, 3044 'exclude "Cannot find Table" so error propagates
  60.         Err.Raise -65536, "Lookup", Err.Description
  61.         Case Else
  62.             MsgBox "Error " & Err.Number & ": " & Err.Description & vbCrLf & "in LookUp"
  63.     End Select
  64.     LookUp = Null
  65. ThatsIt:
  66. Set rs = Nothing
  67. Set db = Nothing
  68. End Function
  69.  
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:
Expand|Select|Wrap|Line Numbers
  1. If DCount("pNumber", "tbldetails", "pNumber=" & Me.pNumber) > 0 Then
Apr 14 '15 #3

P: 83
Thanks jforbes. Just came to the same conclusion after posting yesterday.
Apr 15 '15 #4

Post your reply

Sign in to post your reply or Sign up for a free account.