473,890 Members | 1,397 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
36 9680
111 New Member
Hey ChrisPadgham,

Thanks for the input. Looks good. It is a multiuser environment. How could I accomplish doing a save of the record immediately after. Also where would you put the following code:

Expand|Select|Wrap|Line Numbers
  1. Me.ID = DMax("[ID]","dbo_problems") + 1
Would you just put it behind the form or the control?
Jan 26 '12 #31
111 New Member
I have a form called "Problem Records List" with a button on it called "cmdNewItem ". The On Click event for "cmdNewItem " is a macro called "Detail list".

The "Detail list" macro contains the following:

Expand|Select|Wrap|Line Numbers
  1. Action: OpenForm  
  2. Arguments: Problem Records Details, Form, , 1=0, , Dialog
  4. Action: OnError 
  5. Arguments: Next,
  7. Action: Requery
  9. Action: SearchForRecord 
  10. Arguments: , , First, ="[ID]=" & Nz(DMax("[ID]",[Form].[RecordSource]),0)
Is there a way to include the following code in the "Detail list" macro :

Expand|Select|Wrap|Line Numbers
  1. Me.ID = DMax("[ID]","dbo_problems") + 1
Would some thing like this work?:

Expand|Select|Wrap|Line Numbers
  1. Action: OpenForm 
  2. Arguments: Problem Records Details, Form, , 1=0 And Me.ID = DMax("[ID]","dbo_problems") + 1 , , Dialog
Jan 26 '12 #32
12,516 Recognized Expert Moderator MVP
This will be the third time I've said it. Put the code in the BeforeInsert event of the form.
Jan 26 '12 #33
111 New Member
Can I accomplish that in a macro?

I understand what you are saying but, then anytime that form is opened it will assign that value. There is instances where the form is opened to view details of an existing record from another form by click the "ID" which is a hyperlink.
Jan 27 '12 #34
12,516 Recognized Expert Moderator MVP
Don't do it in a macro, use VBA code. It's the same as using any other event but instead of choosing which macro to run, you paste in the code in the VBA editor.

You don't understand what I'm saying. The code doesn't run every time the form is open. It only runs when a new record is inserted. It doesn't change existing records. That's why you use the BeforeInsert event, because it only fires before a new record is inserted. Hence the name BeforeInsert.
Jan 27 '12 #35
111 New Member
Rabbit, thanks for all your help. The following seemed to work as you indicated previously:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeInsert(Cancel As Integer)
  2. Me.ID = DMax("ID", "dbo_problems") + 1
  3. End Sub
I think I have a better understanding now. Thanks for being patient.
Jan 27 '12 #36
12,516 Recognized Expert Moderator MVP
No problem, good luck.
Jan 28 '12 #37

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: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
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: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
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.