Auto Increment ID field of Linked table when entering on Form

[Catalyst159]

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.

Regards,

Catalyst

[Rabbit]

You could just use an auto increment field instead of calculating it.

[Catalyst159]

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.

[Rabbit]

Then you can use DMax() function to get the last number and then add 1 to that.

[Catalyst159]

How would I do that ? On the form?

[Rabbit]

Yes, on the form. I would use the BeforeInsert event to set the value.

[Catalyst159]

How would it look ? I have never used this before. Something like :

=DMax(+1)

[Catalyst159]

Do you mean the BeforeUpdate event?

[Catalyst159]

Let me know what you think ?

[Rabbit]

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));

[Catalyst159]

But how do I incorporate the + 1to increment.

[Rabbit]

Add one after the DMax call.

[ppierce10]

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

[Catalyst159]

Thanks for the suggestion ppierce10. But I think that this might be overkill in my case.

[Catalyst159]

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.