Generate ID Number automatically in increments of 1

Is this thread helpfull to you?

  • YES! Am working on exactly the same thing! I hope someone answers!

    Votes: 1 10.0%
  • Yes, not exactly what I am having trouble with but close enough

    Votes: 0 0.0%
  • No, Not working on anything similiar.

    Votes: 7 70.0%
  • NO, I know how to do this.

    Votes: 2 20.0%

  • Total voters
    10
More problems..

Travis,

Thank you for your code, finally found the time to try it out and look at it closely.

Would it be too much to ask what your code is doing?

I think understand the flow..

What is this?
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


Are you creating a query that selects the Highest SubTaskID for the current TaskID, what is the rest of it??

If rst.RecordCount = 0 Then
GetLastSubTaskID = 1
Else
GetLastSubTaskID = rst!SubTaskID + 1
End If
End Function


Here is's IF no SubTaskID for this TaskID, then SubTaskID is 1, ELSE add 1 to the Highest SubTaskID.

I assume if the SubTaskID were to be renamed, I could still use GetLastSubTaskID in the code as the function name but would have to replace rst!SubTaskID and the OrderBy..

I'll have to change this code since my tables are split up. Not familiar with Access VBA but am willing to try and modify the code if you can tell me what the statements do. Probably use a Make-table query to put the exisiting TaskID and SubTaskIDs in a table that the code can refer to.

Another problem that has popped up, is that now, there are two project numbers. I've just put the two projecy numbers in a table, and put a lookup field to tblSubTask (i.e. Project number that the SubTask is charging to.

SO, now each ProjectNumber/TaskID/SubTaskID needs to be unique :eek:
Now, I have

Project1, Task1,SubTask1
Project1, Task1,SubTask2
Project1, Task1,SubTask3
Project2, Task1,SubTask1
Project2, Task1,SubTask2
Project2, Task1,SubTask3

and need to increment SubTaskID, not only by TaskID but also but ProjectNumber abd TaskID!!:mad:

Sigh... :(

Any help you can offer me will be greatly appreciated!
 
Relationship Layout

Here's what my tables look like...
 

Attachments

  • projectdb40.jpg
    projectdb40.jpg
    36.6 KB · Views: 303
First I want to appoligize for not getting back to you right away.

I have managed to break both of my wrists. (before you feel sorry for me, it was in a Softball game :D )

What is this?
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

This is a ADO recordset on the tblSubTask. It opens to only one record (the last one). You will need to not only pass the [TaskID] but also to the [ProjectID] to get the last SubTaskID.

You can build this query first using the QBE grid and then viewing the SQL view and coping the SQL here (of course you will need to add the parameters here in code & TaskID &)


If no record is present then it is assumed that this is the first task, otherwise you add one to the SubTaskID to get the next SubTaskID.
 
Travis,

Ouch! How can you type with TWO broken wrists?! :eek:

It is I who must apologize for taking so long before I could test out the code, been busy with two other databases!!

Thank you for your explanation! I usually have an idea on what the code needs to do but don't know enough about VBA to write the code.

I will try to find time to tailor the code to suit my db and will update you on the progress!

THANK YOU SO MUCH FOR YOUR HELP!!
 

Users who are viewing this thread

Back
Top Bottom