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
More like - 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
You could just use an auto increment field instead of calculating it.
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.
Then you can use DMax() function to get the last number and then add 1 to that.
How would I do that ? On the form?
Yes, on the form. I would use the BeforeInsert event to set the value.
How would it look ? I have never used this before. Something like :
Do you mean the BeforeUpdate event?
Let me know what you think ?
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: - 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: - 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: - SELECT Max([Order Details].UnitPrice) AS MaxOfUnitPrice
-
FROM [Order Details]
-
WHERE ((([Order Details].OrderID)=10248));
But how do I incorporate the + 1to increment.
Add one after the DMax call.
This how I am doing this with my app. - 'Set Event Number if one is needed
-
Dim db As Database
-
Dim strYear As String, strNumber As String, strTrackingNumber As String, strFinalNumber As String
-
Dim strID As String
-
Dim StrSql As String
-
Dim qdf As QueryDef
-
Dim rs As Recordset
-
strYear = Year(Now()) ' Get the latest year value (reset sequence #'s if needed)
-
Set db = CurrentDb()
-
Set qdf = db.CreateQueryDef("")
-
qdf.SQL = "SELECT MAX(EventNumber) AS MaxEventNumber FROM tblIncident WHERE EventNumber LIKE '" & strYear & "-*'"
-
'qdf.SQL = qdf.SQL & " WITH OWNERACCESS OPTION;"
-
Set rs = qdf.OpenRecordset(dbOpenDynaset, dbSeeChanges)
-
If IsNull(rs("MaxEventNumber")) Then
-
strTrackingNumber = strYear & "-1000"
-
MsgBox strTrackingNumber
-
Me.EventNumber = strTrackingNumber
-
Me.UseofForce.Value = True
-
Else
-
strNumber = rs("MaxEventNumber")
-
strFinalNumber = (Mid(strNumber, 6) + 1)
-
MsgBox (strFinalNumber), , "FinalNumber"
-
strTrackingNumber = strYear & "-" & strFinalNumber
-
Me.EventNumber = strTrackingNumber
-
Me.UseofForce.Value = True
-
End If
-
End Sub
Thanks for the suggestion ppierce10. But I think that this might be overkill in my case.
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.
Anyone have any Ideas on how to accomplish this.
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.
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.
Adding a new record triggers the BeforeInsert event.
So it should work then right ?
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?
Yes it will work. No it won't be a problem.
I am still a little confused with the syntax. Is something like the following ok: - DMax("ID", "dbo_problems", "ProblemID = ID +1")
More like - DMax("ID", "dbo_problems") + 1
There's no need to specify which ID because it doesn't matter. You just want the max.
Ok, let me try that. This should then populate the ID textbox on the "Problem Records Detail" form?
By itself? No. That only gets the value, you have to assign it to the control you want that value to go into.
How can I test that - DMax("ID", "dbo_problems") + 1
is getting the correct value using the Immediate Pane to test it?
How can I assign it to the control you want that value to go into?
To Test in immediate window I used: - ? 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?
- 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
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: - Me.ID = DMax("[ID]","dbo_problems") + 1
Would you just put it behind the form or the control?
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: -
Action: OpenForm
-
Arguments: Problem Records Details, Form, , 1=0, , Dialog
-
-
Action: OnError
-
Arguments: Next,
-
-
Action: Requery
-
-
Action: SearchForRecord
-
Arguments: , , First, ="[ID]=" & Nz(DMax("[ID]",[Form].[RecordSource]),0)
-
-
Is there a way to include the following code in the "Detail list" macro : - Me.ID = DMax("[ID]","dbo_problems") + 1
Would some thing like this work?: -
Action: OpenForm
-
Arguments: Problem Records Details, Form, , 1=0 And Me.ID = DMax("[ID]","dbo_problems") + 1 , , Dialog
-
This will be the third time I've said it. Put the code in the BeforeInsert event of the form.
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.
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.
Rabbit, thanks for all your help. The following seemed to work as you indicated previously: - Private Sub Form_BeforeInsert(Cancel As Integer)
-
Me.ID = DMax("ID", "dbo_problems") + 1
-
End Sub
I think I have a better understanding now. Thanks for being patient.
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...
|
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)...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
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....
| |