Presenting a subset of a query in a combo box

Question

Hello all

I am creating a task logging database. I have a Projects table which contains a list of around 100 projects for the various teams. I have created a table and form which allows users to select which projects are displayed to them so that they only need see current projects they are working on. Users add lines to a Tasks table which links a project with the time they spent on it. When the user goes to enter their data, they can see Tasks they have logged in the previous weeks.

The problem I have is that if a user changes the list of projects they can view by removing a project they were previously working on, this will then become a blank field in the list of Projects already in the database that they see. This is because the query populating the combo-box (Projects list in the above screenshot) no longer contains the project. So, I wish to display all projects as entered, but have the combo box just show the projects that the user is interested in. If this means sorting the combo box so the user's projects are at the top then this is fine.

Any help would be appreciated.

Thanks

Ray

---

------

Answer 1

Hi larsty,

Do you mean that, User can make the changes in previous records that are displayed in the list?

To avoid this issue, You can try to design the unbound form to insert data and to display previous data, you can display it in sub form and disable it for modification.

Regards

Deepak 

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

Hi Deepak

Thanks for responding. Yes, the user can make changes to the previous records in the list. This was required as occasionally the user needs to change a previous project classification. On review, this is probably functionality that I can argue out of the specification. 

The user only needs to see their own list of projects in the combo drop down, but the whole list of projects in the previous tasks list. If I could order the combo box this would be acceptable.

Thanks

Ray

---

------

Answer 3

Hi larsty,

what is the row source of the Combobox?

If it is a query then you can try to sort data in query then you can able to see the sorted data in combobox.

Output:

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 4

Hi larsty,

Is your issue is solved now?

I find that you did not done any follow up after my last post.

If your issue is fixed by you then I suggest you to post your solution and mark it as an answer.

If your issue is still exist then try to refer the suggestions given by the community members.

If you think that the suggestions given by the community member can solve your issue then mark the helpful suggestion as an answer.

It will help us to close this thread and it also can be helpful to other community members who will meet with same kind of issues in future.

If you have any further questions then you can let us know about it, We will try to provide you further suggestions to solve it.

I suggest you to update the status of this thread and take appropriate actions to close it.

Thanks for your understanding.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 5

Hi Deepak

Sorry for the delay. I have not had a chance to test anything for a while.

I have tried to sort the query as you suggest, but the options are no good for what I need.

I have chosen to take the route where I have a query returning a user selected list of projects and then provide training on why this might result in blank cells in old data when they have removed a project. This will suffice for now. A better solution is much more complex and doesn't warrant the time and risk of introducing errors. So, no solution on this one for now.

Thanks for your help

Regards

Ray

---

------

Answer 6

Hi larsty,

You had mentioned that,"I have tried to sort the query as you suggest, but the options are no good for what I need."

Can you show us the row source and the result you got?

Also try to post the desire result.

We will try to check it and try to reproduce the issue on our side.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 7

Hello Deepak

I can show you what I have. Here is the current query and sort order for the combo box:



fosUsername() looks up the Windows username, which is what is stored in the Username field.

The query returns the projects below which have been selected by the particular user. If this is used to populate the combo box, then when a user deletes a project from their viewing list (projectsdisplayed table), previously logged tasks for this project are blank.

The sort order I require is that the above projects are top of the combo box (preferably with a divider) but all the other projects are still listed lower down. Below is the complete list of projects - I want those above (user-selected) to be top of the list.

I cannot seem to get the ORDERBY part of the query to order by people.username=fosusername() whilst still displaying all the other projects.

I have other solutions which I think will work but as I say they are too complex and risky (make tables).

Thanks for your help

Ray

---

------

Answer 8

The simple way to retain a value in a combo box where the row has been deleted from the referenced  table is not to use a surrogate key.  If the Project column is a candidate key of Projects,  which the data you've shown suggest is the case, then that column can be used as the primary key, and the foreign key in the ProjectsDisplayed table.  Because the combo box's BoundColumn will no longer be a hidden column containing a surrogate key, but the visible project column, its value will still show even where row from the referencing table has been deleted, though I'll come back to the issue of deleting rows from a referenced  table below.

Where a surrogate key is necessary, e.g. with personnel data, where names do not constitute a candidate key, the solution is to use a 'hybrid' control by carefully superimposing a text box on a combo to give the appearance of a single combo box control.  This is only necessary in continuous forms view, however, not in single form view, where a combo box alone can be used.  You'll find examples in ActiveEmployees.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

In this little demo file examples are given for both continuous and single form view.  In both cases an inactive employee, who is no longer included in the combo box's list, is still shown in earlier records.  This is achieved by making the RowSource of the combo box a query which returns not only active employees, but also the current employee regardless of whether they are active or not.

This solution would not work where a row had been deleted from the referenced table of course, but this raises a more fundamental issue.  If you wish to retain the rows in a referencing table which reference a deleted row in a referenced  table, rows should not be deleted from the referenced  table, but 'marked' as deleted in the same way that employees are marked as inactive in my demo.  In addition to allowing the above solution, this also enables the relationships to be enforced, which they should be in a correctly designed relational database.  An unenforced relationship is as much use as a chocolate poker!

For an illustration of how to mark, or unmark, records as deleted, see DeleteDemo.zip in the same OneDrive folder.

---

Ken Sheridan, Stafford, England

Answer 9

Apologies for the long delay in replying. This is not a priority at the moment, but I hope to pick it up again.

Thanks Ken for the detailed explanation. I think that this is the closest I can get to the desired behaviour. Your note on changing my primary key makes good sense, though I don't think I can do this because there will always be cases where the project name changes half way through.

The suggestion about adding a "deleted" field to the users "visible projects" table seems to be the best solution - I can see why this would work and it is elegant and does not require a lot of extra data to be stored. I will try and implement this when I next have time to work on this database.

Thanks again for your help.

---

------

Answer 10

 Your note on changing my primary key makes good sense, though I don't think I can do this because there will always be cases where the project name changes half way through.

That is one reason why a surrogate numeric key, whose value is immutable, should be used in contexts like this.

---

Ken Sheridan, Stafford, England