How do I set up a ToDo list in Access?

[Ray Boissonneault]

I need to set up a system that allows me to prioritize tasks. The challenge
is that I want to be able to insert a new task that may be higher priority
than an older task already in the list. I would like the priorities to
automatically adjust based on the new task inserted. I am using this to help
manage products I am continuously improving. I currently am using a simple
ranking system of 1-10. As usual it seams like all tasks are a #1 priority.
I would like to be able to have a very linear scale that will continuously
adjust.

I am working in Microsoft Access 2003.

Thanks,
Ray

 

[John Spencer]

What I would do would be to use numbers in increments of 100. That way
I could insert a new record value between any two numbers. Or even
better use a number type that has a decimal portion and you can
subdivide the position that way.

IF you feel it necessary, you could occasionally run a VBA routine to
renumber all your items.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[John... Visio MVP]

I provide help with Access, Excel and Word applications for a modest fee.
I could implement this for you for a very reasonable fee.

Steve

And I thought you had finally come to your senses and left.

These newsgroups are provided by Microsoft for FREE peer to peer support.
From past experiences, even free, stevie's help is overpriced.

John... Visio MVP

 

[a a r o n . k e m p f]

SQL Server can actually increment any autonumber field like this-- for
you-- automagically.

or you can write code.

you be the judge

 

[a a r o n . k e m p f]

hey, lay off the personal attacks.

just because people don't look like you-- and think like you-- doesn't
make us wrong.

thanks

-Aaron

 

[posted_by_anonymous]

hey, lay off the personal attacks.

hey, you are the soot-blackened, filthy pot calling a shiny, clean kettle
black. You stalk anyone giving a good, correct answer to an Access question,
belittle them, and insult them with personal attacks. don't preach what you
aren't willing to practice, troll-boy.

just because people don't look like you--
and think like you-- doesn't make us wrong.

no, that's not what makes you wrong.

Steve is wrong because he uses the newsgroup only to solicit work. He
solicits it from people who may not be aware that the Access skills he's
demonstrated in his posts are not worth being paid for. Conscientious,
dedicated people post to let peope know that Steve has demonstrated that he
is not qualified to perform the services that he's promoting himself for.
Exposing a solicitation that would be, at best, a losing proposition for the
buyer, and, at best, a fraudulent misrepresentation of skills and ability is
a "personal attack" only in the mind of a troll like you, with a warped,
skewed, and twisted view of reality.

you are wrong because you stalk anyone providing useful Access answers and
post your spam promoting switching to a server database regardless of whether
it is appropriate or not. Conscientious, dedicated people respond to let new
posters here know that you are a cyberstalker (self-confessed, convicted on
your own statements, and a public record of the guilty plea and sentence easy
to link for proof), more than misguided -- in fact, a pathological liar to
try to prove points that are not true, and, full of vulgar insults. And, lest
it slip past, you have proven yourself to be a troll, a disruptive poster,
with a warped, skewed, and twisted view of reality.

so, hey, lay off the self-appointed moderation.

thanks

yah, thanks if you behave; for shame if you don't (and so far, your actions
in this newsgroup have overwhelmingly been "for shame"), so no thanks so far,
twisted troll.

 

[BruceM]

I put together something recently to replace one number in an ordered list
with another. If the items are numbered 1 through 10, and the user changes
3 to 1, 1 becomes 3. That is, the third item on the list and the first item
swap places.
I used a DMax + 1 system for numbering the records initially. In the form's
Current event (it is a subform in this example):

Dim strWhere As String

If Me.Parent.NewRecord Or _
Me.Parent.Recordset.RecordCount = 0 Then
Me.txtListOrder.DefaultValue = 1
Else
strWhere = "MainID = " & Me.Parent.MainID
Me.txtListOrder.DefaultValue = Nz(DMax("ListOrder", "tblSubTable",
strWhere), 0) + 1
End If

ListOrder is the number field in which the list order is stored.
txtListOrder is the text box bound to ListOrder. tblSubTable is the
subform's Record Source table.

In the After Update event for txtListOrder:

Dim db As DAO.Database
Dim lngOrder As Long
Dim strSQL As String

lngOrder = Me.txtListOrder.OldValue

If Me.txtListOrder = lngOrder Then
Exit Sub
Else
strSQL = "UPDATE tblSubTable SET [ListOrder] = " & lngOrder & _
" WHERE [ListOrder] = " & Me.ListOrder & _
" AND MainID = " & Me.Parent.MainID & ";"
Set db = DBEngine(0)(0)
db.Execute strSQL, dbFailOnError
Set db = Nothing
Me.Requery
End If

It would be somewhat different if the list is in a main form. I expect you
would just drop the reference to the Parent, and reference a field on the
main form instead. If this approach would be useful, and if you are not
able to work out how to apply the code in your situation, post details of
the database structure.

 

[a a r o n . k e m p f]

wow, Bruce.. it looks to me like you've written a whole bunch of code
that would be automatically done already-- if you just used SQL Server
and the RANK function

I put together something recently to replace one number in an ordered list
with another.  If the items are numbered 1 through 10, and the user changes
3 to 1, 1 becomes 3.  That is, the third item on the list and the firstitem
swap places.
I used a DMax + 1 system for numbering the records initially.  In the form's
Current event (it is a subform in this example):

Dim strWhere As String

If Me.Parent.NewRecord Or _
  Me.Parent.Recordset.RecordCount = 0 Then
  Me.txtListOrder.DefaultValue = 1
  Else
    strWhere = "MainID = " & Me.Parent.MainID
    Me.txtListOrder.DefaultValue = Nz(DMax("ListOrder", "tblSubTable",
strWhere), 0) + 1
End If

ListOrder is the number field in which the list order is stored.
txtListOrder is the text box bound to ListOrder.  tblSubTable is the
subform's Record Source table.

In the After Update event for txtListOrder:

Dim db As DAO.Database
Dim lngOrder As Long
Dim strSQL As String

lngOrder = Me.txtListOrder.OldValue

If Me.txtListOrder = lngOrder Then
  Exit Sub
  Else
    strSQL = "UPDATE tblSubTable SET [ListOrder] = " & lngOrder &_
                   " WHERE [ListOrder] = " & Me.ListOrder & _
                   " AND MainID = " & Me.Parent.MainID & ";"
    Set db = DBEngine(0)(0)
    db.Execute strSQL, dbFailOnError
    Set db = Nothing
    Me.Requery
End If

It would be somewhat different if the list is in a main form.  I expectyou
would just drop the reference to the Parent, and reference a field on the
main form instead.  If this approach would be useful, and if you are not
able to work out how to apply the code in your situation, post details of
the database structure.

message

I need to set up a system that allows me to prioritize tasks.  The
challenge
is that I want to be able to insert a new task that may be higher priority
than an older task already in the list.  I would like the priorities to
automatically adjust based on the new task inserted.  I am using thisto
help
manage products I am continuously improving.  I currently am using a
simple
ranking system of 1-10.  As usual it seams like all tasks are a #1
priority.
I would like to be able to have a very linear scale that will continuously
adjust.

I am working in Microsoft Access 2003.

Thanks,
Ray- Hide quoted text -

- Show quoted text -

 

[George Hepworth]

IMHO, the best way to deal with trolls is a two-part strategy.



1. Ignore their standard rantings.

2. Step in when it is necessary to correct a misstatement that could
potentially mislead a novice poster.



While it is true that novice posters don't know a great deal about Access,
it is also true that most of them are smart enough to figure out quite
quickly who the trolls are.

 

[BruceM]

Then describe how that would be done. Describe how the Rank function would
allow reordering of a list without writing the new ranking information to a
table. Don't bother replying with "It just works" or some other such
drivel. Be specific.

message
wow, Bruce.. it looks to me like you've written a whole bunch of code
that would be automatically done already-- if you just used SQL Server
and the RANK function

I put together something recently to replace one number in an ordered list
with another. If the items are numbered 1 through 10, and the user changes
3 to 1, 1 becomes 3. That is, the third item on the list and the first
item
swap places.
I used a DMax + 1 system for numbering the records initially. In the
form's
Current event (it is a subform in this example):

Dim strWhere As String

If Me.Parent.NewRecord Or _
Me.Parent.Recordset.RecordCount = 0 Then
Me.txtListOrder.DefaultValue = 1
Else
strWhere = "MainID = " & Me.Parent.MainID
Me.txtListOrder.DefaultValue = Nz(DMax("ListOrder", "tblSubTable",
strWhere), 0) + 1
End If

ListOrder is the number field in which the list order is stored.
txtListOrder is the text box bound to ListOrder. tblSubTable is the
subform's Record Source table.

In the After Update event for txtListOrder:

Dim db As DAO.Database
Dim lngOrder As Long
Dim strSQL As String

lngOrder = Me.txtListOrder.OldValue

If Me.txtListOrder = lngOrder Then
Exit Sub
Else
strSQL = "UPDATE tblSubTable SET [ListOrder] = " & lngOrder & _
" WHERE [ListOrder] = " & Me.ListOrder & _
" AND MainID = " & Me.Parent.MainID & ";"
Set db = DBEngine(0)(0)
db.Execute strSQL, dbFailOnError
Set db = Nothing
Me.Requery
End If

It would be somewhat different if the list is in a main form. I expect you
would just drop the reference to the Parent, and reference a field on the
main form instead. If this approach would be useful, and if you are not
able to work out how to apply the code in your situation, post details of
the database structure.

message

I need to set up a system that allows me to prioritize tasks. The
challenge
is that I want to be able to insert a new task that may be higher
priority
than an older task already in the list. I would like the priorities to
automatically adjust based on the new task inserted. I am using this to
help
manage products I am continuously improving. I currently am using a
simple
ranking system of 1-10. As usual it seams like all tasks are a #1
priority.
I would like to be able to have a very linear scale that will
continuously
adjust.

I am working in Microsoft Access 2003.

Thanks,
Ray- Hide quoted text -

- Show quoted text -

 

[Fred]

Ray,

I'm not as good as these other guys at coding, but have used Access for
about 10 years for company level versions of what you are doing.

I think that you are going to find that in the end, anything "automatic"
regarding priorities isn't what you want. For example, if you only have
resources to have 5 tasks as "priority 1" and you have classified 20 tasks as
"1", then you need a human decision on which 5 to keep and which 15 to kick
out. And then a change in your triage (decisionmaking) process for
assigning future 1's. Access doesn't know your business like you do.

Sincearely,

Fred