473,320 Members | 2,003 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Auto Increment ID field of Linked table when entering on Form

Catalyst159
111 100+
I have a linked table called "dbo_problems". I have a form that uses "dbo_problems" as its data source. The linked table "dbo_problems" 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.

Regards,

Catalyst
Jan 23 '12 #1

✓ answered by Rabbit

More like
Expand|Select|Wrap|Line Numbers
  1. DMax("ID", "dbo_problems") + 1
There's no need to specify which ID because it doesn't matter. You just want the max.

36 9551
Rabbit
12,516 Expert Mod 8TB
You could just use an auto increment field instead of calculating it.
Jan 23 '12 #2
Catalyst159
111 100+
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
Rabbit
12,516 Expert Mod 8TB
Then you can use DMax() function to get the last number and then add 1 to that.
Jan 23 '12 #4
Catalyst159
111 100+
How would I do that ? On the form?
Jan 23 '12 #5
Rabbit
12,516 Expert Mod 8TB
Yes, on the form. I would use the BeforeInsert event to set the value.
Jan 23 '12 #6
Catalyst159
111 100+
How would it look ? I have never used this before. Something like :

Expand|Select|Wrap|Line Numbers
  1. =DMax(+1)
Jan 23 '12 #7
Catalyst159
111 100+
Do you mean the BeforeUpdate event?
Jan 23 '12 #8
Catalyst159
111 100+
Let me know what you think ?
Jan 23 '12 #9
Rabbit
12,516 Expert Mod 8TB
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
Catalyst159
111 100+
But how do I incorporate the + 1to increment.
Jan 23 '12 #11
Rabbit
12,516 Expert Mod 8TB
Add one after the DMax call.
Jan 23 '12 #12
This how I am doing this with my app.
Expand|Select|Wrap|Line Numbers
  1. 'Set Event Number if one is needed
  2.    Dim db As Database
  3.     Dim strYear As String, strNumber As String, strTrackingNumber As String, strFinalNumber As String
  4.     Dim strID As String
  5.     Dim StrSql As String
  6.     Dim qdf As QueryDef
  7.     Dim rs As Recordset
  8.     strYear = Year(Now())    ' Get the latest year value (reset sequence #'s if needed)
  9.     Set db = CurrentDb()
  10.     Set qdf = db.CreateQueryDef("")
  11.     qdf.SQL = "SELECT MAX(EventNumber) AS MaxEventNumber FROM tblIncident WHERE EventNumber LIKE '" & strYear & "-*'"
  12.     'qdf.SQL = qdf.SQL & " WITH OWNERACCESS OPTION;"
  13.     Set rs = qdf.OpenRecordset(dbOpenDynaset, dbSeeChanges)
  14.     If IsNull(rs("MaxEventNumber")) Then
  15.         strTrackingNumber = strYear & "-1000"
  16.         MsgBox strTrackingNumber
  17.         Me.EventNumber = strTrackingNumber
  18.         Me.UseofForce.Value = True
  19.     Else
  20.         strNumber = rs("MaxEventNumber")
  21.         strFinalNumber = (Mid(strNumber, 6) + 1)
  22.         MsgBox (strFinalNumber), , "FinalNumber"
  23.         strTrackingNumber = strYear & "-" & strFinalNumber
  24.         Me.EventNumber = strTrackingNumber
  25.         Me.UseofForce.Value = True
  26.     End If
  27. End Sub
Jan 23 '12 #13
Catalyst159
111 100+
Thanks for the suggestion ppierce10. But I think that this might be overkill in my case.
Jan 23 '12 #14
Catalyst159
111 100+
I will try to explain how the form structure is:

I have a form called "Problem Records List" and a form called "Problem Records Detail".

The "Problem Records List" form displays records in a split form where the "ID" field is a hyperlink that you can click to actually bring up the the record whose ID you clicked in the "Problem Records Detail" form. There is also a button on the "Problem Records List" form called "cmdNewItem" which opens the "Problem Records Detail" form for entry of a new record, however the Problem ID textbox in the "Problem Records Detail" form is blank. This is where I would like to automatically insert the new ProblemID value by using the last ProblemID + 1. I hope this explains it better. Let me know.
Jan 23 '12 #15
Catalyst159
111 100+
Anyone have any Ideas on how to accomplish this.
Jan 24 '12 #16
Rabbit
12,516 Expert Mod 8TB
Yes, basically what I laid out before. Use the BeforeInsert event of the form to populate the ProblemID. Use the DMax() function to get the last ID and add one to it. The DMax() syntax is in post #10.
Jan 24 '12 #17
Catalyst159
111 100+
The problem is that I only want to use the DMax() when I use the 'cmdNewItem' button to open the "Problem Records Detail" form for adding a new record.
Jan 24 '12 #18
Rabbit
12,516 Expert Mod 8TB
Adding a new record triggers the BeforeInsert event.
Jan 24 '12 #19
Catalyst159
111 100+
So it should work then right ?
Jan 24 '12 #20
Catalyst159
111 100+
But when I click the ID in the "Problem Records List" Form, it will also bring up that record in the "Problem Records Detail" form. Will this be problematic in that case?
Jan 24 '12 #21
Rabbit
12,516 Expert Mod 8TB
Yes it will work. No it won't be a problem.
Jan 24 '12 #22
Catalyst159
111 100+
I am still a little confused with the syntax. Is something like the following ok:

Expand|Select|Wrap|Line Numbers
  1. DMax("ID", "dbo_problems", "ProblemID = ID +1")
Jan 24 '12 #23
Rabbit
12,516 Expert Mod 8TB
More like
Expand|Select|Wrap|Line Numbers
  1. DMax("ID", "dbo_problems") + 1
There's no need to specify which ID because it doesn't matter. You just want the max.
Jan 24 '12 #24
Catalyst159
111 100+
Ok, let me try that. This should then populate the ID textbox on the "Problem Records Detail" form?
Jan 24 '12 #25
Rabbit
12,516 Expert Mod 8TB
By itself? No. That only gets the value, you have to assign it to the control you want that value to go into.
Jan 24 '12 #26
Catalyst159
111 100+
How can I test that
Expand|Select|Wrap|Line Numbers
  1. DMax("ID", "dbo_problems") + 1 
is getting the correct value using the Immediate Pane to test it?
Jan 24 '12 #27
Catalyst159
111 100+
How can I assign it to the control you want that value to go into?
Jan 24 '12 #28
Catalyst159
111 100+
To Test in immediate window I used:

Expand|Select|Wrap|Line Numbers
  1. ? DMax("[ID]", "dbo_problems") + 1
This does work.

How can I assign this to the control on the form that I want the value to go into though?
Jan 24 '12 #29
Expand|Select|Wrap|Line Numbers
  1. Me.ID = DMax("[ID]","dbo_problems") + 1
If you are in a multiuser environment you may like to do a save of the record immediately after this to prevent two users obtaining the same record id
Jan 24 '12 #30
Catalyst159
111 100+
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
Catalyst159
111 100+
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
  3.  
  4. Action: OnError 
  5. Arguments: Next,
  6.  
  7. Action: Requery
  8.  
  9. Action: SearchForRecord 
  10. Arguments: , , First, ="[ID]=" & Nz(DMax("[ID]",[Form].[RecordSource]),0)
  11.  
  12.  
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
  3.  
Jan 26 '12 #32
Rabbit
12,516 Expert Mod 8TB
This will be the third time I've said it. Put the code in the BeforeInsert event of the form.
Jan 26 '12 #33
Catalyst159
111 100+
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
Rabbit
12,516 Expert Mod 8TB
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
Catalyst159
111 100+
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
Rabbit
12,516 Expert Mod 8TB
No problem, good luck.
Jan 28 '12 #37

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

Similar topics

2
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...
0
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)...
1
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...
9
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 :)
2
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...
2
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.
0
chumlyumly
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...
11
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...
5
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...
1
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...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

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.