Schuffling items and automatically adding item

Topic

New Reply

I have 2 Questions

Is there anyway that a person can rearrange items in a datasheet view so that the new order will be retained when the form is reopened?

How do I automatically enter records? I want to be able to enter a major item and have that action add a series of subitems. For example If I picked Form Development I would want it to automatically entered: Research needed information, Design layout, Review with end user, Revise, Submit to Forms committee etc.

Thank you for your help. Fay

Reply | Quote

Replies

There is a way – and I presume you are still working with Access 2003 – Access 2007 has a feature that makes it easier. In 2003 you need to have code running on the form Load or Open event that sets the Order By property. To do that you will probably need to track the user and store what field an in what order the sort is being done on. It will require some experimenting to get exactly what you are after – and you probably want to make sure that each user has their own copy of the database front-end.

To automatically enter records based on a major item being entered, you will probably want to create a VBA procedure based on the After Insert event that uses DAO (or ADO if you prefer) to create each of the subitems as you suggest. If you aren’t familiar with DAO or ADO, you can do it by running an append query that takes all of the subitems for a particular major item and appends them with the appropriate linkage. Using that approach I would create a table that contains each of the subitems in a sample form and then select from that table based on the major item entered. This does get a fair bit more complicated if you are actually trying to do “project management functions” where you would need to track due dates and precedences. If you have further questions on either approach, post back.

Reply | Quote

Thanks Wendell for your time and information. You given me a great set of marching orders. Now I will see what I can do with them.

There is a way – and I presume you are still working with Access 2003 – Access 2007 has a feature that makes it easier. In 2003 you need to have code running on the form Load or Open event that sets the Order By property. To do that you will probably need to track the user and store what field an in what order the sort is being done on. It will require some experimenting to get exactly what you are after – and you probably want to make sure that each user has their own copy of the database front-end.

To automatically enter records based on a major item being entered, you will probably want to create a VBA procedure based on the After Insert event that uses DAO (or ADO if you prefer) to create each of the subitems as you suggest. If you aren’t familiar with DAO or ADO, you can do it by running an append query that takes all of the subitems for a particular major item and appends them with the appropriate linkage. Using that approach I would create a table that contains each of the subitems in a sample form and then select from that table based on the major item entered. This does get a fair bit more complicated if you are actually trying to do “project management functions” where you would need to track due dates and precedences. If you have further questions on either approach, post back.

Reply | Quote