Replacing Sendkeys with VBA code

[FelixTheCat]

I've been working with ACCESS for years but have always avoiding hard VBA coding, relying instead on their visual tools, macros, canned actions. It's always been adequate for my needs. Now I find Sendkeys is no longer available to me in Win 7 and I can't even figure out how to control tab out of a subform.

This is my specific and immediate problem. I need to create a macro or code that will tab from a subform back to it's parent form so I can add a new record from the parent form.



Any suggestions?

[lfpm062010]

You can't change a setting to do this, unfortunately. You can use "Ctrl+Tab" to tab out of the subform to the next control on the main form, or you can write code to "setfocus" on the next field on the main/subform.

[Dal Jeanis]

If you want to do it from VBA, then pick the particular control (MyControl) on the main form that you want to activate, and try:

   Me.Parent!MyControl.SetFocus

This handy reference is where you can find the VBA way to reference any object or control from code in any other object.
http://access.mvps.org/access/forms/frm0031.htm
in this case, the row and column I referenced was

Section:  To refer to a control
Row:      On mainform, 
Colum:    From Sub1  
Syntax:   Me.Parent!ControlName

[ItsMe]

You can also setfocus on the form. Me.SetFocus or maybe Forms!MainFormName.Setfocus if the code is on a subform's Module. You can refer to post #3 if your are not in the main form's VBA module.

I have a question though. Why are send keys not working? Is it an Access 2010 thing. I think I recall using send keys not too long ago with W7

[Dal Jeanis]

Heh. I didn't worry about whether sendkeys works or not, ItsMe, because I know it's a deprecated practice and often precipitates funky results. Best to help the guy ease over into best practices.

[ItsMe]

Dal, so true.

[FelixTheCat]

Thanks guys for the help. Yeah, I know I've been postponing the inevitable (learning to code VBA) and I backed myself into a corner that I was having trouble progressing out of.

As for Sendkeys - what I've read suggested that it would still be working in ACCESS 2010 except that I'm running on top of Win7 (64 bit.) I need for the app to work on Windows 8 machines, so I'm probably lucky to have run into this problem before crunch time.

Once again, thanks for the help!

Jack

[ItsMe]

I listed some references that might get you up and running with SendKeys in this thread here
https://www.accessforums.net/access/...ins-40020.html

Just in case you are in a bind. No pun intended. I would think .Setfocus would be the easiest and best way to proceed though.

[FelixTheCat]

Thanks ItsMe. I am in a bind.

I've gotten the code written to move the focus to the control I wanted on the parent form and it works. I still need to issue one more command and my ignorance of VBA has stopped me short of getting the job finished. After moving the focus to the control on the parent form, I need to issue a "new Record" command.

I expected I'd just call the VBA to set focus within a macro and then invoke the new record command available to me in the macro builder, but I can't find the VBA code to call from the macro, not can I figure out how, in the alternative, to call a new record from the parent form. (Man, I'm paying for not learning VBA earlier and I appreciate your help to get me past my sins...)

Jack

[Dal Jeanis]

DoCmd.GoToRecord , , acNewRec

I'm not sure what best practices is with regard to jumping automatically to a new record. Be sure to test what happens when you don't actually do anything with the new record. You don't want junk accumulating in the database, so you may have to have code on exit to kill the record if it hasn't been completed.

[ItsMe]

It should not save using acNewRec but like Dal mentions, strnage things can happen, especialy when employing macros. All it would take would be to assign an empty string to a field on your new record and Whamo... you have a saved new row in your table(s)

[FelixTheCat]

Thanks Dal!

Worked. Yeah, I'll need to test it a bunch before I put it in front of anyone for work.

Thanks for your help. I need to get caught up on this stuff - my excuses for avoidance are spent.

Jack

[FelixTheCat]

Thanks ItsMe. Works like a charm. No macros involved and no blank records generated.

Dal and ItsMe, thanks much for your help!

Jack