473,883 Members | 1,705 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Auto Increment ID field of Linked table when entering on Form

111 New Member
I have a linked table called "dbo_proble ms". I have a form that uses "dbo_proble ms" as its data source. The linked table "dbo_proble ms" has a field called "ID" which is a Number. This field requires a value and when I enter a new record I want the ID field to use the last record ID + 1. So for example if the last record ID that was entered was "17586", then if a new record was entered the ID would automatically be calculated and be "17587" which is (17586 + 1).

Is there a way that I can accomplish this? Any ideas, suggestions or help would be greatly appreciated. Thank you in advance.


Jan 23 '12 #1
36 9678
12,516 Recognized Expert Moderator MVP
You could just use an auto increment field instead of calculating it.
Jan 23 '12 #2
111 New Member
I can not make any changes to the table design because it is a linked table sourced from a SQL Server. That is why I am trying to do it this way.
Jan 23 '12 #3
12,516 Recognized Expert Moderator MVP
Then you can use DMax() function to get the last number and then add 1 to that.
Jan 23 '12 #4
111 New Member
How would I do that ? On the form?
Jan 23 '12 #5
12,516 Recognized Expert Moderator MVP
Yes, on the form. I would use the BeforeInsert event to set the value.
Jan 23 '12 #6
111 New Member
How would it look ? I have never used this before. Something like :

Expand|Select|Wrap|Line Numbers
  1. =DMax(+1)
Jan 23 '12 #7
111 New Member
Do you mean the BeforeUpdate event?
Jan 23 '12 #8
111 New Member
Let me know what you think ?
Jan 23 '12 #9
12,516 Recognized Expert Moderator MVP
No, I mean the Before Insert Event of the form.

Your DMax function call is wrong.

I found this on google:

In Access, the DMax function returns the maximum value in a specified set of records (or domain).

The syntax for the DMax function is:

Expand|Select|Wrap|Line Numbers
  1. DMax ( expression, domain, [criteria] )
expression is the field that you wish to find the maximum value for.

domain is the set of records. This can be a table or a query name.

criteria is optional. It is the WHERE clause to apply to the domain.

For example:

Let's take a look at a simple example:

Expand|Select|Wrap|Line Numbers
  1. DMax("UnitPrice", "Order Details", "OrderID = 10248")
In this example, you would return the maximum UnitPrice from the Order Details table where the OrderID is 10248. This is the same as the following SQL statement:

Expand|Select|Wrap|Line Numbers
  1. SELECT Max([Order Details].UnitPrice) AS MaxOfUnitPrice
  2. FROM [Order Details]
  3. WHERE ((([Order Details].OrderID)=10248));
Jan 23 '12 #10

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

Similar topics

by: Kajol | last post by:
Hi All, I am using server version: 4.1.11-nt on windows 2000. I want to create a table with ID as auto increment and initialise it with a starting value of 1000. I am trying to do following DROP TABLE IF EXISTS `demo`;
by: Santosh | last post by:
Hi, I have a requirement in which I need to create an auto increment column in a file which will be unique. The following is what I am trying to do. 1) I need to use DDS to define the file 2) The auto-increment field can go upto a 5 digit numeric value. 3) As the records from this file will be deleted at regular intervals.I need the ability to recycle deleted record numbers after the 5 digit limit has been reached.
by: Annette Massie | last post by:
I have a database with a linked table within it. I have a query form that opens and allows the user to select criteria and then the form is filled with data found from the linked table. At what point is the table linking? For example, if I open the form it takes forever. I would like the form to open immediately, allow the user to enter data and then have it search the linked table. It appears it is opening the linked table and then...
by: Eitan M | last post by:
Hello, How can I find the last indentity of auto-increment field ? (If I inserted a record, then the auto-increment field is build automatically, and I want to find its value). Thanks :)
by: alphaomega3 | last post by:
OKay here is the situation. I am creating an inspection log database and have previously created an Non-conformance database. I am trying to get the NCRno field to auto-populate after I update the qtyrej field. the procedure is that we receive the material, inspect it, then log it. The Inspector will get to the point of accepting or rejecting and click a control button that takes them to a form that is has a linked table to enter the...
by: gomzi | last post by:
hi, I have an auto-increment field. I would like to know as to how I could reset it back to zero. thanks, gomzi.
by: chumlyumly | last post by:
Hello scripters - OS: Mac OSX Language: PHP w/ MySQL database I've created an insert page where a user inputs his info, which then goes to four different tables in a MySQL database. The tables are all linked with the field 'member_id', which is an auto-increment field in the parent table ('members'). I've been able to input multiple records into the other three tables 'specialty_groups', 'committee_interest' and 'committee_member'...
by: ZaphodBBB | last post by:
Hi I have a table that has as its Primary Key the Auto-Increment Field: Equipment_Number. In M.S. Access is there any way to define a starting number for the field? OR another way in which I could save the same problem would be: I have a form frmAddNew_Equipment, once all the fields are filled in correctly and it has been verified that there is no double entry etc, and the 'SAVE' button is clicked (unbound form), another form, bound...
by: David Wright | last post by:
Hello Everyone I would be grateful if someone could help me with the automatic increment of a field on my subform called ‘Test_SrNo’. I am Using Microsoft Office 2000. The auto entry of the incremented number would help to reduce errors and make data entry simpler. The next record in the Test_SrNo field should not have any entry in it until the text insertion point enters the Test_SrNo field. Example: If the last test serial number...
by: Formula | last post by:
Hi, i'm working on my first project on asp.net using c#. I'm adding a couble of fields to database but i need the primary key of the master table to add it to sub table. The database contains 2 tables : Question Table :questionID(primary key and identity) , questionInText
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.