Code for command button navigation

[Accesser]

Hi there,

I have a main form with 2 tabbed pages (pg1, pg2), each with a different subform (sub1, sub2). Across the bottom of the main form are three command buttons (cmd0, cmd1, cmd2). I'm trying to write some code for navigating among the pages and forms, and would like to do the following:

If main form has the focus, then add a new record in main form on cmd0 click, and also add a new record in sub1 and sub2.
If cmd1 is clicked, go to 1st field in sub1.
If cmd2 is clicked, go to 1st field in sub2.

If sub1 on pg1 has the focus, then add a new record in sub1 on cmd1 click.
If cmd0 is clicked, open a msgbox that asks "Enter a new main record?" and, if clicked yes, adds a new record in the main form and also sub1 and sub2, and goes to the 1st field in the main form.
If cmd2 is clicked, go to 1st field in sub2.

And the same if sub2 on pg2 has the focus...add a new record in sub2 on cmd2 click.
If cmd0 is clicked, open a msgbox that asks "Enter a new main record?" and, if clicked yes, adds a new record in the main form and also sub1 and sub2, and goes to the 1st field in the main form.
If cmd1 is clicked, go to 1st field in sub1.

Any help would be greatly appreciated.
Thanks

 

[TrojanRabbit]

When the command buttons are clicked, focus is shifting to the those commands (main form), so you won't be able to check where the focus was. You may need to ask what they want to add when they click on it. Not sure what to do for that.

To add records, you can just use a recordset object with .AddNew. Use the following for each form's source:
Set rst = CurrentDb.OpenRecordset(&quot;<source table>&quot;, dbOpenDynaset)
rst.AddNew
...
rst.Update

To go to the record just created, set rst back to the main table, then:
rst.MoveLast
Me.Bookmark = rst.Bookmark
(You may actually be able to replace rst here with Me.RecordsetClone.)

To set focus, Forms!<frm name>!<ctrl name>.SetFocus.