Solved

MS Access form tab to next page on exit from last control

Posted on 2007-12-06
19
1,742 Views
Last Modified: 2013-12-25
Can someone please provide a code example for tabbing to the next page of a form from the last control? I know it has something to do with the On Exit command but have no idea how to code it. Thanks.
0
Comment
Question by:devabarry
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
  • 4
19 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 20422558
No code, in Form Design, do a right-click:Properties on the form, Other tab, and set the Cycle property to All Records.

This way, when you tab off the control with the largest TabOrder value, the form will move to the next record.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 20422572
otherwise...

docmd.GoToRecord acDataForm, ,acNext
0
 

Author Comment

by:devabarry
ID: 20423361
thanks. I didn't see a Cycle property on the Other tab with either focus on form or on specific control. As for the code, I get the following error msg: This action requires an Object name argument. I presume this means the page name? Where would this go?
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 65

Expert Comment

by:Jim Horn
ID: 20423539
>I didn't see a Cycle property on the Other tab with either focus on form or on specific control.
Make sure the upper left caption of the Properties dialog reads 'Form'

Which version of Access are you using?
0
 

Author Comment

by:devabarry
ID: 20424550
2003. I see now this is a little more involved. When right-clicking on the form, I get the tab list of controls that span all the pages, i.e. ID and date controls and the Add, Save, Undo, Delete and Find controls. But the tab order for individual fields on each page is accessed by highlighting a field and right-clicking. The way it is currently set up is to begin on the ID control then tab up to the page tab, after which it cycles through the individual fields, but then after the last field returns to ID. What I want it to do is to cycle to the first control on the next page after each of the last fields, and ideally return to the Add, Save, etc controls after the last page (i.e. save the record after all fields entered). So, between the two tab sections, how do I make them do what I want them to do? Is this too complicated? Perhaps it's set up in the wrong way from a design perspective? Thanks.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 20425741
devabarry,

First, re- read jimhorn's post and make *sure* you are in the right spot with regard to the Properties box.



I think you statement:
<tabbing to the next page of a form from the last control? >
... is a little confusing

A form can have separate "Pages" (for the same record) separated by a page break.
So pressuming your tab order is set correctly, you *should* move to the next Page automatically, when you tab out of the last control on the current page.

You can also have a Tabbed control *ON* a form that has *pages*.

Or do you have something different altogether?

Please explain in detail
Thanks!
:)

JeffCoachman
0
 

Author Comment

by:devabarry
ID: 20430433
Yes I think I am in the right spot in the properties box.  by <tabbing to the next page of a form from the last control? > I meant the last control from the previous page. I have multiple pages (page tabs) and I want the first control on the next page to become active after the On Exit from the last tab of previous page. My dilemma is that I essentially have two sets of tab orderings: the tabs that are for the controls that appear across all pages (ID, date, Add, Save, Undo, etc), and the tab order for the individual fields on pages. As it is now set up, the focus is first set on the ID, then date, then the top of the page (all part of the 'all pages' tab order), then the tab skips into the individual page fields. On Exit from the last field of an individual page sends the focus back to the ID and date fields, and then back through the individual page fields, until on the second go-round it goes to the Add, Delete, etc controls, after which I get a 'Record Saved' popup. I don't want the record to save until all pages have been cycled through, i.e. I want the On Exit from the last control of the last page to cycle down to the Add control, but now before. Hope all this makes sense!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 20431080
devabarry,

So again, Can I presume you are using a "Tabbed Control" on a from?
Is this correct?

If you have a Tabbed control on a form, you do indeed have two tab orders.
One for the Form itself.
Another for the Fields in the tabbed control.

The <controls that appear across all pages (ID, date, Add, Save, Undo, etc)>
...should be on the form itself.
Correct?

I presume also that your current Tab order is the way you want it.
But you just don't want the "Record Saved" popup to appear untill All the filelds on the tabbed control are cycled through.
Correct?

You need to figure out what triggers this "Record Saved" Popup, and what it actually does.
AFAICT, this is not a message that is native to Access.

I think this code is causing your issue.
Can you post all the code on the Form here, so we can take a look?

Thanks

JeffCoachman

0
 

Author Comment

by:devabarry
ID: 20431752
Yes, I guess I am using a tabbed control. I didn't know that. Yes, ID, date, etc are on form itself. Correct on no save until all controls cycled through. Here's the code:

Option Compare Database

Private Sub AddRecord_Click()
On Error GoTo Err_AddRecord_Click


    DoCmd.GoToRecord , , acNewRec
    Participant_ID.SetFocus
   
Exit_AddRecord_Click:
    Exit Sub

Err_AddRecord_Click:
    MsgBox Err.Description
    Resume Exit_AddRecord_Click
   
End Sub



Private Sub Delete_Click()
On Error GoTo Err_Delete_Click


    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Delete_Click:
    Exit Sub

Err_Delete_Click:
    MsgBox Err.Description
    Resume Exit_Delete_Click
   
End Sub
Private Sub Command213_Click()
On Error GoTo Err_Command213_Click


    DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

Exit_Command213_Click:
    Exit Sub

Err_Command213_Click:
    MsgBox Err.Description
    Resume Exit_Command213_Click
   
End Sub
Private Sub Command214_Click()
On Error GoTo Err_Command214_Click


    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    MsgBox "Record Saved"
Exit_Command214_Click:
    Exit Sub

Err_Command214_Click:
    MsgBox Err.Description
    Resume Exit_Command214_Click
   
End Sub
Private Sub Combo215_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "Participant_ID = " & str(Nz(Me![Combo215], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


Private Sub Add_Record_Click()

End Sub

Private Sub Currently_in_house_Exit(Cancel As Integer)

End Sub

Private Sub Form_AfterUpdate()
      MsgBox "Record Saved"
End Sub

Private Sub Form_Current()
    Combo246 = Participant_ID
End Sub
Private Sub Command217_Click()
On Error GoTo Err_Command217_Click


    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Exit_Command217_Click:
    Exit Sub

Err_Command217_Click:
    MsgBox Err.Description
    Resume Exit_Command217_Click
   
End Sub
Private Sub Command249_Click()
On Error GoTo Err_Command249_Click


    DoCmd.GoToRecord , , acNewRec
    Participant_ID.SetFocus
Exit_Command249_Click:
    Exit Sub

Err_Command249_Click:
    MsgBox Err.Description
    Resume Exit_Command249_Click
   
End Sub
Private Sub Command250_Click()
On Error GoTo Err_Command250_Click


    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
 
Exit_Command250_Click:
    Exit Sub

Err_Command250_Click:
    MsgBox Err.Description
    Resume Exit_Command250_Click
   
End Sub
Private Sub Command251_Click()
On Error GoTo Err_Command251_Click


    DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

Exit_Command251_Click:
    Exit Sub

Err_Command251_Click:
    MsgBox Err.Description
    Resume Exit_Command251_Click
   
End Sub
Private Sub Command252_Click()
On Error GoTo Err_Command252_Click


    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Command252_Click:
    Exit Sub

Err_Command252_Click:
    MsgBox Err.Description
    Resume Exit_Command252_Click
   
End Sub
Private Sub Combo246_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "Participant_ID = " & str(Nz(Me![Combo246], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Command248_Click()
On Error GoTo Err_Command248_Click


    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Exit_Command248_Click:
    Exit Sub

Err_Command248_Click:
    MsgBox Err.Description
    Resume Exit_Command248_Click
   
End Sub
Private Sub Command254_Click()
On Error GoTo Err_Command254_Click


    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Command254_Click:
    Exit Sub

Err_Command254_Click:
    MsgBox Err.Description
    Resume Exit_Command254_Click
   
End Sub

Private Sub Text352_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Text508_Dirty(Cancel As Integer)

End Sub

Private Sub RMR_Date_Exit(Cancel As Integer)

End Sub
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 20433378
devabarry,

OK, it's a little bit clearer now.

I had already written my code to move to the next sheet tab when I tested.
So , yes you are correct, tabbing you would go to the form , then the first tab, then the record will cahnge and you will be back on the form.

So you should put code on the "LostFocus" event of each of the last fields on each of the tabs.

The code will look something like this:
Sub YourLastField_LostFocus()
    Me.YourTabbedControlName.Pages("YourNextTab").setfocus
End sub

Do not put this code on the Last Tab, because obviously, there are no more tabs to go to.
This should get you to tab through all the fields on all the tabs.


Now about that "Save" Button.

The "Record Saved" message code is on the Form AfterUpdate event.

Whey you cycle through all the fields and make at least one change, this event fires.
There is nothing you can do to stop it.
Just remember, Access will save the record on the After Update event all the time anyway, so there is no real need to have a "Save" button.

There are instances where you would need a Save button. Like if you had a lot of fields and you wanted to save every once in a while.
Or if you had an unbound form and collected all the Field data on the form and then "Saved" the record to a table (Somewhat like the "submit" button on some websites)

Anyway...you can now tab through all the fields on all the tabs, then Save, if you like.

Give this a try and post back here if you have any issues.

JeffCoachman
0
 

Author Comment

by:devabarry
ID: 20446212
Thanks Jeff. I'm getting the error msg: "Compile Error: Argument not optional" with the following code:

Private Sub Currently_in_house_LostFocus()
Me.DefaultControl("Household").SetFocus

End Sub

when this error is thrown the "DefaultControl" is highlighted on the VB Editor page. I'm pretty sure DefaultControl is the name of my tabbed control, but how do I know for sure? Household is the name of the next tab page I want to set focus to. Thanks.
0
 

Author Comment

by:devabarry
ID: 20446399
Correction, the code example is:

Private Sub Currently_in_house_LostFocus()
Me.DefaultControl.Pages("Household").setFocus

End Sub
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 20447327
devabarry,

The code I gave you was:
Sub YourLastField_LostFocus()
    Me.YourTabbedControlName.Pages("YourNextTab").setfocus
End sub

But yours somehow has: Me.DefaultControl?
:O
    Private Sub Currently_in_house_LostFocus()
        Me.DefaultControl.Pages("Household").setFocus
    End Sub

Correct the code and see if it works.
;)



In any event, just to be sure:
A Sheet tab has both a Name Property and a Caption Property.
The name in quotes should be the *name* of the sheet tab, (not the caption)

For example,
The Caption is:  Household
But the Name is:  Page2

So it would be something like this:
Private Sub Currently_in_house_LostFocus()
    Me.YourTabbedControlName.Pages("Page2").setfocus
End Sub

Hope this clears things up!
:)

JeffCoachman
0
 

Author Comment

by:devabarry
ID: 20447555
Hi Jeff, I had thought that DefaultControl was the name of my tab control. It comes up in the dropdown list and I don't know what other way to determine the name. I don't think you mean to say that "YourTabbedControlName" is a system variable or some such thing? In any case I tried it and it is unrecognized. I know this is very simple but I'm stuck on figuring out how to identify the tab control name because I didn't create the names and don't know how they are identified (I don't see it in the tab control properties). Thanks.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 20451372
devabarry,

No problem.
;)

Here is a screen-shot:
https://filedb.experts-exchange.com/incoming/ee-stuff/6106-Control-Name-in-properties-box.zip

So in my screenshot, the tabbed control's name is:  TabCtl0

Inserted into my code, this would be:
Sub YourLastField_LostFocus()
    Me.TabCtl0.Pages("YourNextTab").setfocus
End sub

JeffCoachman

0
 

Author Comment

by:devabarry
ID: 20451625
Thanks Jeff!!!! That's it. I'm sorry you had to spell it out in such kindergarten fashion, it's just that I have so many tabs on my tab control that I didn't even realize I had some blank space to click on (until I scrolled to the end). A thick moment on my part but nonetheless I appreciate your patience and guidance. I have learned a lot.

Cheers,
Barry
0
 

Author Closing Comment

by:devabarry
ID: 31413243
thanks again Jeff :-)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 20452810
devabarry,

No problem!
:)

Tabbed controls are tricky for that reason... If you right-click on the wrong spot, you might get the "Page", not the Tabbed control itself.

As far as patience goes...
Years ago someone once took the time to help me... so...

Happy Holidays!

JeffCoachman
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 20452861
>As far as patience goes...  Years ago someone once took the time to help me... so...
I vaguely remember you sent them a case of beer for it too.   That would seem appropriate here as well.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question