Setfocus in Access form FROM within Excel ?

[ksor]

I have a WorkSheet in Excel with some shapes representing different social events.
These social events are commented in a Word document with a filename and path stored in a textbox on a form in Access.
When working in Access I can just focus the textbox with the filename and path and press ENTER then the document opens.

I want to do this while viewing the shapes in Excel.

I know how to minipulate Access from Excel so the right record is shows in the form, but the focus is in a wrong textbox !

How can I from WITHIN EXCEL move that focus to the right textbox AND press ENTER by VBA ... in Excel ?

[ksor]

A way to do this - I thought - could be:

Ok, when you can run subs in Access from within Excel, and I CAN do that.
then just make a PUBLIC sub i Access - lets call it "openDoc" that can do the SetFocus and open the document - nice !

BUT ...

when calling the sub "openDoc" from within Excel ... IT CAN'T FIND THE SUB !

I can call other subs from within Excel, but these subs are places in a MODULE !

OK, that sub "OpenDoc" is placed in a subform - is that THE problem ?

I can't move the sub "openDoc" to a module because of all the close ties to the subform.

How can I ... sort of make that "openDoc" sub "real" PUBLIC so it CAN be called from within Excel ?

[snb]

You can use a querytable in Excel to retrieving the data from Access.
You can link every shape in Excel to that querytable, so that clicking the shape uses the data in the querytable to open the worddocument.
To me a Form in Access seems to be fully redundant.

[ksor]

You can use a querytable in Excel to retrieving the data from Access.
You can link every shape in Excel to that querytable, so that clicking the shape uses the data in the querytable to open the worddocument.
To me a Form in Access seems to be fully redundant.

That's sort of doing all the coding I've done in Access one more time in Excel ... that's bad practice, I think.

I can call the sub from the immidiate window WITHIN ACCESS this way:

Forms("PERSON Vedligehold af")![Sub Form Hændelser].Form.getDocument

and it works nicely !

But "Forms("PERSON Vedligehold af")![Sub Form Hændelser].Form.getDocument" CAN'T be inserted in Excel without some change of " to []:

    With appAccess
        .Run "prepPersonForm", id, "tabHændelser", Hid                                                 <<<<<<<<<<<<<<<<<<<<<<<< this call works nicely - the sub "prepPersonForm" in placed in a MODULE
        .Run "Forms([PERSON Vedligehold af])![Sub Form Hændelser].Form.GetDocument"   <<<<<<< I changed the " to [] - but it can't be found ! 
        SetForegroundWindow .Application.hWndAccessApp
    End With

HILFE .... that's german and means HELP !

EDIT: Oh, I forgot to comment on the "form in Access" - that form is NOT primarily to serve this "fiddling" from Excel - it's the "main thing" in the system ... all data is entered in that form. Every social event is commented in details there.

What I do in Excel is just another way of showing events on a timelime - in the form they are shown in a listbox and you don't have the same feeling of "time span" in a listbox.

[snb]

I'm very glad you know what bad practice is.
But you also know very well to scare of potential helpers.

[ksor]

I'm very glad you know what bad practice is.
But you also know very well to scare of potential helpers.

I don't quite get you ...

You don't know my system but assumed I made that form just as a tool to get done what I wants done from within Excel.

I just told you that wasn't the case AND that coding the same functionality twice is bad practice.

But never mind ... I think I now have found out how to do it, so I'll close this thread.

thx for your time anyway !

[SamT]

that's bad practice, I think.

No. Bad practice is to insist that you open two applications to accomplish one task, instead of just copying and editing existing code so as to only use one application. It wastes the Users time and doubles memory usage

I think that you are conflating duplicate code within one application with recycling code from one application in another application. The first is indeed not the best practice, but is recommended in the second.

[ksor]

You don't understand my goals, you don't know my system so you can't really know what is good or bad practice !

You set up some assumptions for bad practice and then declare it's bad pratice ... what har this to do with my system and methods ?

As I said earlier ... never mind, I have it to work nicely now.

BTW: I marked the thread as solved ... so let's close it here.

[SamT]

I bow to your many years of professional programming experience and instruction.