Generate ID Number automatically in increments of 1

[Cosmos75]

Generate ID Number automatically in increments of 1 (Not an AUTONUMBER!)

I have a project management database that I have created that has this structure

Task has a one-to-many relationship with Sub-Task

Each Task has an ID Number (1 to 99), and Each Sub-Task has an ID number that should increase by 1 (1 to X) each time a Sub-Task is added to a Task. Is there anyway to have the Sub-Task number be generated automatically each time a new Sub-Task is entered? Right now it is entered manually and there are less than 5 Sub-Tasks per Task so it’s not a problem, but as it grows it’ll be harder to check whether a Sub-Task ID Number has already been used or not.

Thanks in advance!

 

[Graham T]

This May Help You....

Hi Cosmos

On the subform create a command button named cmdNewSubTaskID

On the On Click event of this add the following:

Private Sub cmdNewSubTaskID_Click()

        Me![lngSubTaskID] = NewSubTaskID()
        Me![strSubTaskName].SetFocus

End Sub

Then create a function called NewSubTaskID():

Public Function NewSubTaskID() As Long

On Error GoTo NewSubTaskID_Err

Dim lngNextID As Long

    'Find higest Sub Task ID in the tblSubTask table and add 1
    lngNextID = DMax("[lngSubTaskID]", "tblSubTask") + 1

    'Assign function the value of the Next ID
    NewSubTaskID = lngNextID

    'Exit function now after successful incrementing or after error message
Exit_NewSubTaskID:
Exit Function

    'If an error occurred, display a message, then go to Exit statement
NewSubTaskID_Err:
    MsgBox "Error " & Err & ": " & Error$

    Resume Exit_NewSubTaskID

End Function

This should then look at your Sub Task ID (lngSubTaskID) in your Sub Task Table (tblSubTask) and assign the next highest value to the Sub Task.

Hope this points you in the right direction. Post back if you are still having problems.

The attached zipped A2k example shows this in action.

Graham

 

[Cosmos75]

Thank you! I don't think I explained myself well enough.

My data should be like this
Task ID : 1
- Sub Task ID : 1
- Sub Task ID : 2
- Sub Task ID : 3
- Sub Task ID : 4

Task ID : 2
- Sub Task ID : 1
- Sub Task ID : 2
- Sub Task ID : 3

So when I go to add a Sub Task to Task 1, the new Sub Task ID should be 5.

And when I go to add a Sub Task to Task 2, the new Sub Task ID should be 4.

Am not that good with Access VBA but if it's close enough to Excel VBA (which I know more of but am no guru), I should be able to play around till I get it!

Thank you for your code which should help point me in the right direction!

 

[Rich]

How do you know which task the sub task belongs to?

 

[RichMorrison]

Sounds like you want:
Max("[SubTaskID]","[YourTable]","YourTable.[TaskID]=1")
and add 1 to the returned value.
You would need to generate the appropriate value for TaskID.

The rest of Graham's example looks like it should work.

RichM

 

[Cosmos75]

Basically the only way to know which Task a Sub-Task belongs to is by looking at the Form where the data is entered, running a Query, looking at the tables and expanding them or by running a report.

That's how it's set up and I can't change that!

Tried to alter the code


lngNextID = DMax("[lngSubTaskID]", "tblSubTask") + 1

Since it has the syntax

DMax(expr, domain, [criteria])

but I don't know how to make the [criteria] to be equal to the Value for the Current Task displayed on the form, so that it would search for the highest Sub-Task ID and include only Sub-Tasks that "belong" to the current displayed Task.

 

[Cosmos75]

Just FYI,

Here's something I found in the ACCESS Help file about doing Min/Max in DAO but I would rather not use DAO.

"Min, Max Functions Example

This example uses the Orders table to return the lowest and highest freight charges for orders shipped to the United Kingdom.

This example calls the EnumFields procedure, which you can find in the SELECT statement example.

Sub MinMaxX()


Dim dbs As Database, rst As Recordset


' Modify this line to include the path to Northwind

' on your computer.

Set dbs = OpenDatabase("Northwind.mdb")



' Return the lowest and highest freight charges for

' orders shipped to the United Kingdom.

Set rst = dbs.OpenRecordset("SELECT " _

& "Min(Freight) AS [Low Freight], " _

& "Max(Freight)AS [High Freight] " _

& "FROM Orders WHERE ShipCountry = 'UK';")



' Populate the Recordset.

rst.MoveLast



' Call EnumFields to print the contents of the

' Recordset. Pass the Recordset object and desired

' field width.

EnumFields rst, 12


dbs.Close


End Sub"

 

[Cosmos75]

RichMorrison

ACCESS 2000 doesn't recognize the function MAX??

Also, your formula still requires that I refer to the current TASK ID displayed on the form.

Max("[SubTaskID]","[YourTable]","YourTable.[TaskID]=1")

I don't know how to do that.....

 

[RichMorrison]

You wrote:
<<
RichMorrison

ACCESS 2000 doesn't recognize the function MAX??

Also, your formula still requires that I refer to the current TASK ID displayed on the form.

Max("[SubTaskID]","[YourTable]","YourTable.[TaskID]=1")
>>

Sorry, it is "DMax". My mistake.

Yes you have to refer to the current Task ID on the form. You need event code for some command button to add a Sub Task. The code would build a criteria string like:
strCriteria = "YourTable.[TaskID]=" & Me![txtTaskID]
intSubTask = DMax(blah, blah, strCriteria)
intNewSubTask = intSubTask + 1

HTH,
RichM

 

[Rich]

What will happen if the tasks exceed 100? and what are the reasons for these restrictions, if taskID is the FK in subTasks why the need for sequential numbers limited to 5 , the unique identifier can be any old number you like as long as it's unique even an autonumber. How will you use this field as a PK?

 

[Cosmos75]

RichMorrison,

THANK YOU! Will try out your code when I get the chance.

Rich,

The Sub-Task aren't from 1-99, they can be from 1 to any number.

I am not using the Task number OR the Sub-Task number the Primary Keys in the respective tables.

In tblTask, I have
TaskID - Primary Key
TaskNumber
TaskName

In tblSubTask, I have
SubTaskID - Primary Key
SubTaskNumber
SubTaskName

What I really want to have increase by 1 is the SubTaskNumber (by Task), not the SubTaskID.

I guess I was confusing you my refering to SubTaskNumber as Sub-Task ID. Sorry about that.

As for why they need to be sequetial, it's because that's how my supervisor wants it to be, so that he can see what SubTask was done in which order for each Task. Not a biggie since I am not using them as Primary Keys.

 

[Rich]

Doesn't seem much point in storing the number, you could use this function to display the numbers on your subform it will re-set for each task
Function GetLineNumber(F As Form, KeyName As String, KeyValue)
Dim RS As Recordset
Dim CountLines

On Error GoTo Err_GetLineNumber

Set RS = F.RecordsetClone

' Find the current record.
Select Case RS.Fields(KeyName).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
RS.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value?
Case DB_TEXT
RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
Exit Function
End Select

' Loop backward, counting the lines.
Do Until RS.BOF
CountLines = CountLines + 1
RS.MovePrevious
Loop

Bye_GetLineNumber:
' Return the result.
GetLineNumber = CountLines

Exit Function

Err_GetLineNumber:
CountLines = 0
Resume Bye_GetLineNumber

End Function

HTH

 

[Cosmos75]

Rich,

Well, the Sub-Task Number is shown in a report I generate and also, since that is how Sub-Tasks are assigned, keeping the Sub-Task Number as a field allows someone to run a criteria query using Task Number and Sub-Task Number to find all the details about it (Notes/Hours/Person Assigned).

Wouldn't using your code only display a calculated Sub-Task Number, but couldn't it change everytime based upon the sorting of the table? [Am not sure??]. If not, and it's based upon the Primary Key, whabout Sub-Tasks that aren't entered sequentially while the Sub-Task Number should be? Or am I not getting what you code is doing?

I have 11 tables in my database. Didn't go into all the detail since I only wanted to ask about one small part.

Thanks! Feel free to critisize any part or my design, I find it helpfull!

 

[Graham T]

Cosmos

You wrote >>

I am not using the Task number OR the Sub-Task number the Primary Keys in the respective tables.

In tblTask, I have
TaskID - Primary Key
TaskNumber
TaskName

In tblSubTask, I have
SubTaskID - Primary Key
SubTaskNumber
SubTaskName

Why are you storing a TaskID (PK) and TaskNumber (redundant data?) and SubTaskID (PK) and SubTaskNumber (redundant data?)

Also how are you relating the two tables, surely tblSubTask should contain the Primary Key - TaskID as a Foreign Key as One Task can have Many SubTasks?

Graham

 

[Cosmos75]

I think my nomenclature has people confused.

The TaskID and SubTaskID are Primary Keys.

The way I am tracking Sub Tasks, there will be repetition of SubTask Numbers, so I just created a Primary Key and named it SubTaskID so that I know which table the Primary Key is for (that’s how I name primary keys).

Same deal for TaskID, although right now there are no duplicate Task Numbers, in the future there could be so I just created a Primary Key and named it TaskID.

Also, it may be that I am not using the correct terms. (Just started using Access about 2 months ago for the first time)

Here’s how the two tables are

tblTask
Fields
PK: TaskID / Autonumber
Task Number / Number
Task Name / Text

TlbSubTask
Fields
PK: SubTaskID / Autonumber
FK: TaskID / Number (Lookup to TaskID)
Sub Task Number / Number
Sub Task Description / Text

tblTask has a one-to-many relationship with tblSubTask

 

[Graham T]

Hi Cosmos

You wrote >>

Same deal for TaskID, although right now there are no duplicate Task Numbers, in the future there could be so I just created a Primary Key and named it TaskID.

**************************

Each task should be a unique task, and your database should never contain duplicate Task Numbers.

So if you were to do a task today (for example Task Number 1, Create Mailing List), and then 6 months down the line you create a New Task with the same Task Number (but different TaskID ).

How would you query for a task number as this would be bringing up duplicates?

Graham

 

[Cosmos75]

Graham T,

Here's the thing, a Task Number actually represents a Facility (don't ask me why but that's how accounting has it set up) so there there will never be a duplicate Task Number.

Each Sub-Task number doesn't represent a particular type of sub-task, just whatever we work we did for that Task in sequential order (so there are duplicates of the Sub-Task Number).

I'm pretty new to Access so bear with me.

I've been building tables with my fields and just letting Access build a auto-number fields to use a primary key in all my tables. Is there an inherent problem with this? (especially concerned about adding data to my existing multi-table database with data from another database)

Thank you all for your help. I've learned more practical things here than reading books on Access!!

 

[Cosmos75]

ID Number increment by 1

Anyone know how to do this?

 

[RichMorrison]

Are you still stuck on how to make SubTaskID numbers ?

Some time ago you wrote
<<
Here’s how the two tables are

tblTask
Fields
PK: TaskID / Autonumber
Task Number / Number
Task Name / Text

TlbSubTask
Fields
PK: SubTaskID / Autonumber
FK: TaskID / Number (Lookup to TaskID)
Sub Task Number / Number
Sub Task Description / Text
>>


1) You should change TblSubTask so the Primary Key is:
PK: TaskID Long Int
PK: SubTaskID Long Int

that is; both TaskID and SubTaskID. Since TaskID is an AutoNumber, you will never have duplicates in TblSubTask.

2) Then see my previous post about getting the DMax SubTaskID for a given TaskID and then adding one. I use this method and it works just fine.

RichM

 

[Travis]

Add this code to the subform that is the SubTask Form:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.SubTaskID = GetLastSubTaskID(Me.Parent!TaskID)
End Sub

Private Function GetLastSubTaskID(TaskID As Long) As Long
Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
rst.Open "Select Top 1 SubTaskID From tblSubTask Where [TaskID]=" & TaskID & " Order by [SubTaskID] Desc", Application.CurrentProject.Connection, adOpenStatic, adLockReadOnly
If rst.RecordCount = 0 Then
GetLastSubTaskID = 1
Else
GetLastSubTaskID = rst!SubTaskID + 1
End If
End Function


This will get the last SubTaskID for the TaskID.

Another thing for data integrity is that on your SubTask Table you may want to set the TaskID and SubTaskID as the Primary Key. Doing this makes the rule that the combination of the two fields must be unique.

Thus Task 1 has subtasks that have a TaskID of 1 and the incremental 1 to whatever in the SubTask. This will also prevent you from having duplicate TaskID/SubTaskIDs entered into the SubTask Table.