I am finding AutoNumber is limiting what I can do with a subform and
how it interacts with other data/controls on the parent form. Anyone
have ideas on how to code a function as a substitute that can increment
an ID field automatically in the same way the AutoNumber does?

THANKS FOR YOUR ASSISTANCE!

Tammy

One way of simulating autonumber may be found here:
http://www.rogersaccesslibrary.com/d...berProblem.mdb
(Watch out for line wrapping)
You haven't mentioned what you find limiting about autonumber, but I assume
that you want something like an invoice number or some such that the user
can see, and you want it to increment without gaps in the numbering
sequence. However, a number produced by any means will interact with other
tables in pretty much the same way as a number produced by some other means,
so if there is some problem other than the number sequence, the simulated
autonumber code will probably not help.

"FireGeek822" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I am finding AutoNumber is limiting what I can do with a subform and
> how it interacts with other data/controls on the parent form. Anyone
> have ideas on how to code a function as a substitute that can increment
> an ID field automatically in the same way the AutoNumber does?
>
> THANKS FOR YOUR ASSISTANCE!
>
> Tammy
>

Thanks Bruce. Will check it out!

T-

Bruce -

I checked out your link for A2K (I am running Access 2003). It appears
as if this person's example is only running the code to increment the
number after an Error. If at all possible I like to avoid errors -
good coding. Not quite what I am after.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
On Error GoTo Err_Form_Error

Response = IncrementField(DataErr)

Exit_Form_Error:
Exit Sub

Err_Form_Error:
MsgBox Err.Description
Resume Exit_Form_Error

End Sub

Function IncrementField(DataErr)
If DataErr = 3022 Then
Me!ProductID = DMax("ProductID", "Product") + 1
IncrementField = acDataErrContinue
End If
End Function

Bruce -

I typed out an explanation of what is going on with the AutoNumber
problem and it appears as if it never got posted here... ?????

Anyway. I doubt I can use the AutoNumbering if this is a multiperson
environment - or am I wrong???

T-

Hey Bruce,

My issue with the AutoNumber -

This is actually a "Project Database". This organizes a Project ToDo
List and tracks time spent on all pieces of our projects. On my
Project form (tblProjects) I have the following:

- TreeView Control that populates a To Do List (tblToDo) for the
Project. Organizes the list by generic item categories such as
(Assessment, Training, Implementation, Testing, etc.) and SubItems that
fall under these categorise. For Training there are items such as
Schedule Training, Create PowerPoint Presentation, etc. When an item
is done, we can select the item, enter the date the item was completed
in a separate textbox and the amount of time spent on this item in
another textbox. We then can click a button and it removed this item
from the TreeView and places it, the date and time into a listbox of
completed items below. This updates 3 fields in the tblToDo table
(date, time and completed).

- ListBox as explained above.

- SubForm (where my problem lies) - This is our smaller work log
(tblWrkLog). We are actually 3 Project Coordinators for an HR office.
If any of us are out, we need to be able to follow someone's work on a
project. These projects include others doing various items at any of
our 9 offices in states across the country. Some ToDo items may take
longer to do than just create a PowerPoint presentation. So the work
log tracks all the small details. The 3 tables involved really don't
have any fields that can be "Key" fields other than creating an ID
field as a "Key" and using AutoNumber for that field. This Work Log
subform kicks out errors when I want to use/reference the number from
the WorkLogID field (the autonumber field).


We will be having any of 4 users using this database and possibly at
the same time. We really like being able to see all 3 items on one
form (ToDo List, Completed Items List and Work Log).

I am sooo close to finishing this off. Just irritating problems like
this popping up now.

Thanks for your help - hope this explanation isn't too confusing.

T-

This code is the default value for the incrementing field:
= DMax("ProductID", "Product") + 1
In Roger's example, ProductID a field in the Product table. Substitute your
own field and table names.
You set the number in the Default Value property of the control (text box)
bound to the incremented field. The default value applies only for a new
record. If you go back to that record later, default value will not come
into play.
The error handling code is used in a multi-user environment. If two users
are in the process of creating a new record at the same time, both will see
the same number in ProductID. The first one who navigates away from that
record (and thus saves it) will have that number assigned to the just-saved
record. The other user will generate error 3022 (duplicate primary key, I
believe) when saving the record. Rather than notifying the user that error
3022 has occurred, Access will go back and get another number. In Roger's
database (at least the copy I have, which I have had for some time)
ProductID is not set as the primary key, but I think it needs to be in order
for this error handling to work.
Another comment would be that it is best to use a different name for the
control and the field. If ProductID is the field, I would name the text box
txtProductID. In Roger's example this does not matter, but in some cases
there can be difficulties if they do not have distinct names.

"FireGeek822" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Bruce -
>
> I checked out your link for A2K (I am running Access 2003). It appears
> as if this person's example is only running the code to increment the
> number after an Error. If at all possible I like to avoid errors -
> good coding. Not quite what I am after.
>
> Private Sub Form_Error(DataErr As Integer, Response As Integer)
> On Error GoTo Err_Form_Error
>
> Response = IncrementField(DataErr)
>
> Exit_Form_Error:
> Exit Sub
>
> Err_Form_Error:
> MsgBox Err.Description
> Resume Exit_Form_Error
>
> End Sub
>
> Function IncrementField(DataErr)
> If DataErr = 3022 Then
> Me!ProductID = DMax("ProductID", "Product") + 1
> IncrementField = acDataErrContinue
> End If
> End Function
>

I'm a little confused on a few points. It seems clear enough that
CompletedItems are items that started on the ToDo list. You say that they
are placed in a list box after they are completed. Why a list box? And are
they removed from the ToDo table after they are completed? If so, I think a
better approach would be to have two queries based on the ToDo table. If
there is a completed items check box, one query would be for records in
which that field is checked, and another for records in which it is not.
The same idea could apply to a DateCompleted field, or whatever. Your
Project main form could have two subforms based on those queries.
I don't really see how the WorkLog fits into this, nor do I follow your
meaning when you write "This Work Log subform kicks out errors when I want
to use/reference the number from the WorkLogID field (the autonumber field).
A brief description of the database structure would help here, particularly
the relationships. When you describe a database's structure, all you need
to do is something like this:

tblProject
ProjectID (autonumber primary key)
ProjectManager
Client
etc.

tblToDo
ToDoID (autonumber primary key)
ProjectID (foreign key, linked to ProjectID in tblProject)
DateStarted, etc.

And so forth for the rest of your tables. It sounds as if there are three
levels: Project, ToDo, and ToDoDetails. Is my understanding correct?

Autonumbers are fine for primary keys, but should not be seen by the user.
If you need an orderly progression of project numbers, you could use
something like Roger's code to produce it, but that number would need to be
the primary key of tblProjects. I'm not quite sure how that would work with
subform records, so the best choice may be to explicitly save the Project
record before entering any ToDo items or any other such details.

"FireGeek822" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hey Bruce,
>
> My issue with the AutoNumber -
>
> This is actually a "Project Database". This organizes a Project ToDo
> List and tracks time spent on all pieces of our projects. On my
> Project form (tblProjects) I have the following:
>
> - TreeView Control that populates a To Do List (tblToDo) for the
> Project. Organizes the list by generic item categories such as
> (Assessment, Training, Implementation, Testing, etc.) and SubItems that
> fall under these categorise. For Training there are items such as
> Schedule Training, Create PowerPoint Presentation, etc. When an item
> is done, we can select the item, enter the date the item was completed
> in a separate textbox and the amount of time spent on this item in
> another textbox. We then can click a button and it removed this item
> from the TreeView and places it, the date and time into a listbox of
> completed items below. This updates 3 fields in the tblToDo table
> (date, time and completed).
>
> - ListBox as explained above.
>
> - SubForm (where my problem lies) - This is our smaller work log
> (tblWrkLog). We are actually 3 Project Coordinators for an HR office.
> If any of us are out, we need to be able to follow someone's work on a
> project. These projects include others doing various items at any of
> our 9 offices in states across the country. Some ToDo items may take
> longer to do than just create a PowerPoint presentation. So the work
> log tracks all the small details. The 3 tables involved really don't
> have any fields that can be "Key" fields other than creating an ID
> field as a "Key" and using AutoNumber for that field. This Work Log
> subform kicks out errors when I want to use/reference the number from
> the WorkLogID field (the autonumber field).
>
>
> We will be having any of 4 users using this database and possibly at
> the same time. We really like being able to see all 3 items on one
> form (ToDo List, Completed Items List and Work Log).
>
> I am sooo close to finishing this off. Just irritating problems like
> this popping up now.
>
> Thanks for your help - hope this explanation isn't too confusing.
>
> T-
>

Bruce,

your project management database sounds interesting...do you have a template
that you could send me...I hope I am not asking for too much...we are always
searching of ways to increase our productivity, efficiency and customer
relations...we have also had issues with autonumbering. thanks and hope that
you can assist me. my email address is (E-Mail Removed)

"BruceM" wrote:

> I'm a little confused on a few points. It seems clear enough that
> CompletedItems are items that started on the ToDo list. You say that they
> are placed in a list box after they are completed. Why a list box? And are
> they removed from the ToDo table after they are completed? If so, I think a
> better approach would be to have two queries based on the ToDo table. If
> there is a completed items check box, one query would be for records in
> which that field is checked, and another for records in which it is not.
> The same idea could apply to a DateCompleted field, or whatever. Your
> Project main form could have two subforms based on those queries.
> I don't really see how the WorkLog fits into this, nor do I follow your
> meaning when you write "This Work Log subform kicks out errors when I want
> to use/reference the number from the WorkLogID field (the autonumber field).
> A brief description of the database structure would help here, particularly
> the relationships. When you describe a database's structure, all you need
> to do is something like this:
>
> tblProject
> ProjectID (autonumber primary key)
> ProjectManager
> Client
> etc.
>
> tblToDo
> ToDoID (autonumber primary key)
> ProjectID (foreign key, linked to ProjectID in tblProject)
> DateStarted, etc.
>
> And so forth for the rest of your tables. It sounds as if there are three
> levels: Project, ToDo, and ToDoDetails. Is my understanding correct?
>
> Autonumbers are fine for primary keys, but should not be seen by the user.
> If you need an orderly progression of project numbers, you could use
> something like Roger's code to produce it, but that number would need to be
> the primary key of tblProjects. I'm not quite sure how that would work with
> subform records, so the best choice may be to explicitly save the Project
> record before entering any ToDo items or any other such details.
>
> "FireGeek822" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Hey Bruce,
> >
> > My issue with the AutoNumber -
> >
> > This is actually a "Project Database". This organizes a Project ToDo
> > List and tracks time spent on all pieces of our projects. On my
> > Project form (tblProjects) I have the following:
> >
> > - TreeView Control that populates a To Do List (tblToDo) for the
> > Project. Organizes the list by generic item categories such as
> > (Assessment, Training, Implementation, Testing, etc.) and SubItems that
> > fall under these categorise. For Training there are items such as
> > Schedule Training, Create PowerPoint Presentation, etc. When an item
> > is done, we can select the item, enter the date the item was completed
> > in a separate textbox and the amount of time spent on this item in
> > another textbox. We then can click a button and it removed this item
> > from the TreeView and places it, the date and time into a listbox of
> > completed items below. This updates 3 fields in the tblToDo table
> > (date, time and completed).
> >
> > - ListBox as explained above.
> >
> > - SubForm (where my problem lies) - This is our smaller work log
> > (tblWrkLog). We are actually 3 Project Coordinators for an HR office.
> > If any of us are out, we need to be able to follow someone's work on a
> > project. These projects include others doing various items at any of
> > our 9 offices in states across the country. Some ToDo items may take
> > longer to do than just create a PowerPoint presentation. So the work
> > log tracks all the small details. The 3 tables involved really don't
> > have any fields that can be "Key" fields other than creating an ID
> > field as a "Key" and using AutoNumber for that field. This Work Log
> > subform kicks out errors when I want to use/reference the number from
> > the WorkLogID field (the autonumber field).
> >
> >
> > We will be having any of 4 users using this database and possibly at
> > the same time. We really like being able to see all 3 items on one
> > form (ToDo List, Completed Items List and Work Log).
> >
> > I am sooo close to finishing this off. Just irritating problems like
> > this popping up now.
> >
> > Thanks for your help - hope this explanation isn't too confusing.
> >
> > T-
> >
>
>
>

your project management database sounds interesting...do you have a template
that you could send me...I hope I am not asking for too much...we are always
searching of ways to increase our productivity, efficiency and customer
relations...we have also had issues with autonumbering. thanks and hope that
you can assist me. my email address is (E-Mail Removed)



"FireGeek822" wrote:

> Hey Bruce,
>
> My issue with the AutoNumber -
>
> This is actually a "Project Database". This organizes a Project ToDo
> List and tracks time spent on all pieces of our projects. On my
> Project form (tblProjects) I have the following:
>
> - TreeView Control that populates a To Do List (tblToDo) for the
> Project. Organizes the list by generic item categories such as
> (Assessment, Training, Implementation, Testing, etc.) and SubItems that
> fall under these categorise. For Training there are items such as
> Schedule Training, Create PowerPoint Presentation, etc. When an item
> is done, we can select the item, enter the date the item was completed
> in a separate textbox and the amount of time spent on this item in
> another textbox. We then can click a button and it removed this item
> from the TreeView and places it, the date and time into a listbox of
> completed items below. This updates 3 fields in the tblToDo table
> (date, time and completed).
>
> - ListBox as explained above.
>
> - SubForm (where my problem lies) - This is our smaller work log
> (tblWrkLog). We are actually 3 Project Coordinators for an HR office.
> If any of us are out, we need to be able to follow someone's work on a
> project. These projects include others doing various items at any of
> our 9 offices in states across the country. Some ToDo items may take
> longer to do than just create a PowerPoint presentation. So the work
> log tracks all the small details. The 3 tables involved really don't
> have any fields that can be "Key" fields other than creating an ID
> field as a "Key" and using AutoNumber for that field. This Work Log
> subform kicks out errors when I want to use/reference the number from
> the WorkLogID field (the autonumber field).
>
>
> We will be having any of 4 users using this database and possibly at
> the same time. We really like being able to see all 3 items on one
> form (ToDo List, Completed Items List and Work Log).
>
> I am sooo close to finishing this off. Just irritating problems like
> this popping up now.
>
> Thanks for your help - hope this explanation isn't too confusing.
>
> T-
>
>