Moving a record from one subform to another

[jshaw1]

I'm attempting to build a form to be used as an agenda generator with two subforms in datasheet view: one contains a list of all active projects, and the other is a list of projects to be discussed. What I'd like to have happen is to be able to double-click the project name in the active list and have it move into the to be discussed one. I do not want the record to be deleted from the active list.

I have an event set for the double-click and an append query to move the data and it works, but it brings up the parameter window when I try to run it. When I manually type the record name in it works fine, but I'd like to avoid having to do this.



I've written the following code in VBA for the double-click event:

Sub Project_Name_DblClick(Cancel As Integer)
DoCmd.SetWarnings flase
DoCmd.OpenQuery "AgendaAppend"
End Sub

And the append query is the following SQL:

INSERT INTO AgendaList ( ProjectName, Category )
SELECT Projects.ProjectName, Projects.Type
FROM Projects
WHERE Projects.ProjectName=forms!AgendaListSubform!ProjectName
ORDER BY Projects.Type;

Any help would be appreciated!

[isladogs]

One of the golden rules in Access is that data should never be duplicated.
So I wouldn't append your data to another table.Instead use a query to view the data in its original table

[June7]

What do you mean by 'active list' and 'to be discussed list'? You have two tables? Why not just 1 table with a field to set record status? Then apply filter to forms.

[jshaw1]

isladogs and June7, thanks for your replies. Allow me to try to clarify some things.

The active list is based on a query that only displays projects that have been identified as “active”. This active list would need to remain constant from week to week and not have any records removed (unless they have been changed to “inactive”) to ensure that projects can be selected in future meetings.

My company would like to be able to select an item from the active list and use the name of the project to create a new record where they can write quick notes from the weekly staff meetings.

A new record in the agenda form would be created for each week’s staff meeting, and the same process would be repeated for subsequent meetings.

Hope that helps to clear up some questions.

[isladogs]

Nothing in your clarification changes what has already been said. You don't need a second table to do this.
That is unless you intend to have several Notes records for the same project in which case you need a second table with three fields: NotesID (PK), ProjectID (FK) & Notes.
A one to many join will link the 2 tables.
Having said that, if the Notes field is memo / long text datatype, you can still do this in one table by using its column history property as an alternative approach.
For more info, see http://www.mendipdatasystems.co.uk/c...lds/4594523656

[GinaWhipp]

Well, I see you are using Reserved Words, i.e. Type and Category as field names in your tables. This is bound to confuse Access which MIGHT explain the issue you are having. You will want to fix that. For a complete list of reserved words see...

http://allenbrowne.com/AppIssueBadWord.html


http://www.utteraccess.com/forum/Acc...s-t539419.html

[jshaw1]

isladogs, thanks! I'll take a look at that as I think that could help.

GinaWhipp, those were just placeholders for the actual names of the tables/queries to redact information. Thanks for that though!

[GinaWhipp]

GinaWhipp, those were just placeholders for the actual names of the tables/queries to redact information. Thanks for that though!

Good news! Can't be too careful.

[isladogs]

Which of my suggestions did you think looked useful?
Think carefully before using column history. It does have disadvantages as explained in my article

[jshaw1]

Apologies for relative inactivity - I was out of town for the weekend. I think that the column history option would make the most sense as the data is stored as a memo. Thanks for your help!

[isladogs]

OK as long as you are aware of the issues before you start.
Good luck.