Append Query using the Current record

trebor3900

Registered User.
Local time
Today, 21:29
Joined
Apr 26, 2005
Messages
47
I have a form with a subform in Datasheet view. I want the user to double click a record in that subform and a query to ammend it to another table.
I want to trap the double click event of the subform but
how do i run a query based on a current record in a subform?
 
There is only one "current" record in the sense that focus exists only at one place at a time.

Rather than having the parent form trap the double-click in the sub-form, turn the problem around. Have the SUB-FORM trap the double-click and then find out who/what its parent is. If you think about layers and the concept of programming "scope" - the sub-form is not within a "natural" scope when you are running code on the parent form. However, the parent form is merely an "outer" scope when the sub-form is active.

Look up property .Parent (which applies to forms) to find out how to locate your parent form and look up data from it.

The other part of your problem is the automatic append. There, you will have to figure out how to build the append (actually, an INSERT INTO) query based on the data available to you. Try building a dummy append query in design view just to see the exact syntax you need. Then you can build the string you want for the append operation. OR at that level, if you have enough to do an append query, you have enough to open a recordset, do an .AddNew, modify the fields in the recordset, and do an .Update on it, then close it. Since you are ALREADY in an event routine, you are ALREADY working with VBA code any way you look at it. So the recordset operation might not be so taxing.

In any case, look up recordsets, .AddNew, and .Update, and related topics in the Access Help files. Remember to specify the type of recordset you wanted to use - DAO or ADO, depending on what else you use in the VBA - when you DIM the recordset variable.
 
Are you using a datasheet display on the subform? edit : Oops, yes you are!

If so, have you considered using a list box instead? That's what I do when I want to do operations like this, and I have several in my db.

Regards,

George
 

Users who are viewing this thread

Back
Top Bottom