469,282 Members | 1,732 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,282 developers. It's quick & easy.

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 9173
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

Post your reply

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

Similar topics

reply views Thread by Santosh | last post: by
1 post views Thread by Annette Massie | last post: by
9 posts views Thread by Eitan M | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.