SOLVED

Filter & Auto number in Tasks Management Template

Copper Contributor

Hi all,

 

I have two problems:

 

1. Completed Tasks:

- I use a DB based on Task Management template.

- When I open the form (Tasks List) it doesn't show the "Completed" tasks.

 

How can I make it show all the records?

 

2. Autonumering

In the same DB there is autonumber, I want to convert it to normal numbered list, so that if i delete the record no (3) for instance, the following record replaces it and takes the same number (3). In brief, I just want regular numbering I dont care to have unique number for each specific filed.

 

(1) If I convert the ID to normal record, shall it affect the other functions of the DB such as the relationships?

- If it's not advisable to change it, how I can create another normal number list? I tried but i got a msg that there should be only one autonumber list in one DB.

 

Thank you

5 Replies

@Muhammad-Esq 

 

We can't see the modifications you made to the template, so we can't comment on why it doesn't show Completed tasks in your version.

 

You definitely DO NOT want to try to change the way the Primary Key works in ANY table. By that I mean if you plan to replace the AutoNumber with your own version, and then, renumber all of the records in the table accordingly, that will lead to disaster. Please don't make that mistake.

 

If you want a SECOND field, with a sequential, incrementing value that you can control, by all means do that. It can't be an AutoNumber, of course, but you can use a standard Number field and use code to increment it. Again, however, trying to renumber existing records "if i delete the record no (3) for instance, the following record replaces it and takes the same number (3)" is a quick way to cause confusion. Let's say you did that and issued a report saying that something happened with record 3 on May 16th, 2022. Then you follow through on this plan. A week later you create a new report and suddenly it says something happened with record 3 on May 19th, 2022. Confusion, misinformation, call it what you will, it's a problem that need not be created.

 

There are alternatives. If you need to delete a record and don't want to lose its record number, the way to do that is to add a "ItemDeleted" field to the table with a yes/no datatype. Instead of physically deleting records, change that flag ItemDeleted field to Yes. Then you know it's no longer valid, but history is not corrupted.

 

An internet search for code to self generate incrementing sequence numbers will turn up many candidates.

 

 

 

Many thanks @George Hepworth for your answer>

 

Regarding filtering the (Completed) tasks, I didn't make any modification in that part, I think the Access, for some logic, does it by default. You may have a look on it, it is an access template. thanks again.

 

Regarding the autonumbering, (1) Dont worry :) I will not do that and touch the ID.

(2) To be clear, when I open the Task List or generate a report, I just want, simply, to see a regular serial, that's it, not 1, 3, 4, 7 because I deleted (2, 5, 6).

 

If it is difficult to make it in the form view,m then at least in the report view.

 

thanks again.

best response confirmed by Muhammad-Esq (Copper Contributor)
Solution

@Muhammad-Esq 

 

You can determine how filtering is done in the Tasks List form by examining its recordsource. It probably has a criteria that excludes completed tasks. That might be something like "WHERE Status<>"Complete" or "WHERE Status <> 4" where 4 is the ID for status of Complete. That's the most likely situation. You can modify it if you prefer to see all tasks, or you can add a option to see all tasks, or only open tasks.

 

If the task numbers will vary between reports, i.e. you want the sequence only to represent the current set of tasks, there is a way to create a sequence number on the fly when the report or form opens.

 

Here's a link to an example

Your time and care are so much appreciated George. I followed your guidance and both issues worked.

Thank you so much bro :)
One more thing, in the report view, I want to add vertical lines among the columns.
1 best response

Accepted Solutions
best response confirmed by Muhammad-Esq (Copper Contributor)
Solution

@Muhammad-Esq 

 

You can determine how filtering is done in the Tasks List form by examining its recordsource. It probably has a criteria that excludes completed tasks. That might be something like "WHERE Status<>"Complete" or "WHERE Status <> 4" where 4 is the ID for status of Complete. That's the most likely situation. You can modify it if you prefer to see all tasks, or you can add a option to see all tasks, or only open tasks.

 

If the task numbers will vary between reports, i.e. you want the sequence only to represent the current set of tasks, there is a way to create a sequence number on the fly when the report or form opens.

 

Here's a link to an example

View solution in original post