Assigning New ID# in Code - microsoft.public.access.formscoding

Why not just use an Autonumber field? If you can't for some reason, you can get a new number using the DMax function: lngNewId = Nz(DMax("[ID]","TableName"),0) + 1 -- Dave Hargis, Microsoft Access MVP "Gator" wrote: > What code can I use to assign a number as an ID# to a new record? I > basically would add 1 to the previous ID. > -- > Gator

I could probably use auto number...I just figured I would assign code, maybe for sake of learning. Also, what does "Nz" mean? -- Gator "Klatuu" wrote: > Why not just use an Autonumber field? > > If you can't for some reason, you can get a new number using the DMax > function: > > lngNewId = Nz(DMax("[ID]","TableName"),0) + 1 > -- > Dave Hargis, Microsoft Access MVP > > > "Gator" wrote: > > > What code can I use to assign a number as an ID# to a new record? I > > basically would add 1 to the previous ID. > > -- > > Gator

The Nz function is used to convert Null values to some other value. It does not chage the value of non Null values. The reason for putting it in this code is that when you create the first record, the DMax would return Null, becase there are no records. If you were to add 1 to Null, you would get Null. So to avoid that possibility, it converts the Null to 0 and 0 + 1 = 1. That would be the first record. -- Dave Hargis, Microsoft Access MVP "Gator" wrote: > I could probably use auto number...I just figured I would assign code, maybe > for sake of learning. Also, what does "Nz" mean? > -- > Gator > > > "Klatuu" wrote: > > > Why not just use an Autonumber field? > > > > If you can't for some reason, you can get a new number using the DMax > > function: > > > > lngNewId = Nz(DMax("[ID]","TableName"),0) + 1 > > -- > > Dave Hargis, Microsoft Access MVP > > > > > > "Gator" wrote: > > > > > What code can I use to assign a number as an ID# to a new record? I > > > basically would add 1 to the previous ID. > > > -- > > > Gator

What do I use to just simply add 1 to an existing record? -- Gator "Klatuu" wrote: > The Nz function is used to convert Null values to some other value. > It does not chage the value of non Null values. > The reason for putting it in this code is that when you create the first > record, the DMax would return Null, becase there are no records. If you were > to add 1 to Null, you would get Null. So to avoid that possibility, it > converts the Null to 0 and 0 + 1 = 1. That would be the first record. > -- > Dave Hargis, Microsoft Access MVP > > > "Gator" wrote: > > > I could probably use auto number...I just figured I would assign code, maybe > > for sake of learning. Also, what does "Nz" mean? > > -- > > Gator > > > > > > "Klatuu" wrote: > > > > > Why not just use an Autonumber field? > > > > > > If you can't for some reason, you can get a new number using the DMax > > > function: > > > > > > lngNewId = Nz(DMax("[ID]","TableName"),0) + 1 > > > -- > > > Dave Hargis, Microsoft Access MVP > > > > > > > > > "Gator" wrote: > > > > > > > What code can I use to assign a number as an ID# to a new record? I > > > > basically would add 1 to the previous ID. > > > > -- > > > > Gator

I don't understand your question. Do you mean add 1 to the id already in an existing record? -- Dave Hargis, Microsoft Access MVP "Gator" wrote: > What do I use to just simply add 1 to an existing record? > -- > Gator > > > "Klatuu" wrote: > > > The Nz function is used to convert Null values to some other value. > > It does not chage the value of non Null values. > > The reason for putting it in this code is that when you create the first > > record, the DMax would return Null, becase there are no records. If you were > > to add 1 to Null, you would get Null. So to avoid that possibility, it > > converts the Null to 0 and 0 + 1 = 1. That would be the first record. > > -- > > Dave Hargis, Microsoft Access MVP > > > > > > "Gator" wrote: > > > > > I could probably use auto number...I just figured I would assign code, maybe > > > for sake of learning. Also, what does "Nz" mean? > > > -- > > > Gator > > > > > > > > > "Klatuu" wrote: > > > > > > > Why not just use an Autonumber field? > > > > > > > > If you can't for some reason, you can get a new number using the DMax > > > > function: > > > > > > > > lngNewId = Nz(DMax("[ID]","TableName"),0) + 1 > > > > -- > > > > Dave Hargis, Microsoft Access MVP > > > > > > > > > > > > "Gator" wrote: > > > > > > > > > What code can I use to assign a number as an ID# to a new record? I > > > > > basically would add 1 to the previous ID. > > > > > -- > > > > > Gator

I am using a List Box Control on an Form to automatically add new records. I'm only entering data in a couple of the fields and the other fields are automatically filled based on the data in the list that I select (which I am not displaying on the form). The ID is a field that I am not planning to display and enter data. On the ADOrs.AddNew command, I want the ID to automatically be added, which will naturally be just an increment from the previous record. How do I code that increment? Thankx -- Gator "Klatuu" wrote: > I don't understand your question. Do you mean add 1 to the id already in an > existing record? > -- > Dave Hargis, Microsoft Access MVP > > > "Gator" wrote: > > > What do I use to just simply add 1 to an existing record? > > -- > > Gator > > > > > > "Klatuu" wrote: > > > > > The Nz function is used to convert Null values to some other value. > > > It does not chage the value of non Null values. > > > The reason for putting it in this code is that when you create the first > > > record, the DMax would return Null, becase there are no records. If you were > > > to add 1 to Null, you would get Null. So to avoid that possibility, it > > > converts the Null to 0 and 0 + 1 = 1. That would be the first record. > > > -- > > > Dave Hargis, Microsoft Access MVP > > > > > > > > > "Gator" wrote: > > > > > > > I could probably use auto number...I just figured I would assign code, maybe > > > > for sake of learning. Also, what does "Nz" mean? > > > > -- > > > > Gator > > > > > > > > > > > > "Klatuu" wrote: > > > > > > > > > Why not just use an Autonumber field? > > > > > > > > > > If you can't for some reason, you can get a new number using the DMax > > > > > function: > > > > > > > > > > lngNewId = Nz(DMax("[ID]","TableName"),0) + 1 > > > > > -- > > > > > Dave Hargis, Microsoft Access MVP > > > > > > > > > > > > > > > "Gator" wrote: > > > > > > > > > > > What code can I use to assign a number as an ID# to a new record? I > > > > > > basically would add 1 to the previous ID. > > > > > > -- > > > > > > Gator

Make it the default value of the control: =Nz(DMax("[ID]","TableName"),0) + 1 -- Dave Hargis, Microsoft Access MVP "Gator" wrote: > I am using a List Box Control on an Form to automatically add new records. > I'm only entering data in a couple of the fields and the other fields are > automatically filled based on the data in the list that I select (which I am > not displaying on the form). The ID is a field that I am not planning to > display and enter data. On the ADOrs.AddNew command, I want the ID to > automatically be added, which will naturally be just an increment from the > previous record. How do I code that increment? Thankx > -- > Gator > > > "Klatuu" wrote: > > > I don't understand your question. Do you mean add 1 to the id already in an > > existing record? > > -- > > Dave Hargis, Microsoft Access MVP > > > > > > "Gator" wrote: > > > > > What do I use to just simply add 1 to an existing record? > > > -- > > > Gator > > > > > > > > > "Klatuu" wrote: > > > > > > > The Nz function is used to convert Null values to some other value. > > > > It does not chage the value of non Null values. > > > > The reason for putting it in this code is that when you create the first > > > > record, the DMax would return Null, becase there are no records. If you were > > > > to add 1 to Null, you would get Null. So to avoid that possibility, it > > > > converts the Null to 0 and 0 + 1 = 1. That would be the first record. > > > > -- > > > > Dave Hargis, Microsoft Access MVP > > > > > > > > > > > > "Gator" wrote: > > > > > > > > > I could probably use auto number...I just figured I would assign code, maybe > > > > > for sake of learning. Also, what does "Nz" mean? > > > > > -- > > > > > Gator > > > > > > > > > > > > > > > "Klatuu" wrote: > > > > > > > > > > > Why not just use an Autonumber field? > > > > > > > > > > > > If you can't for some reason, you can get a new number using the DMax > > > > > > function: > > > > > > > > > > > > lngNewId = Nz(DMax("[ID]","TableName"),0) + 1 > > > > > > -- > > > > > > Dave Hargis, Microsoft Access MVP > > > > > > > > > > > > > > > > > > "Gator" wrote: > > > > > > > > > > > > > What code can I use to assign a number as an ID# to a new record? I > > > > > > > basically would add 1 to the previous ID. > > > > > > > -- > > > > > > > Gator

I'm geting an error my table is main2007 and the field is ID adors.AddNew adors!ID = Nz(DMax([ID], main2007), 0) + 1 -- Gator "Klatuu" wrote: > Make it the default value of the control: > =Nz(DMax("[ID]","TableName"),0) + 1 > > -- > Dave Hargis, Microsoft Access MVP > > > "Gator" wrote: > > > I am using a List Box Control on an Form to automatically add new records. > > I'm only entering data in a couple of the fields and the other fields are > > automatically filled based on the data in the list that I select (which I am > > not displaying on the form). The ID is a field that I am not planning to > > display and enter data. On the ADOrs.AddNew command, I want the ID to > > automatically be added, which will naturally be just an increment from the > > previous record. How do I code that increment? Thankx > > -- > > Gator > > > > > > "Klatuu" wrote: > > > > > I don't understand your question. Do you mean add 1 to the id already in an > > > existing record? > > > -- > > > Dave Hargis, Microsoft Access MVP > > > > > > > > > "Gator" wrote: > > > > > > > What do I use to just simply add 1 to an existing record? > > > > -- > > > > Gator > > > > > > > > > > > > "Klatuu" wrote: > > > > > > > > > The Nz function is used to convert Null values to some other value. > > > > > It does not chage the value of non Null values. > > > > > The reason for putting it in this code is that when you create the first > > > > > record, the DMax would return Null, becase there are no records. If you were > > > > > to add 1 to Null, you would get Null. So to avoid that possibility, it > > > > > converts the Null to 0 and 0 + 1 = 1. That would be the first record. > > > > > -- > > > > > Dave Hargis, Microsoft Access MVP > > > > > > > > > > > > > > > "Gator" wrote: > > > > > > > > > > > I could probably use auto number...I just figured I would assign code, maybe > > > > > > for sake of learning. Also, what does "Nz" mean? > > > > > > -- > > > > > > Gator > > > > > > > > > > > > > > > > > > "Klatuu" wrote: > > > > > > > > > > > > > Why not just use an Autonumber field? > > > > > > > > > > > > > > If you can't for some reason, you can get a new number using the DMax > > > > > > > function: > > > > > > > > > > > > > > lngNewId = Nz(DMax("[ID]","TableName"),0) + 1 > > > > > > > -- > > > > > > > Dave Hargis, Microsoft Access MVP > > > > > > > > > > > > > > > > > > > > > "Gator" wrote: > > > > > > > > > > > > > > > What code can I use to assign a number as an ID# to a new record? I > > > > > > > > basically would add 1 to the previous ID. > > > > > > > > -- > > > > > > > > Gator

nevermind, it works. I removed the brackets on ID. -- Gator "Klatuu" wrote: > Make it the default value of the control: > =Nz(DMax("[ID]","TableName"),0) + 1 > > -- > Dave Hargis, Microsoft Access MVP > > > "Gator" wrote: > > > I am using a List Box Control on an Form to automatically add new records. > > I'm only entering data in a couple of the fields and the other fields are > > automatically filled based on the data in the list that I select (which I am > > not displaying on the form). The ID is a field that I am not planning to > > display and enter data. On the ADOrs.AddNew command, I want the ID to > > automatically be added, which will naturally be just an increment from the > > previous record. How do I code that increment? Thankx > > -- > > Gator > > > > > > "Klatuu" wrote: > > > > > I don't understand your question. Do you mean add 1 to the id already in an > > > existing record? > > > -- > > > Dave Hargis, Microsoft Access MVP > > > > > > > > > "Gator" wrote: > > > > > > > What do I use to just simply add 1 to an existing record? > > > > -- > > > > Gator > > > > > > > > > > > > "Klatuu" wrote: > > > > > > > > > The Nz function is used to convert Null values to some other value. > > > > > It does not chage the value of non Null values. > > > > > The reason for putting it in this code is that when you create the first > > > > > record, the DMax would return Null, becase there are no records. If you were > > > > > to add 1 to Null, you would get Null. So to avoid that possibility, it > > > > > converts the Null to 0 and 0 + 1 = 1. That would be the first record. > > > > > -- > > > > > Dave Hargis, Microsoft Access MVP > > > > > > > > > > > > > > > "Gator" wrote: > > > > > > > > > > > I could probably use auto number...I just figured I would assign code, maybe > > > > > > for sake of learning. Also, what does "Nz" mean? > > > > > > -- > > > > > > Gator > > > > > > > > > > > > > > > > > > "Klatuu" wrote: > > > > > > > > > > > > > Why not just use an Autonumber field? > > > > > > > > > > > > > > If you can't for some reason, you can get a new number using the DMax > > > > > > > function: > > > > > > > > > > > > > > lngNewId = Nz(DMax("[ID]","TableName"),0) + 1 > > > > > > > -- > > > > > > > Dave Hargis, Microsoft Access MVP > > > > > > > > > > > > > > > > > > > > > "Gator" wrote: > > > > > > > > > > > > > > > What code can I use to assign a number as an ID# to a new record? I > > > > > > > > basically would add 1 to the previous ID. > > > > > > > > -- > > > > > > > > Gator

Similiar Articles:

Assign one default Site ID - microsoft.public.greatplains ...
Hi, I am needing to assign one default site ID to thousands of items ... to do this and update the locati= on > code ... By default, new sites only have one feature ...

Assign from a list of names randomly - microsoft.public.access ...
Run this code: Global Const StartSht = "Sheet1 ... Global Const NewSht = "SheetX" 'Name for the new ... Assign one default Site ID - microsoft.public.greatplains ...

parse data from memo field to text field - microsoft.public.access ...
... one "note" is finished and another one (i.e., new ID ... assigned to the case), Note Type (a reference code ... take each record from > > my > > current table assign an ID ...

Can I mass-change macro paths of tool buttons? - microsoft.public ...
Upon getting a new computer, I resto... ... I know I can assign an ID, but that's a limited library ... Is there a way to go beyond the limited ID numbers in code to ...

Error ==> You can't assign value to this object - microsoft ...
If you still can't assign value to this object, post back with what ... Veni, Vidi, Velcro" (I came; I saw; I stuck around.) "New_Access" wrote: > I have a code on 'on ...

[ODBC Driver Manager] Data source name not found and no default dr ...
Is there a problem with my code? Public Function Update_Assign() As ... Type As Integer Doc_Type = Forms!Assignment_frm!Type_ID ... Connection Dim cmdCommand As New ...

Create and assign an outlook task using VBA? - microsoft.public ...
Outlook VBA question creating and assigning a task.: outlook, vba ... I have the following vba code that will create a new task item in a public folder.

Getting a Return Value from a Stored Procedure in VBA - microsoft ...
... Server, and I get the return value My VBA code ... work, I get that I have too many arguments assigned ... id", adInteger, > > adParamInput, , Forms("new_content").slide_id ...

Automatic Data Entry Script OnChange Event for Date Field ...
... Event for Date Field. has anyone assigned 'Today date'( new ... field, I have the following code. DatefiledName.value = new Date ... Change Automatic ID Field? - microsoft ...

SQL Query in VBS - microsoft.public.scripting.vbscript
... to return an integer value and assign it to a variable in my script. the code ... Initial Catalog=MyDatabaseName;User Id ... accept the new query (or new SQL string ...

Assigning New ID# in Code
What code can I use to assign a number as an ID# to a new record? I basically would add 1 to the previous ID.

Assigning Roles to Users (C#) : Official Microsoft Site
... user belongs to, and the ability to assign or ... Code removed for brevity ... CreateUserWizard’s Smart Tag and add a new WizardStep, setting its ID to ...

Assigning session ID - Dev Shed
New user comes is , and he is assigned with a new session ID (which ,let's say ... id integer on the server, and for each session id, increment it by one. - The code is ...

c# - Code First - Foreign Key Reference not loading on assigning ...
Code First - Foreign Key Reference not loading on assigning Id ... BirthDate = DateTime.Now, AccessCode = new AccessCode { Code = "ABC ...

Statements in Visual Basic
... can loop or branch through blocks of code. Executable statements include Assignment ... class when you declare it by using the New ... proc.ProcessName, proc.Id ...

7/10/2012 12:22:53 PM