 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / July 2008Automatically adding a primary key
Hello and thank you in advance for your help. I am looking to add records to a table with patient information via a form. I've created an Add Record button, and what I want is when it is clicked, for it to automatically generate the next primary key in the table sequence. We are using what we call an 'H#' to uniquely identify patient invoices. The latest H# is 9679. So when we add a record, I want the form to automatically generate 9680 in the appropriate form box. Then we can manually enter first name, last name, etc. How do I do this? Ryan rye, Set the Default Value for your ID field to... =NZ(DMax("[YourID]","tblYourTable"),0) + 1  Signaturehth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." > Hello and thank you in advance for your help. > [quoted text clipped - 15 lines] > > Ryan Hi Al, I tried this and when I click my button to add a record the H# box reads "#Error". Is there something I've missed? My H# is defined as a number in my table. Thanks, Ryan > rye, > Set the Default Value for your ID field to... [quoted text clipped - 18 lines] > > > > Ryan rye, Cut and paste the code you used in your post. Where did you put the expression? I tested my code (using my object names... you use your own) Rename your H# field to something like InvoiceNo.... The "#" is a special character used in date operations, and should be avoided in object naming convention. In form design mode, select View/Properties. Highlight the field (InvoiceNo) you want to increment. In the properties dialog box, select the ALL tab, find the Default Value property, and in the text box to the right enter this... =NZ(DMax("[CustID]","tblCustomers"),0) + 1 (Use the name of your field and your table name) Whenever a new record is created, this will generate the next higher number in the table. Signaturehth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." > Hi Al, > [quoted text clipped - 32 lines] >> > >> > Ryan Here's a typical Auto-incrementing Number hack. Exact syntax varies depending on the Datatype of the H#. The first code would be for an IDNumber that is defined in the table as Text Private Sub Form_BeforeUpdate(Cancel As Integer) If Me.NewRecord Then If RecordsetClone.RecordCount = 0 Then Me.IDNumber = "1" Else Me.IDNumber = DMax("val([IDNumber])", "YourTableName") + 1 End If End If End Sub Here's the same code for an IDNumber defined as Numerical: Private Sub Form_BeforeUpdate(Cancel As Integer) If Me.NewRecord Then If RecordsetClone.RecordCount = 0 Then Me.IDNumber = 1 Else Me.IDNumber = DMax("[IDNum]", "YourTableName") + 1 End If End If End Sub Replace IDNumber with the name of the textbox on the form holding your H# Replace IDNum with the name of the field in your table that holds the H# Replace YourTableName with the actual name of your table that holds this info SignatureThere's ALWAYS more than one way to skin a cat! Answers/posts based on Access 2000/2003 Thanks Linq. I'm a bit of an Access novice though. Could you provide me with a step-by-step process of where to enter this code into Access? FYI - my IDnumber is defined as a number in my table. Ryan > Here's a typical Auto-incrementing Number hack. Exact syntax varies depending > on the Datatype of the H#. [quoted text clipped - 29 lines] > Replace YourTableName with the actual name of your table that holds this > info |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.