Getting the Current record in a SubForm by a button on the Main Form

[bstoyano]

Hi,

I have a form which I have split in two parts: the upper one is the header, it's some general info of a Project (reading from table Project, shown in Form View); the lower one consists of lines, which represent the tasks within the project (a separate form ProjectTasks on table Tasks, shown in datasheet view). Those two are linked through a field ProjectID.

On the main form (form Project) I have a button EditTask. What I want from this button to do is : when clicked it should open a third form (form Tasks) for the task where the cursor in the subform ProjectTasks is.

I tried to create a macro for OnClick, using the OpenForm macro action, but I cannot get it to work. I tried to find some info and examples on how to use the ApplyFilter macro action, but I still don't know if it will do. Last I tried to do it with Opening the form and using the GotoRecord action but it requires the record number, which I couldn't get. I saw there is a property CurrentRecord but I didn't get how to use it in this specific case.

So if you guys have any ideas on how to get this button open the task under the cursor I would appreciate it. Is it possible to achieve this with macros or some VBA code will be needed?



Regards,
Borislav

[June7]

Why not edit the task in the subform?

[bstoyano]

Hi, June,
Thanks for replying.
The reason is that the subform should only show a list of the tasks within the project. and it shows only several fields of table Tasks, which I think are important. This is just to give you an overview of how the project should go. Once you have identified roughly what needs to be done, you should be able to open one task only and focus on it. Besides in the Task form (which is much like the Project form) I have a datasheet, showing the employees engaged in the specific task (which I haven't done yet too )

Maybe the short answer to your question should be - because from my point of view it will be much more convenient and this technology can be applied in a number of other cases. Additionally there is the training element in doing such fancy things with your databases

Regards,
Borislav

[June7]

It can be done, I do something very similar. The how depends on what event you want. A button click on the main form (or the subform)? What about the subform DblClick event? I have a form that offers both events to the user.

I don't use macros, only VBA. Something like (button on main form):

Sub btnOpenTask_Click()
DoCmd.OpenForm "Task", , , "TaskID=" & Me.ctrTasks!TaskID
End Sub

I always give controls a name different from the field or object they are bound to, in this case the subform container control named ctrTasks.

[bstoyano]

Hi, thanks again.

Well, I guess that I need to switch to VBA, I didn't want this before having a good understanding of the built-in functionality, but I have the sense that it will be much easier with VBA.

And thanks for sharing your naming convention, I am surely going to use it. I already have that problem with confusing names of components, fields, objects...

I also like the idea of the double click in the subform, although for now it seems too advanced for me.

Regards,
Borislav

[June7]

If you prefer macro, I am sure can do the same behavior.

[bstoyano]

Hi,
Thanks, June7, your solution is EXACTLY what I need. And it's much more elegant than a macro. The more I think the more macros look a bit awkward.